Use a form in single form view and base the form on the Orders table. Use a
combo box bound to the foreign key field which references the primary key of
the Customers table to show the customer and use a subform based on the Order
Details table to show the order items, linking the subform to the parent form
on the OrderID (or whatever) primary/foreign key fields in Orders and Order
Details.
In fact you'll find exactly this set up in the Orders form in the sample
Northwind database which comes with Access. One thing the Northwind example
doesn't allow you to do, however, is to enter a new customer directly from
the Orders form. To add this facility you'd put the following code in the
CustomerID combo box's NotInList event procedure:
Private Sub CustomerID_NotInList(NewData As String, Response As Integer)
Dim ctrl As Control
Dim strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list of customers?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "NewCustomer") = vbYes Then
DoCmd.OpenForm "Customers", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure Customers form closed
DoCmd.Close acForm, "Customers"
' ensure customer has been added
If Not IsNull(DLookup("CustomerID", "Customers", "CompanyName = """
& _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Customers table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub
For the Open event procedure of the Customers form you'd use the following
to set the default value for the CompanyName field to the name you entered in
the combo box on the Orders form:
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.CompanyName.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub
Ken Sheridan
Stafford, England
