Switching between Forms

[Keith]

I have an Orders form and if a customer is not in my customers table I want to be able to enter a new customer and then return the details to the orders form. I hope that this makes sense.

 

[Carol]

The following should work for you:

Place the first coding as an Event Procedure in the On Not in List in the properties for your CustomerID Combobox:

Private Sub CustomerID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub

Place this second coding as an Event Procedure in the On Double Click in the properties for your CustomerID Combobox:

Private Sub CusomerID_DblClick(Cancel As Integer)
On Error GoTo Err_CustomerID_DblClick
Dim lngCustomerID As Long

If IsNull(Me![CustomerID]) Then
Me![CustomerID].Text = ""
Else
lngCustomerID = Me![CustomerID]
Me![CustomerID] = Null
End If
DoCmd.OpenForm "Customers", , , , , acDialog, "GotoNew"
Me![CustomerID].Requery
If lngCustomerID <> 0 Then Me![CustomerID] = lngCustomerID

Exit_CustomerID_DblClick:
Exit Sub

Err_CustomerID_DblClick:
MsgBox Err.Description
Resume Exit_CustomerID_DblClick
End Sub

What this does is take you into your Main Customers Entry form by double clicking the combo box. Once you add the customer details it requeries so that the information is available for use.

Note: You should change the field names accordingly. I have used CustomerID for your combo box in your current form and Customers as your Customers main input form name.

Good luck