Using One Form With Multiple Recordsets

  • Thread starter Thread starter Jane Doe
  • Start date Start date
J

Jane Doe

Hi All,

What I'm trying to do is really pretty simple, but it
isn't working. I have a form that shows all of the orders
for my company -- ever. That's a lot of information, so
I'm trying to make it possible to change the form's
recordsource to see only a small part of the total data.

For example: I have a command button that shows a list of
all customers. When I click on a button next to the
customer's name, I run a query that selects only those
orders for that customer. After the data has been
selected, I would like to reset the forms recordsource to
the results of that query. It seems like it should be
easy, but it's just not working.

Any one have any ideas as to how this should be done?

Should I include the failed code samples?

Thank you SO much for your time!!
 
Jane, here is a really simple, code-free solution.

Presumably you have something like you see in the Northwind sample database:
- a table of Customers, with a CustomerID;
- a table of Orders with an OrderID, and a CustomerID;
- a table of OrderDetails, for the line items in an order;
- a form for the Orders, with a subform for OrderDetails.

1. Create an unbound form (not based on any table), with a listbox on the
left for your customers. Set these properties for the list box:
Control Source {leave this blank}
Row Source {create a query that gives CustomerID and
names.}
Bound Column 1 {Must be the CustomerID field}
Name lstCustomerID

2. Still in form design view, drag your Orders form from the Database Window
onto this new form. Access creates a subform.

3. Right-click the edge-of the subform control, and choose Properties.
In the Properties box, Data tab set these properties:
LinkMasterFields lstCustomerID
LinkChildFields CustomerID

4. Save.

Now, when you select a customer in the list box, their orders are shown in
the subform (with the line items in the sub-subform). The form looks like it
has a navigation pane on the left (actually the list box), so the interface
is familiar to users.


If you prefer do to it with code instead, you can create a SQL string that
selects the records you want, and assign it to the RecordSource property of
your form.

If you are not sure how to get the SQL statement:
- Mock up a query using any criteria you want;
- Switch it to SQL View (View menu in query design), and copy what you see.
 
Back
Top