Add New Records with Normalised Data 1

[sweevo]

Hello,

Hopefully this will be a pretty straightforward question...

I have a table, with many values foreign keys to data in other tables.

I want to create a form in order to add new records to this table. I need the form to have drop-downs showing the text values from the other tables, which will then place the relevant key values into the main table.

I can create a query to join all the tables and output the correct data, but when I've tried to make an Add New form, and place drop-downs to show text values from one table in order to place the key in another, I run into problems.

I know how to do this in VB or ASP, but I don't know enough about Access so I'm getting a bit stuck.

Any help would be appreciated. Thanks.

 

[GingerR]

Hi there.

So your form's recordsource should be your 'main' table.
Then from the toolbox pick the combo box tool, and add one to the form. You can run thru the wizard if you want to, to see how they build it for you. It will ask which table you want to fill the combo box with (one of your 'other' tables), and which field in your 'main' table you want the chosen data (key) to go into. So do that once, then take a look at the combo box properties.

The main ones are these:

Row Source: the table/query that fills up the combo box
Control Source: where the chosen data goes into.
Column Count: cooresponds to how many columns of data you want to show in the combo box.
Bound Column: the column from the Row Source that will go into the field of your 'main' table.

So say your main table is Employees, and there's a field of "EmployeeTypeID". Then you have a table called "Types" with fields "TypeID"(autonumber, PK) and "TypeDesc" (text, i.e. Full-Time, Part-Time, Contractor).

So your form's Record Source is table Employees.
One combo box is to pick Type:

Row Source: the table 'Types'
Control Source: EmployeeTypeID (this is the field in the table Employees where you want the chosen info to go into)
Column Count: 2
Column Widths: 0,1 (0 width will hide the PK which is usually just a number that the users don't need to see)
Bound Column:1 (this will put columm 1, TypeID into the main table)

Hope this helps. Try the wizard, fiddle around a little bit, and let us know if you need some more help.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244