MS Access problem

Oriel

Not quite programming, but hey, close enough.
I'm having some trouble explaining this, but I'll give it a go anyways.

Ok, I've got two tables:

Table One
Table1Key
Table1Field1
Table1Field2
Table1Field3
Table1Field4

Table Two
Table2Key
Table2Field1
Table2Field2
Table2Field3
Table1Key* <--Foreign Key, Lookup

When I make the form for TableTwo, and when I select a value for its Table1Key, how do I get Access to display the relative contents of Table1 for that value?

Hope somebody understands what I mean...

Cheers,
Sinecure

Mick L

Do you mean you want the control for the Table1Key field to be a combo box (drop down list) which displays all or some of the fields in Table1 to aid the user in deciding what value to select?

Oriel

Originally posted by Mick L
Do you mean you want the control for the Table1Key field to be a combo box (drop down list) which displays all or some of the fields in Table1 to aid the user in deciding what value to select?

Em no, I only want the one value (one list of values) there, but when I selected a value (say Record3) then I want to display the fields of Record3 in the same form.

Any clearer?

Thanks,
Sinecure.

Mick L

clear as mud

The confusion is whether you want the values from table one entered into fields in table2 and hence displayed on the form for form2 or whether you just want the values form table1 displayed for information purposes and they have nothing to do with table2.

I guess what I need to know is when the user enters a value in Table1Key relating to Record3 in Table1 do you want that to update Table2Field1,2 and 3 with the values Table1Field1, 2 and 3?

Oriel

I just want to display the information, to make it easier for the user. So, the contents of Table1 wouldn't be changed through Form2 etc, just viewed.

Sound's like it should be simple enough...

Mick L

It is, and there are a few ways of doing it.

1) Add a subform to your form. The subform would be based on table1 and use the Table1Key field as the Link Master Field.

2) Rather than have the Form based soley on Table2, base it on a query which has the two tables linked using a left join so that all fields are shown from Tabl2 but only those from Table 1 where they are equal. Then just add the fields from both tables, making the ones form Table1 read only.

3) Similar to 2 above but if you don't want to change the datasource to a query you could add the fields for table one but base them on dlookups which could lookup the relevant value in table1 using the key field. This could make the form quite slow though, as Access will have to run a whole other query for each dlookup.

If you're not having any luck with these I can send you a sample db showing them.

Oriel

Mick L,
Thanks a lot. I worked out that subform nonsense, and tweaked it to getting looking the way I wanted it.
Heh, if I have any more queries before the end of the assignment, I know who to come to ;-)

Cheers,
Sinecure.