Making a Form that is a child table form that shows name values from parent table

[klk_access]

I've been away from Access for a while, my db design has one table that is THE table to fill in, it has all the information about the db and so the user should be able to fill in


the form with pull-downs that show the user-readable name from the parent but the user is actually entering the ID number from the parent table. So the form is an amalgam of other tables (the fields are all children of parent tables), and I want to fill in a form that has pull-downs that are the parent values from the parent tables. In php/html this was easy, since for each field you created the sql statement that got both the ID and text value from the parent form, and the html would show the name value while the id is what was actually passed to the INSERT statement into the child table. I remember back in 1999 being able to do this, but all the Access documentation starts by saying you create a form for the parent and then include children subforms, surely there is a way to do this, I tried playing with the Data tab on the Property Sheet for the form (just took the child table and made a form based on the child table), but can't find what works. I hope people have gone down this road before, thanks

[June7]

The comboboxes are not based on 'child' tables, those are 'lookup' tables.

A parent/child arrangement is like a table for orders and then related ('child') table with details about those orders. The details table would have records for the products selected for the order. The products would be selected in a combobox that uses a 'lookup' table as source for product list.

Can't have records in 'child' table without first establishing associated record in parent table. Whereas lookup tables are not dependent on a 'parent' record.

Use a form/subform arrangement for data entry of parent and child records. Use comboboxes to select values.

[klk_access]

I may have phrased this incorrectly, but I figured it out, when you are creating a form based on a table where you are filling in all the fields of the table, and the table is in essence the the many of the the one-to-many tables that feed into it, you are really filling in ID numbers, and you want the combo-box to show the actual values from the core table so the user does not have to fill in ID values. The way to do this is to make the field properties so it is a combo box, and give the property the Data tab, Row Source as the sql statement from the source table that gives you the ID and the Name Order by Name, then in the Format tab make the Column Count 2 and the column widths 0",3" (for example), so the ID number does not show up but the name does. So the pull-down shows the names in order, and the user can use the pull-down or type in the name and it will auto-fill. So this does work when you have a table that includes datafill that are IDs from core tables.