Retrieving data in a form

Hi Ian

Assuming there is a one to many relationship between the two tables (you can set this under menu Option Tools > Relationships) then do the following :

1. Build a query that includes all of the data from the 2nd table and the fields from the 1st table that you want to see on the form. Include the identifier field from the 2nd table (the field that links it to the 1st table) but don't include the unique ID field from the 1st table (just to avoid any confusion).
2. Save the query.
3. Go into the form design, and change the control source of the form to this query (and not the table). You can get to this by clicking Edit > Select Form, click menu option View > Properties > Data tab > Record Source > use the drop down box to select the query.
4. Make sure each field in the query has a corresponding bound text box on the form (especially the fields from the 1st table).
5. Save the form.
6. Now when you enter the ID into the 2nd table via this form, then the boxes with the information from the 1st table will automatically populate themselves. Given these fields are on the one side of the one-to-many relationship, you won't be able to edit them via this form.

HTH, Andrew

Thanks for this Andrew.

I'll give it a go and let you know how it works out for my situation.

Best Regards,

Ian

I have tried this but cant seem to get it to work...

Table (namelookup) and Table1 ( main data )

I can get the names to display in the form but i cannot edit the data around them... not sure what im doing wrong..

Hi

Do you have the set-up as described in my post? The idea is that you edit the data on the many side of the one-to-many relationship and the data on the one side of the realtionship automatically appears when you enter the ID. The data on the one side of the relationship cannot be edited using this layout. If it still isn't working, can you provide some additional details? Like the SQL for the query, what you are trying to edit and so forth.

Andrew