How to make a drop down box where you can add information in to?

[slenish]

What I am trying to do is create (on a Form) a drop down box or (combo/list box) that I can also add information in to.

To give you a little better understanding of what im trying to do. I want to make a box for example, that you can type in a Zip Code. Now in a combo box I can set it so it will look up information on a table/query and self populate the box as I start to type in the zip code. Now the tricky part is I want to make it so if I type in a zip code that is not already on the table/query I can add a new entry to the table/query making it so the new zip code is now part of the table and I can reference it the next time.

Appercaite the help!

[NeoPa]

This is possible with some code.

If the data that populates the Combo- or List- Box is from a table, then you would need to call a .ReQuery() of the control after the data has been added into the table for the new data to become visible.

Adding the data to the table is a straightforward matter of opening a form that does that job.

[NeoPa]

Alternatively, if you wanted a new value to be added automatically (Be cautious here. It's easy to allow garbage and typos in.), you could ensure the .LimitToList property is set to false then, using .ListIndex < 0 and .Value you could add the value into the table from within your code (Either SQL or Recordset processing can work here) then call the .ReQuery to get it to display like the other data.

[slenish]

Hi NeoPa,

Its good to hear from you again :D I looked in to more detail about what you said and I found a site where i think it has the right code I am looking for. Wanted you to take a look at it and see what you think and if it will do what I need it to. From what I was reading it says that the code should make it so in a combo box if the information in not listed it will create a pop up box asking if you would like to add it to the list, and you click yes or no.

It said to add this in the NotInList function.
here is the code

Private Sub cboMetals_NotInList(NewData As String, _

 Response As Integer)

  'Update value list with user input.

  On Error GoTo ErrHandler

  Dim bytUpdate As Byte

  bytUpdate = MsgBox("Do you want to add " & _

   cboMetals.Value & " to the list?", _

   vbYesNo, "Non-list item!")

  'Add user input

  If bytUpdate = vbYes Then

    Response = acDataErrAdded

    cboMetals.AddItem NewData

    'Update RowSource property for versions

    'XP and older.

    'cboMetals.RowSource = _

    ' cboMetals.RowSource _

    ' & ";" & NewData

  'Don't add user input

  ElseIf bytUpdate = vbNo Then

    Response = acDataErrContinue

    cboMetals.Undo

  End If

  Exit Sub

ErrHandler:

  MsgBox Err.Number & ": " & Err.Description, _

   vbOKOnly, "Error"

End Sub

Thanks for the help :)

[ADezii]

The NotInList() Events exists solely for the scenario which you describe, and would be the solution to your problem.

[slenish]

Ok I adjusted the code some and have it starting to work except I am getting an error at one part and I know its because I dont have the syntax set up right. I have been looking at some examples but cant seem to get it. I put the line that is giving me problems in BOLD.

Thanks

Private Sub Combo53_NotInList(NewData As String, Response As Integer)

  'Allow user to save non-list items.

  Dim cnn As New ADODB.Connection

  Dim strSQL As String

  Dim bytUpdate As Byte

  On Error GoTo ErrHandler

  Set cnn = CurrentProject.Connection

  bytUpdate = MsgBox("Do you want to add " & Combo53.Value & " to the list?", vbYesNo, "Non-list item!")

  If bytUpdate = vbYes Then

    [B]strSQL = "INSERT INTO Test Table 2(IN Facility Zip), (field8)  ('" & NewData & "')"[/B]    

    Debug.Print strSQL

    cnn.Execute strSQL

    Response = acDataErrAdded

  ElseIf bytUpdate = vbNo Then

    Response = acDataErrContinue

    Me!Combo53.Undo

  End If

  Exit Sub

ErrHandler:

  MsgBox Err.number & ": " & Err.Description, vbOKOnly, "Error"


End Sub

[ADezii]

Private Sub Combo53_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim MyDB As DAO.Database
Dim rstZip As DAO.Recordset
    
strMsg = "'" & NewData & "' is not in the list.  "
strMsg = strMsg & "Would you like to add it?"

Me![Combo53].LimitToList = True

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Zip Code") Then
  Response = acDataErrContinue      'Display no Error Message
  Me![Combo53].Undo             'Cancel the Entry
Else
  Set MyDB = CurrentDb()
  Set rstZip = MyDB.OpenRecordset("Test Table 2", dbOpenDynaset)
  
  rstZip.AddNew
    rstZip![IN Facility Zip] = NewData    'ADD New Zip Code
  rstZip.Update
  
  Response = acDataErrAdded   'Item added to underlying Recordset and the Combo
                              'Box is Requeried, Item added to List
  rstZip.Close
  Set rstZip = Nothing
