1 form to 4 tables 1

[childlead]

hi.

i have 4 tables with the same exact fields but i want to have my one form be able to access all 4 tables. is there any way that i can do this? i was envisioning maybe a dropdown menu that would allow me to choose what table i want for the form to access. thank you for your help.

 

[jebry]

Hi!

In the after update event of the dropdown box put:

Me.RecordSource = YourCombobox.Value

Make sure the combo box isn't bound to anything

hth
Jeff Bridgham
bridgham@purdue.edu

 

[THoey]

My first question would be why do you have four tables with the exact same fields??? I think you need to step back to the design stage BEFORE this task becomes unbearable...

Maybe you have one table per location or some other qualifier. Create one table with the above mentioned fields and then add another column that distinguishes the location. You can easily create a query that excepts a parameter to display just one locations data...

If that is not the reason, let us know... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[childlead]

Thoey,

that sounds like a plan. i'll just use one table and add a column that distinguishes the location. how would i create that query that you're talking about? thanks for your help.

 

[jebry]

Hi Terry!

Excellent response! Forgot to ask why, didn't I!

Jeff Bridgham
bridgham@purdue.edu

 

[THoey]

Thanks Jebry...

As to your question, it would help to have a little more information. But, as a guess, first thing I would do is create your tables:

[b]tblLocations[/b]
LocID             <-- Primary key (ex. 0001)
LocDescription    <-- (ex. San Antonio Office)

[b]tblDetails[/b]
RecordID          <-- Primary key
...
...
...
LocID             <-- Foreign Key to tblLocations

Then, I would create a form that would have a drop down listbox that was loaded by a query from your tblLocations table:

SELECT LocID, Locdescription FROM tblLocations

This listbox would only display the Description field (size of the LocID field = 0), because most users don't know ID numbers.

Once they select a location, I would have a button that would call your form and pass the LocID selected as a parameter to a query that select records just for that location:

SELECT * 
FROM tblDetails
WHERE LocID = [MySearchForm]![MyComboBox].Value

Something like that... I always end up playing around with it for a little bit to get it to work...

Hope that helps... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

Oops, I guess you can't put bold text inside a code block. Ignore the [ b ] and [ /b ]... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...