Hi Dazed,
The simple answer is that, in the form where you add new service records,
you have a combo box where you select customers. The combo box lists
customers, by name, alphabetically. The user selects a customer name.
'Hidden' inside the combo box's functionality is the fact that, for each
customer name displayed to the user, there is also the customerID for that
customer name. The user is selecting the customer name, but what's really
getting stored in the underlying record is the customerID associated with
that customer name. The net effect is that the user gets something to work
with he understands (customer names), and access gets something to work with
that's the most efficient way to represent and save customers to records
(customerID).
To accomplish that, you use an sql select query as the rowsource of a combo
box. You set the rowsource type to 'table/query'. You set the column count
to '2', you set the 'bound column' to '1' (in the example that follows), and
you set the 'column widths' to '0;1"' (remove all SINGLE quotes from the
previous sentence when inputing values).
You would, of course, set the 'recordsource' of the form to a table or
query, and the 'control source' of the combo box to the field in that
table/query that represents the customerID data.
All those settings are in the properties window, and all but the
'recordsource' are in the properties window related to the combo box. You
get the properties window open by hitting the f4 key. Open your form in
design mode, hit f4, and you open the properties window with the form itself
as target of the properties window. That means all the properties shown in
the properties window relate to the form itself (the form as an entity in and
of itself). If, in design mode, you select the combo box control, then open
the properties window (or, if you already had the properties window open and
select the combo box control), then all the properties you'll see in the
properties window relate to the combo box.
Back to the rowsource. A select query used for the rowsource, given the
example you gave of your customers table, would look like this:
Select CustomerID, CustomerName from Customers Order by CustomerName;
That is, in the real world, most likely a bit simplistic because you've got
a field called 'customerName' in the customer table when, in reality, you'd
likely have separate fields for first name, last name, middle name. A more
realistic sql select statement might look like this:
Select CustomerID, Trim(LastName & ", " & FirstName & " " & MiddleName) As
CustomerName from Customers Order by CustomerName, CustomerID;
That produces a "firstname, lastname middlename" formatted customer name
list (along with their customerID numbers).
Anywho, so you stick that select statement into the 'rowsource' of the combo
box, and make all the other settings as stated above, and now you have a
combo box control that will store an accurate list of your current customers,
display the list using the customer names, but store the choices made by the
user utilizing the customerID.
Neat, huh?
At some point, when you get all that working, then you'll have to deal with
the situation where you need to add a new customer. You can do a search for
"NotInList" events and find code that will help you address that situation.
Bear in mind: IF you've already designated the customer in question in
another form that's currently open, you can simply pull that customer data
from one form to the other (second) form. That is NOT an uncommon situation,
so think about whether or not your situation requires a CHOICE, or if your
situation requires an ACKNOWLEDGEMENT of a PREVIOUSLY MADE choice (AND the
result of that previous choice is easily accessible to you).
It bascially breaks on this assessment: In a 'main' type form where you are
creating independent/New/main records, you'll definitely need a combo box.
For example, if your application is an orders tracker, and each order
requires you to add the related customer to it, then you'll need a combo.
In a form that creates RELATED records to a main record (displayed in a form
currently opened), you often can skip the combo box and just pull the choice
from the main form to a hidden control in the related form.
That is confusing, I realize. Here's an example:
If you have a 'files' database, and you want to relate a list of people to a
file. Your main record is 'file', your related record is FilePerson So the
files table has a one-to-many relationship with this second table that will
store people related to the file.
Well, to add a person to a file, you don't need to have a combo box that
people use to select which file you are adding people to, because you will
open the file record in the 'main' form, then click on an 'add related
people' button on that main form that will open a (second) form to add the
people. The addRelatedPeople form will get the 'fileID' it needs from the
already opened main file form. You don't need to 'select' a file, because
that choice has been 'previously made' when you opened the main form to the
file in question.
In contrast, you WILL need a combo box on the second (addRelatedPeople) form
for users to select the person they want to add to the file. Why? Because
that choice has NOT already been made (it is, in fact, the whole point of the
second form).
Wow, that answer got a bit out of hand...hope it's not too confusing.
Anway, hope you find some value in here.
CW
