By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,149 Members | 885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,149 IT Pros & Developers. It's quick & easy.

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

100+
P: 283
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!
Mar 13 '10 #1

✓ answered by ADezii

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo53_NotInList(NewData As String, Response As Integer)
  2. Dim strMsg As String
  3. Dim MyDB As DAO.Database
  4. Dim rstZip As DAO.Recordset
  5.  
  6. strMsg = "'" & NewData & "' is not in the list.  "
  7. strMsg = strMsg & "Would you like to add it?"
  8.  
  9. Me![Combo53].LimitToList = True
  10.  
  11. If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Zip Code") Then
  12.   Response = acDataErrContinue      'Display no Error Message
  13.   Me![Combo53].Undo             'Cancel the Entry
  14. Else
  15.   Set MyDB = CurrentDb()
  16.   Set rstZip = MyDB.OpenRecordset("Test Table 2", dbOpenDynaset)
  17.  
  18.   rstZip.AddNew
  19.     rstZip![IN Facility Zip] = NewData    'ADD New Zip Code
  20.   rstZip.Update
  21.  
  22.   Response = acDataErrAdded   'Item added to underlying Recordset and the Combo
  23.                               'Box is Requeried, Item added to List
  24.   rstZip.Close
  25.   Set rstZip = Nothing
  26. End If
  27. End Sub

Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 13 '10 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 13 '10 #3

100+
P: 283
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMetals_NotInList(NewData As String, _
  2.  
  3.  Response As Integer)
  4.  
  5.   'Update value list with user input.
  6.  
  7.   On Error GoTo ErrHandler
  8.  
  9.   Dim bytUpdate As Byte
  10.  
  11.   bytUpdate = MsgBox("Do you want to add " & _
  12.  
  13.    cboMetals.Value & " to the list?", _
  14.  
  15.    vbYesNo, "Non-list item!")
  16.  
  17.   'Add user input
  18.  
  19.   If bytUpdate = vbYes Then
  20.  
  21.     Response = acDataErrAdded
  22.  
  23.     cboMetals.AddItem NewData
  24.  
  25.     'Update RowSource property for versions
  26.  
  27.     'XP and older.
  28.  
  29.     'cboMetals.RowSource = _
  30.  
  31.     ' cboMetals.RowSource _
  32.  
  33.     ' & ";" & NewData
  34.  
  35.   'Don't add user input
  36.  
  37.   ElseIf bytUpdate = vbNo Then
  38.  
  39.     Response = acDataErrContinue
  40.  
  41.     cboMetals.Undo
  42.  
  43.   End If
  44.  
  45.   Exit Sub
  46.  
  47. ErrHandler:
  48.  
  49.   MsgBox Err.Number & ": " & Err.Description, _
  50.  
  51.    vbOKOnly, "Error"
  52.  
  53. End Sub
  54.  
Thanks for the help :)
Mar 14 '10 #4

ADezii
Expert 5K+
P: 8,638
The NotInList() Events exists solely for the scenario which you describe, and would be the solution to your problem.
Mar 14 '10 #5

100+
P: 283
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Combo53_NotInList(NewData As String, Response As Integer)
  3.  
  4.   'Allow user to save non-list items.
  5.  
  6.   Dim cnn As New ADODB.Connection
  7.  
  8.   Dim strSQL As String
  9.  
  10.   Dim bytUpdate As Byte
  11.  
  12.   On Error GoTo ErrHandler
  13.  
  14.   Set cnn = CurrentProject.Connection
  15.  
  16.   bytUpdate = MsgBox("Do you want to add " & Combo53.Value & " to the list?", vbYesNo, "Non-list item!")
  17.  
  18.   If bytUpdate = vbYes Then
  19.  
  20.     strSQL = "INSERT INTO Test Table 2(IN Facility Zip), (field8)  ('" & NewData & "')"    
  21.  
  22.     Debug.Print strSQL
  23.  
  24.     cnn.Execute strSQL
  25.  
  26.     Response = acDataErrAdded
  27.  
  28.   ElseIf bytUpdate = vbNo Then
  29.  
  30.     Response = acDataErrContinue
  31.  
  32.     Me!Combo53.Undo
  33.  
  34.   End If
  35.  
  36.   Exit Sub
  37.  
  38. ErrHandler:
  39.  
  40.   MsgBox Err.number & ": " & Err.Description, vbOKOnly, "Error"
  41.  
  42.  
  43. End Sub
  44.  
