I am building an order placing database...
I have a table with gear categories, and a separate related table with gear sizes and associated part numbers. On the order form I have two combo boxes synchronized so when a gear category is selected in the first, the size list of the category is imported into the next field's combo box. I am trying to figure out how to have the part number automatically imported into a field once the category and size are selected. Any ideas?
Thanks, Bobby

Use DLookup

Set unbound control to

=Dlookup("[PartNo],"tblSizesandParts","[Size]=Forms![FormNameYouAreOn]![Size]")

Each size in the size table is unigue correct, since you said that the Category of the gear is related in each table.

If you need to save the Dlookup value, have a control on your form that shows say... "PartNeeded" On the AfterUpdate part of your second combobox, run SetValue to PartNeeded to Forms![FormYouAreOn]![DLookupControlName]

Messy the way I wrote it, but it will do your trick.

DLookup is not the way to go .... much too slow

First do not store the PartNumber in the table ........
This goes against the "Rules of Relational Database Normalization". The second combobox should be bound to the table and only store the Record ID from the Lookup Table. Then to get the PartNumber to show for viewing purposes, you need to have the RecordID, Size, and the PartNumber included in the Row Source query for the second combobox. The RecordID can be hidden along with the PartNumber in the combobox if wanted. Now to get the PartNumber to populate the txtbox in your form, use:

=Me.[SecondComboName].Column(2)
(change "SecondComboName" to your combo name)

This will reference the 3rd column of the Row Source query.

Aggregate Functions such as Dlookup should be avoided if possible.

HTH
RDH

[This message has been edited by R. Hicks (edited 08-26-2001).]

R. Hicks:

Where should the PartID be stored? Are you saying all the records should be stored in one table?

I was so thrilled with DLookup when I first learned it, you're busting a bubble here, but so be it. Is it OK to use for display purposes when you don't need to store a value?

[This message has been edited by jwindon (edited 08-26-2001).]

Domain Aggregate functions are notoriously slow. In a small db consisting of a few hundred records, it will not cause a noticable problem. But in tables that can contain thousands of records, using these types of functions can have a large effect on the gathering and processing of information. If the result can be derived by the use of SQL or a query this will much, much faster. In the case of this question a Row Source is used by the combo that can include the needed information, so why not use it.

As for storing the Part Number in the main table, this is the basic idea of a Relational Database, not to store redundant data. All of the information for the Gear should be stored in a seperate table with a Unique Identifier assigned to each record in this table. Then all that is needed to access any of the information stored about the Gear can be readily access through Relational Joins using links from Primary Keys to Foreign Keys in the database. So if I store the ID 4 in the main data table I can, through joins, get the PartNumber, Size, Description, Width, or any other piece of information stored in the Gear Table for the record that has 4 assigned to it's ID field.

HTH
RDH

[This message has been edited by R. Hicks (edited 08-26-2001).]

R.Hicks:
Where in the text box properties do I enter the expression you suggested?
Thanks for the help..Bobby

If you have included all the info into the Row Source query for the combobox I decribed in my earlier post, then in the txtbox's Control Source property put:

=Me.[SecondComboName].Column(2)

This is assuming that the PartNumber is the 3rd column of the Row Source query for the combobox.

In the above example I have named the combobox "SecondComboName". Replace this name with the actual name of your combobox.

HTH
RDH

PartId may be the correct field to store. It depends on what uniquely identifies the part record. To store the PartId, make it the bound field of the second combobox. In this scenerio, the first combobox would not be bound because you would not also need to store the gear category.

As to the DLookup(), Microsoft frequently uses it in its sample code because it is self-contained and easy to understand. However, you will find it to be extremely slow when used in queries or reports or when proccessing a recordset in code. On a form, where you are only accessing a single record at a time, you probably won't notice how slow it is. There is almost always a better way to replace its functionality. In queries it is replaced by a join to the lookup table. In forms it is either replaced by a join in the form's bound query or by including the field you are looking up in the query that you are using to fill a combobox as in this particular question.

R.Hicks:
I got it to work. Thanks a lot for your help.
Bobby

Great ..... You are welcome.

And thanks to Pat Hartman with the help explaining everything. It's amazing how she can say the same thing as I do in about half the words ..... and make a whole lot more sense .... LOL

RDH