Retrieving data in a form

iangorm1

Board Regular
Joined
Sep 24, 2003
Messages
61
Hi All,

I am new to Access - so please bear with me.

I have 2 tables.

Table 1 has a unique site identifier field for each record. From a form I would like the user to enter the site identifier, which would result in the site address data for that record being retrieved from Table 1. This address cannot be changed on this form, so I assume I will need to display it in a message box on the form?

The user (on the same form) then proceeds to enter other data to update Table 2.

I know how to use a form to input data, but don't know how to get it to recall and display data based upon the site identifier.

Any assistance would be much appreciated.

All the Best,

Ian :confused: [/b]
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

iangorm1

Board Regular
Joined
Sep 24, 2003
Messages
61
Thanks for this Andrew.

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

Best Regards,

Ian
 

ISBB

Board Regular
Joined
May 2, 2005
Messages
101
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..
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top