Mar 14 '10 #6

ADezii
Expert 5K+
P: 8,638
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo53_NotInList(NewData As String, Response As Integer)
  2. Dim strMsg As String
  3. Dim MyDB As DAO.Database
  4. Dim rstZip As DAO.Recordset
  5.  
  6. strMsg = "'" & NewData & "' is not in the list.  "
  7. strMsg = strMsg & "Would you like to add it?"
  8.  
  9. Me![Combo53].LimitToList = True
  10.  
  11. If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Zip Code") Then
  12.   Response = acDataErrContinue      'Display no Error Message
  13.   Me![Combo53].Undo             'Cancel the Entry
  14. Else
  15.   Set MyDB = CurrentDb()
  16.   Set rstZip = MyDB.OpenRecordset("Test Table 2", dbOpenDynaset)
  17.  
  18.   rstZip.AddNew
  19.     rstZip![IN Facility Zip] = NewData    'ADD New Zip Code
  20.   rstZip.Update
  21.  
  22.   Response = acDataErrAdded   'Item added to underlying Recordset and the Combo
  23.                               'Box is Requeried, Item added to List
  24.   rstZip.Close
  25.   Set rstZip = Nothing
  26. End If
  27. End Sub
Mar 15 '10 #7

100+
P: 283
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo53_NotInList(NewData As String, Response As Integer)
  2.  
  3. Dim strMsg As String
  4. Dim MyDB As DAO.Database
  5. Dim rstZip As DAO.Recordset
  6.  
  7. strMsg = "'" & NewData & "' is not in the list.  "
  8. strMsg = strMsg & "Would you like to add it?"
  9.  
  10. If Len(strMsg) <> 5 Then
  11. MsgBox "Number must be 5 digits long", vbRetryCancel
  12.     Cancel = True
  13.  End If 
  14.  
  15.  
  16. If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Zip Code") Then
  17.   Response = acDataErrContinue      'Display no Error Message
  18.   Me![Combo53].Undo             'Cancel the Entry
  19. Else
  20.   Set MyDB = CurrentDb()
  21.   Set rstZip = MyDB.OpenRecordset("ZipCode Test", dbOpenDynaset)
  22.  
  23.   rstZip.AddNew
  24.   rstZip![FacilityZipCode] = NewData     'ADD New Zip Code
  25.   rstZip.Update
  26.  
  27.   Response = acDataErrAdded   'Item added to underlying Recordset and the Combo
  28.                               'Box is Requeried, Item added to List
  29.   rstZip.Close
  30.   Set rstZip = Nothing
  31. End If
  32.  
  33. End Sub
  34.  
Mar 15 '10 #8

ADezii
Expert 5K+
P: 8,638
Try:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNumeric(Me![Combo53].Text) Or Len(Me![Combo53].Text) <> 5 Then
  2.   MsgBox "Number must be 5 digits long", vbYes, "Invalid Entry"
  3.    Response = acDataErrContinue
  4.     Me![Combo53].Undo
  5.      Exit Sub
  6. End If
Mar 15 '10 #9

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 15 '10 #10

100+
P: 283
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
Mar 15 '10 #11

NeoPa
Expert Mod 15k+
P: 31,494
@slenish
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.
Mar 15 '10 #12

NeoPa
Expert Mod 15k+
P: 31,494
@slenish
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.
Mar 15 '10 #13

100+
P: 283
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?
Mar 15 '10 #14

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 15 '10 #15

100+
P: 283
Thanks NeoPa!!

I pulled up the list and found you use the IsNull function. :) I kept trying for a while anything I could think of such as text, string, word, letters, alpha, and so on.

appreciate the help :)
Mar 16 '10 #16

Post your reply

Sign in to post your reply or Sign up for a free account.