If the form is to be used for inserting orders than it can be based on a query which joins the Customers and Orders tables, but should not be used for inserting data into the Customers table, only into the Orders table. The foreign key CustID column
in Orders should have as its bound control in the form a combo box set up along the following lines if the customers are individuals:
ControlSource: CustID
RowSource: SELECT CustID, FirstName & " " & LastName FROM Customers ORDER BY LastName, FirstName;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm
If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches. The important thing is that the dimension is zero to hide the first column.
Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is
entered:
RowSource: SELECT CustID, LastName & ", " & FirstName FROM Customers ORDER BY LastName, FirstName;
If the customers are corporate entities rather than individuals you will not have to concatenate the name of course, as there will presumably be a single CustName column or similar.
The query can return whatever non-key columns from Customers you wish to show in the form when a customer is selected in the combo box, and controls in the form can be bound to these columns, setting the Locked property of each control to True and its
Enabled property to False to make them read-only.
If the customers are corporate entities with distinct customer names then a new customer can be inserted in the Customers table where necessary by means of the CustID combo box's NotInList event procedure. If they are individuals, however, then the names
cannot be assumed to be distinct as personal names can legitimately be duplicated, so other means have to be adopted, e.g. a 'New Customer' button on the form, which opens a form bound to Customers in dialogue mode at an empty new record. You'll find
an example in NotInList.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.
In this little demo file the opening form includes a button for adding a new contact by opening a form in dialogue mode. When the contacts form is closed the new name is automatically added to the drop down list of the combo box in the form, and
can be selected. Note also how the combo box's list concatenates address data with each contact's names, to differentiate between two or more contacts of the same name.
Also in this demo file you'll find examples of the use of the NotInList event in a variety of contexts. In your case that to add a new city in the contacts form would be an appropriate model if your customers are corporate entities with distinct names.
This opens a form in dialogue mode to insert a new city record, passing the new city name to the form as its OpenArgs property. In the dialogue form the state in which the city is located can then be selected. Note BTW that this demo unrealistically
assumes that city names are distinct. In real life this is not the case of course.
Ken Sheridan, Stafford, England