End If
End Sub

[slenish]

Thanks ADezii

That worked great!! I had to play with it a little and tweak a couple things but i finally got it to work. Really appreciate you taking the time to re-write that for me. Now im trying to get one other small part work. Im trying to get the same box to come up with a cancel command if you dont enter in enough numbers. I have it working but when you hit cancel it just keeps going to the "would you like to add this as a new record" Which i dont want. I want it to stop and just go back to where you can type in the number again. I know its something small that im missing. Im going to keep playing with it but appreciate any help.

Here is what I have

Private Sub Combo53_NotInList(NewData As String, Response As Integer)

Dim strMsg As String
Dim MyDB As DAO.Database
Dim rstZip As DAO.Recordset
  
strMsg = "'" & NewData & "' is not in the list.  "
strMsg = strMsg & "Would you like to add it?"
  
[B]If Len(strMsg) <> 5 Then
MsgBox "Number must be 5 digits long", vbRetryCancel
    Cancel = True
 End If[/B] 
    
   
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Zip Code") Then
  Response = acDataErrContinue      'Display no Error Message
  Me![Combo53].Undo             'Cancel the Entry
Else
  Set MyDB = CurrentDb()
  Set rstZip = MyDB.OpenRecordset("ZipCode Test", dbOpenDynaset)
  
  rstZip.AddNew
  rstZip![FacilityZipCode] = NewData     'ADD New Zip Code
  rstZip.Update
  
  Response = acDataErrAdded   'Item added to underlying Recordset and the Combo
                              'Box is Requeried, Item added to List
  rstZip.Close
  Set rstZip = Nothing
End If
  
End Sub

[ADezii]

Try:

If Not IsNumeric(Me![Combo53].Text) Or Len(Me![Combo53].Text) <> 5 Then
  MsgBox "Number must be 5 digits long", vbYes, "Invalid Entry"
   Response = acDataErrContinue
    Me![Combo53].Undo
     Exit Sub
End If

[NeoPa]

Look more closely at lines #10 - through #13.

I'm not sure what you mean by hitting Cancel, but you should be aware that the way you call the MsgBox() function causes any response to be dropped/ignored.
Setting Cancel = True also seems to be entirely unused. Unless you have a variable called Cancel defined anywhere in your code that you haven't posted. If so then we can't possibly determine what is actually going on. If not, then it is doing nothing, and worse than that, indicates you haven't even compiled your code before posting. In case that is news top you I'll copy in some instructions that should always be followed before posting code for help :

When posting any code on here please :

1. Ensure you have Option Explicit set (See Require Variable Declaration).
2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
4. Ensure that the code in your post is enveloped within CODE tags. The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.

If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.

[slenish]

ADezii you are the Man!! That worked perfectly. Now im trying to break down the code and just see how it works so i can understand it better. Really apperciate the help!

Im playing around with the code some just to see how it works and I took out the line "If Not IsNumeric(Me![Combo53].Text) Or" and it still worked. Just wondering what does this line do, and is it needed for something im not seeing?

Thanks again!

NeoPa,
sorry about the cancel = true part. I was trying to figure out how to make it so if you hit the cancel button it would cancel out and go back to the main screen. I just copied what I had been doing. I was trying a lot of things. Instead I needed what ADezii posted "Me![Combo53].Undo" I did figure out that I could get it to close on command by using DoCmd.close. Sorry If i posted that wrong. I am trying my best :D

[NeoPa]

Sorry If i posted that wrong. I am trying my best :D

We can always work with posters who try. No worries.

I hope those few hints help you in future though. Option Explicit and attempting to compile your code can save so many otherwise wasted questions.

[NeoPa]

Im playing around with the code some just to see how it works and I took out the line "If Not IsNumeric(Me![Combo53].Text) Or" and it still worked. Just wondering what does this line do, and is it needed for something im not seeing?

That's actually quite important. It ensures that the operator doesn't, accidentally or otherwise, enter any characters that are not digits. Otherwise "ABCDE" would be an acceptable entry. You probably wouldn't want to allow the code to continue if the operator had entered that.

[slenish]

Hi NeoPa,

thanks for the information about that line. Now that does make more sense as to why that is there.

A question for you also reguarding that line?? Now since it says IsNumeric does that mean if I had a box where I wanted only letters to be entered I could change that to IsText or IsString and it would understand only letters?

[NeoPa]

No. But if you type in "is" + Ctrl-Space in the IDE you'll see a whole list of methods that start with Is...

I'll leave you to explore.