tjgquicken
asked on
MS Access Forms with Multiple Record Sources
Is it possible to include data from multiple record sources in one of my MS Access forms? What I have is a form that I use to assign id codes to client invoices, and I have two queries. One looks like "select * from work_done where invoice_num is null and client_id=[Client ID]" and the other is "select [complicated string manipulation expression] from work_done where client_id=[Client ID] and invoice_num is not null" (it generates the invoice's id code from the date, the client's name, how many previous invoices I sent out this month, etc.). Both share the same [Client ID] parameter and it's the only parameter for both queries.
The problem is that in the Form properties, there only seems to be room for one record source. My question is how do I get two different record sources into my form? (I can't combine them into a single query, I don't think, because one query returns only the rows with null invoice_num and the other needs the non-null invoice_num .)
The problem is that in the Form properties, there only seems to be room for one record source. My question is how do I get two different record sources into my form? (I can't combine them into a single query, I don't think, because one query returns only the rows with null invoice_num and the other needs the non-null invoice_num .)
This comment is not for points....................
If you expect to add or change data in any or all of the individual tables, then you should have a subform for each dataset. The Master/Child properties of each subform will synchronize the data in each subform based upon the main table (where the Client information is displayed).
If you are simply displaying data, then peter57's solution for the union query is the most efficient.
Good Luck!!
Lenny Gray
If you expect to add or change data in any or all of the individual tables, then you should have a subform for each dataset. The Master/Child properties of each subform will synchronize the data in each subform based upon the main table (where the Client information is displayed).
If you are simply displaying data, then peter57's solution for the union query is the most efficient.
Good Luck!!
Lenny Gray
ASKER
I think my problem here is that one of my queries returns a collection of rows, while the other one returns a single value. The union query doesn't seem to like that.
the union query requires that the structures of ALL tables/queries being merged are exactly the same.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you were selecting the same fields then probably you would use a union query to bring the two results into a single list.