Adding Values from Combo Box to Value List 1

[kampspropane]

I am in the process of creating an inventory tracking database and need help!

I have one table with a field named "Size". My boss would like this field to be a combo box but also add to it while entries are being made. I found a post in the FAQs, however it is not working with my database.

Any help would be greatly appreciated. I have been looking at it too long and am affraid I have lost my mind.

Thanks in advance for any suggestions!
Dora

 

[pgmm]

Is it not possible to set the property to limit to list false?
Regards

 

[kampspropane]

Thanks for your input. When I first started this I thought it was that easy, but after I realized it will let you type in a new "Size" however, it will not add it to the list. That would work fine if I can get everybody to be consistant in their data entry. They just like making my job harder.

Dora

 

[Rick39]

This is some code that I use in the "On Not In List" event that allows users to add new entries to a combobox:

Sub AC_Tail_Number_NotInList(NewData As String, Response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String

strMsg = NewData & " is not an available choice. "
strMsg = strMsg & vbNewLine & "Do you want to add it to the list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new number?&quot = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset)
' On Error Resume Next
rs.AddNew
rs!YourField = NewData
rs.Update

If err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub

Hope this helps

 

[kampspropane]

Thank you for your help. I finally got it working. Works like a charm now. I am sure my boss will come up with a new interesting thing for me to figure out.

Dora

 

[kampspropane]

Ok I ran into another problem. This is the code I used:


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

Response = acDataErrContinue

If MsgBox("The size" & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then

Combo12.RowSource = Combo12.RowSource & ";" & NewData

Response = acDataErrAdded

End If

End Sub


My problem is when I close the form the new entries are no longer in the combo box. Any ideas?

Thanks again for all of the help!

Dora

 

[Lightning]

The problem is that the changes you are making are only valid while the form is open. Once you close the form, those changes vanish without trace.

You will actually have to open the table that your combo box list is drawn from, add a new record to that table, and then close the table for your changes to be permanent.

Rick39's code in full, modified to include your table and field names, will do what you require.

HTH
Lightning

 

[DTLaBo]

Why not create a query that pulls the Size information from your main data table to populate the combo box instead of using a seperate field. That way you can still add new values while allowing your users to view the existing data from the Size column.

When you create this query make sure you set Query Properties " Unigue Values" to Yes. This will only show each entry one time in the combo box (Example: If in the size column of the main table you have Size 5 listed 10 time, by setting the "Unique Value to yes will display Size 5 one time in the combo box instead of 10 times).

 

[kampspropane]

I tried using the code and I get a runtime error '3022' and it stops at rs.Update . Any ideas?

Thanks again,
Dora

 

[Rick39]

Might check for missing references (DAO Object Library) under Tools->References

 

[Lightning]

This is the error description for Error Code 3022:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

So, try running the code again using a value you know is not already in the table.

To always generate the error description, change your error handling code like this:

If err Then
MsgBox err.number & ": " & err.description
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub

This will at least tell you what error has occurred and give you the first steps to rectifying the problem during development.

HTH
Lightning

 

[skd2726]

TO RICK39:

HELP - I tried your idea and it lets me enter a new data, but I don't get the message box and it doesn't keep the entry in the table...here is the code I added to my combo box in my form

Private Sub Craft_NotInList(NewData As String, Response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String

strMsg = NewData & " is not an available choice. "
strMsg = strMsg & vbNewLine & "Do you want to add it to the list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new number?&quot = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset(CraftTypes, dbOpenDynaset)
' On Error Resume Next
rs.AddNew
rs!Craft = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub

 

[Pangloss14]

Hi,

I'm running into a related problem and was wondering if anyone had any insight. In my case, my form's source is the table that I'm trying to modify, so I don't need to open recordsets et al. (I believe). The following is my code for handling the NotInList event. This combo box also serves to look up other values.

Private Sub Combo12_NotInList(NewData As String, Response As Integer)
If MsgBox("Would you like to add new patient?", vbYesNo + vbQuestion, _
"Patient not found&quot = vbYes Then
DoCmd.OpenForm "frmNewPatient", acNormal, , , acAdd, acDialog, NewData
Response = acDataErrAdded
DoCmd.Close acForm, "frmNewPatient"
Else
Response = acDataErrContinue
End If
End Sub

The frmNewPatient (a pop up) takes the info from the combo box, and adds it along with other fields in that form. This form's source is also the table that I'm trying to modify so I don't open and recordsets etc. This is all the code in that form.

Private Sub cmdOK_Click()
Me.Visible = False
End Sub

Private Sub Form_Load()
Me.ID = Me.OpenArgs
End Sub

So when I'm done here, I'm returned to the original form. The new ID exists in the combo box, but the associated lookups aren't being updated. I'm sure it's a simple solution so if anyone would like to hit me over the head with the obvious solution, that would be much appreciated.

Thanks