Storing data from a form problem

[Ken Fedak]

I have a form which gets data from other tables and stores it along with other data in a master table. The data I retrieve from the other tables stores in the master as the data's autonumber.


How do I get it to store actual data from the other tables rather than the autonumber.

 

[Fuga]

It sounds like you are about to store data twice in the db. Are you sure you need to do that?

Fuga.

 

[Ken Fedak]

The data being stored in the other tables is small and is used for every record. The form has 2 combo boxes which choose location and supervisor. When the form is used the actual location and supervisor does show but only the autonumber is stored in the main form.

 

[Fuga]

Well, if you base your form on your main table, containing the fields for location etc, and then use the data from the other tables to populate the combos, it shouldn´t be a problem.

Make sure to have the right fields as control sources. If you use a wizard, you select the field in which you want to store the data.

Fuga.

 

[Ken Fedak]

Fuga

I have the right fields as control sources, would my problem be with relationships. Let me explain Both tables are linked so when I choose location only the supervisors from that location show in the combo box

 

[Fuga]

If you chose location in a combo, does it stor the autonumber in that combo´s field?

If so, you probably have the primary key as the first column in your combos. I think access defaults to this.

Fuga.

 

[Ken Fedak]

Fuga

Thanks

That is exactly what is happening, How do I change the default?

 

[Fuga]

If you use a wizard when you set up the combos, you will be given those options. Otherwise you can change it in the properties. "Bound column" or something like that I think it´s called. I have the swedish version so I´m not sure about the english one.

Fuga.

 

[Pat Hartman]

You are already storing the correct value. You SHOULD be storing the pk of the lookup table. Then in your reports or queries you simply join to the lookup table to obtain the text value.

 

[Ken Fedak]

Thanks Pat

How do I join this information in a Query or report. When I did a query I still recieved the autonumbers.

 

[Pat Hartman]

In the query grid, add the table that contains the "lookup" value. Join the two tables on the common "key" field. Then choose whatever columns you want from either table. Notice that the table name line for the first four fields says table4 and the fifth is table1.

Notice the symbols on the join line - infinity and 1. This is the cardinality of the join and is shown automatically if the tables were joined in a permanent relationship using the relationship window. It is a good idea to to this with related tables because it allows you to enforce referential integrity.

Base all your forms and reports on queries.

 

[Ken Fedak]

Thanks very much Pat its works great