Solved

MS Access Forms with Multiple Record Sources

Posted on 2008-10-31
5
943 Views
Last Modified: 2013-11-28
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 .)
0
Comment
Question by:tjgquicken
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22850785
If you are selecting different fields - as you appear to be doing- then you should see if you can use a use a main form /subform structure.

If you were selecting the same fields then probably you would use a union query to bring the two results into a single list.
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22851045
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
0
 

Author Comment

by:tjgquicken
ID: 22851622
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.
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22851809
the union query requires that the structures of ALL tables/queries being merged are exactly the same.
0
 
LVL 10

Accepted Solution

by:
LennyGray earned 250 total points
ID: 22851814
Use multiple subforms, then
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question