I have ten tables and want to run a date query on one of them. I'd like to have a form with two combo boxes--one to select the table and one to select the date. I know how to do the second part, but don't know how to use a combo box to tell the query which table to use.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Choosing a Table from a Form 1
- Thread starter JKoval
- Start date
-
1
- #2
It depends on what you want the query to return. Are you displaying the results of the query as a datasheet? Are you passing the results into a form or subform? Are you updating one of 10 tables based on the date selected?
If you want to use the same update query against 10 different tables, the easiest way is to bring the query into your code behind the form as a SQL statement. For example:
DoCmd.RunSQL "UPDATE [" & Me.cboTableName & "] SET field = something WHERE date = #" & Me.cboDate & "#;"
If you are bringing the results up in a form, you can pass the tablename to the form in OpenArgs, then in the Open event of the form, use the value in OpenArgs to change the form's RecordSource.
If you want to do the equivalent of an OpenQuery, I'm at a loss for a good way to do this, unless you create a form that uses Datasheet as the default view and follow my form suggestion above.
Let me know what you finally do!
If you want to use the same update query against 10 different tables, the easiest way is to bring the query into your code behind the form as a SQL statement. For example:
DoCmd.RunSQL "UPDATE [" & Me.cboTableName & "] SET field = something WHERE date = #" & Me.cboDate & "#;"
If you are bringing the results up in a form, you can pass the tablename to the form in OpenArgs, then in the Open event of the form, use the value in OpenArgs to change the form's RecordSource.
If you want to do the equivalent of an OpenQuery, I'm at a loss for a good way to do this, unless you create a form that uses Datasheet as the default view and follow my form suggestion above.
Let me know what you finally do!
- Thread starter
- #3
I sincerely appreciate the help, but I'm now realizing that I'm in way over my head. I have just a small amount of experience modifying this application written by someone else, and have spent lots of time with Access Help, but I can see I have a long, long way to go.
In case you're curious, the object was to produce a packing list for a trucking company from a set of ten inventory tables (one for each of their suppliers). When items are shipped, they put the ship date into the "date out" field of the appropriate table. My idea was to design a form that asked for the supplier and the ship date and then rany a query against that table and produced a report of items shipped that date.
Thanks again.
In case you're curious, the object was to produce a packing list for a trucking company from a set of ten inventory tables (one for each of their suppliers). When items are shipped, they put the ship date into the "date out" field of the appropriate table. My idea was to design a form that asked for the supplier and the ship date and then rany a query against that table and produced a report of items shipped that date.
Thanks again.
Given your limited Access knowledge, the best thing for you would be to create 10 identical reports, each one going against a different table. In your form, call one of the 10 reports based on the supplier chosen on the form. It's not the most elegant, but it's the easiest. When you call the report, add a WHERE clause to limit the report's output to a specific date.
- Status
Similar threads
- Question
- Locked
- Question