Kevin:
You can do this very easily by modifying the existing Customer Orders form
and saving it under a new name. The modifications required are as follows:
1. Delete the ControlSource of the CompanyName text box.
2. Change the text box to a combo box.
3. Rename it cboCustomer
4. Set up the combo box's properties like so:
RowSource: SELECT CustomerID, CompanyName FROM Customers ORDER BY
CompanyName;
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
5. In the AfterUpdate event procedure of the combo box put:
Dim rst As Object
Set rst = Me.Recordset.Clone
With rst
.FindFirst "CustomerID = """ & cboCustomer & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
6. In the form's Current event procedure put:
Me.cboCustomer = Me.CustomerID
7. Set the form's AllowEdits property to True (Yes in the properties sheet)
so that you can select from the combo box. If you want the form to be read
only set the Country control's Locked property to True (Yes) and its Enabled
property to False (No)
8. If you wish you can of course add controls bound to other columns from
the Customers table to the form and , if necessary, lock/disable them as
above.
9. The combo box will show the company names, although its value is the
hidden CustomerID. The CustomerID column is really of little intrinsic
meaning, essentially being just to provide a set of unique values as the
table's primary key, so would not normally be shown on a form of this type,
but if you do want to see it you can add a text box (locked and disabled
again) to the form with a ControlSource property of:
CustomerID
You'll find that when you select a customer from the combo box the form will
move to that customer record and the first subform will show that customer's
orders. When you click on an order in the first subform the second subform
will show the details for that order.
Ken Sheridan
Stafford, England