combo box blues!!! I can't add to the drop down list. 1

[jschneider998]

Hello, Can anyone help me? I am trying to figure out a way to be able to add to my combo box dropdown list without having to keep going back to the original table....YIKES!!

 

[scriverb]

Check out the NotInList event procedure and the LimitToList property of the ComboBox. After you have looked at this post back with a little detail about your form and combobox and I will assist you in putting together code to add to the list dynamically.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jschneider998]

Thank you, I have the limit to list property set to "yes" and the code I have for the NotInList event is as follows:

dimMySQL as string
MySQL = "insert into [tblPartNumbers][(part no)] select '"& NewData & "'"
Docmd.runsql MySQL
Combo.Requery

I have a form with several combo boxes on it, my company does work on parts for the automotive industry and we use different part numbers, different descriptions that we are looking for, and different instructions for each part number. i.e. part # 1234567 is looking for dents and we must put green tags on all parts looked at.

I used the above code and I am getting an error message stating that I must save the current field before running the code. Perhaps there is a better way to add items to a combo box?

Thanks again!!

 

[scriverb]

This is the method that I have always used and it works quite well. You must determine if you just want to add the data entered in the combo to the record or add a new record to your table and open a Modal Popup form to allow the user to enter all the required fields for the new record.

Here is the code to just add the data entered so that the new field is now part of the dropdown list and your table is updated:

NotInList Event Procedure VBA code:

Dim vResponse As Variant
If Len(NewData) > [red]30[/red] Then
    MsgBox "The Data entered: '" & NewData & Chr(13) & "is " & Len(NewData) & " characters long." & Chr(13) & "The maximum is [red]30[/red] characters. Please re-enter"
    Me![[i][red]ComboName[/red][/i]] = Null
    Response = DATA_ERRCONTINUE
Else
    NewData = Mid$(NewData, 1, [red]30[/red])
    vResponse = MsgBox("Data entered '" & NewData & "' is not contained in current list." & Chr(13) & "Do you wish to add it to the file?", 36, "New Data Prompt")
    If vResponse = 6 Then
        Dim MyDB As DAO.Database
        Dim MyRS As DAO.Recordset
        DoCmd.DoMenuItem A_FORMBAR, A_EDIT, A_UNDOFIELD
        Set MyDB = DBEngine(0)(0)
        Set MyRS = MyDB.OpenRecordset("[i][red]yourtablename[/red][/i]")
        MyRS.AddNew
        MyRS("[i][red]fieldname[/red][/i]") = NewData
        MyRS.Update
        MyRS.Close
        MyDB.Close
        Response = DATA_ERRADDED
    Else
        Me![[i][red]ComboName[/red][/i]] = Null
    End If
End If

If you need more fields prompted and added to the record then you can insert code to open a form that is setup to just enter the fields you are looking for and upon clicking FINISHED the data would be updated to the recordset new record also.

Let me know if this works for your situation.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jschneider998]

Thanks for your help. I put the code in the NotInList Event and I am getting a compile error on both Dim statements. Saying that the user defined types are not defined. I am very new to VB Code and I am not sure what that means, any ideas?

 

[scriverb]

You are probably using A2k and do not have a library reference made for Data Access Objects. Open up a form or module to a code screen. Then from the Tools menu select References. Now make sure that you have the MS Access DAO 3.6 Object Library selected.

This should clear up the problem.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jschneider998]

Thanks SOOOOOO MUCH!!! I am so happy it works perfectly!!