Yes. You said you have a command button. In the click event, you would have
something like this:
DoCmd.OpenForm "YourFormName", acNormal, , , , acDialog
Me.YourCombobox.Requery
Alternatively, with a bit more coding, you could use the Not in List event
that Jeff mentioned. There are a few problems associated with doing it this
way though, but here is some code I have used if you want to try it:
'Set the combobox property "Limit to List" to yes. Set
'the Allow Value lists edit to no.
'Make sure you have a reference to DAO.
'Add this code, changing my fields and tables to yours:
Private Sub Combo4_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String
On Error GoTo Err_Combo4_NotInList
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a record, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new record, open a recordset
' using the table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tblEmp", dbOpenDynaset)
' Ask the user to input a new Emp ID.
Msg = "Please enter a unique 3-character" & vbCr & "Emp ID."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("EmpID", dbText, NewID)
' If the NewID already exists, ask for another new unique
' ID
Do Until Rs.NoMatch
NewID = InputBox("Emp ID " & NewID & " already exists." & _
vbCr & vbCr & Msg, NewID & " Already Exists")
Rs.FindFirst BuildCriteria("EmpID", dbText, NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the EmpID field.
Rs![EmpID] = NewID
' Assign the NewData argument to the EmployeeName field.
Rs![EmpNm] = NewData
' Save the record.
Rs.Update
' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
End If
Exit_Combo4_NotInList:
Exit Sub
Err_Combo4_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue
End Sub
Damon
Thanks for responding Damon,
I am pretty much a rookie at this. So please help me a little more, do I
[quoted text clipped - 12 lines]
