Update two queries at the same time from a form

[dannewestis]

Hi,

Not sure where to post this as it covers both queries, forms and partly tables.


ISSUE:
I need to add two records to a query with one record in a form. Is this possible?


BACKGROUND:
This is a database with TV programs and the times they are to be broadcast.

Tables:
One table. "table_programs" contains the information about each program (title, description, length etc.). The other table, "table_times" has the times for the programs (date, start_time etc.), where one program can be shown multiple times.

Query:
There is one query, "query_programtimes", that combines the above two tables + two calculation fields. One calculates the end time of a program by adding the start time (from the times table) and the length (from the programs table). The other calculates a file name, based on the weekday of the date field.

Form:
The form is based on the "table_programs" table, with the "query_programtimes" as the data source for a subform. There I enter a program and in the sub-form one or more times for it to be broadcast.


So far so good. The form adds one record to the query for each time that a program is broadcast.

Now what I'd like to do is to add another record in the query for each scheduled time. That is for entering a file that will be played when a program is finished. That is, it'll take its title from the file name that is calculated in the query and the scheduled programs end time as its start time.

Alternatively, and perhaps even better, this data could be in its own query and then a third query can combine the two to get the full schedule.

Is there any way to do this? That is, one entry in the form is supposed to update two queries at the same time.

Hope I made myself clear...

Thanks!

 

[The_Doc_Man]

1. You cannot do this simply from a form. A form only has one recordsource. You can do this only through VBA code underneath the form, doing direct output to a second recordsource. Besides which, you don't want to update two queries where one could theoretically do the job.

2. You can, perhaps, build a query to drive the form that references multiple related tables and make the form update the single query. Anything in the related tables that needs to be updated should appear in the query. It is OK to have hidden fields in the table that get updated along with the more visible fields. This assumes you have good relationship definitions among the tables that participate in the query.