NotInList help (1 Viewer)

[LauraBee]

OKay...here goes my explanation. I'm creating a database where the main form has a combobox. If the user types in something new, a second form opens to allow the new item to be added to the database. This works great. When that second form is closed, the initial form does not update. The user has to close the form and start over to get the new item included in the combobox. Help!?!?!?!

 

[jatfill]

you would want to use the the "requery" command after you have added the new item... maybe on the OnClose event of the form that adds the item?

 

[Peter Paul]

Another neat option is one I got from the following website http://www.candace-tripp.com/_pages/HTML/default_HTML.asp.

' This code was modified from code I got from Candace L. Tripp ctripp1@ earthlink.net
Dim ctl As Control
Dim strSQL As String

' Return Control object that points to combo box.
Set ctl = Me![FieldName]
' Prompt user to verify they wish to add new value.
If MsgBox("Name is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to products table.
NewData = (NewData)
strSQL = " INSERT INTO TableName( [FieldName] ) SELECT '" & (NewData) & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

exit_cboProductID_NotInList:
Exit Sub

err_cboProductID_NotInList:
If Err = 2113 Then
Err = 0
Resume Next
Else
MsgBox Str(Err)
MsgBox Err.Description
Resume exit_cboProductID_NotInList

End If

This will display a message box when a new option is entered asking if the new name should be entered into the table. If the user clicks OK then the name is added to that table, and is reflected immediately.

So, this goes in the NotInList event for the field. Change the field name to what you need, and define the table in the code where it says TableName. Lastly, on the properties for the field, change the Limit To List property to YES if it is not already.

Good luck,
Peter

 

[LauraBee]

Problem solved...thanks Peter Paul!!!