Main form and "grandchild" form

[Big Pat]

Hi, I need some help with this, please

I have three tables

SUPPLIERS (1) >->->-> (many) PRODUCTS (1) >->->-> (many) STOCKTRANSACTIONS.

I use a query based on a search string to open frmSUPPLIER, filtered to the record I want. But now I want to have a child form based on the STOCKTRANSACTIONS table, because I want the user to be able to select only products from that supplier.

Option 1 was to have Main form > child form > grandchild form, but that forces me to have the child form set to single view (in order that it can have a child form itself)

But I really want a subform in continuous view like this:

--------------------
| Supplier ..............|
--------------------
| Productcombo.....|
| Productcombo.....|
| Productcombo.....|
--------------------

In effect this would be a parent > grandchild form, skipping the "middle generation". Is such a thing possible and how would I go about it?


The tables are linked as follows (TABLE.Fieldname):

SUPPLIERS.SupplierID >> PRODUCTS.Supplier >> STOCKTRANSACTIONS.Product

Very very grateful for assistance! Thank you.

 

[heedaf]

Have you tried using a subform. It should work the way you want.

 

[CraigDolphin]

Your subform needs a recordsource that provides a key field that can be linked to the SupplierID in your main form.

Often people use tables as the recordsource for forms but, here, that approach would force you to use the 'intermediate' subform that you wish to avoid.

Another approach is to create a query to use as a form's recordsource. In this case, a query of two tables: Products and StockTransactions.

In your query builder, add the two tables you need. Make sure that the ProductID field in the Products table is joined to the ProductID field in the Strocktransactions table.
Add the SupplierID FK field from the Products table to the grid, and then add the rest of the fields from the StockTransactions table to the grid.

Save the query with a name you recognize.

Then when you create your subform, use that query as your recordsource.

When you add the subform to your mainform, use the SupplierID field from both forms as the master/child fields.

HTH

 

[Big Pat]

Thanks a lot for your reply Craig.

What you say makes a lot of sense and when I read it I was convinced it would work. Maybe I've taken a wrong step or misunderstood something, but the subform does NOT limit the choices available in the product combo to those from that supplier. I can still choose any product.

I tried adjusting the query a bit e.g. putting a reference to the supplier ID displayed on the form in the criteria line, but that seems to have had no effect. Even in the query, if I want to add a new record, I'm still allowed to pick from ANY supplier's products.

I've attached a couple of zipped jpegs (only way I could work out how to get them small enough!) The first one shows the query design and you can also see the form with the supplier ID showing. The second shows the query results,which does show only the one matching record from the EXISTING data, but as you can see, when I go to add a new record, it is not limited.

I'm really grateful for your input so far and I'm certain it's NEARLY there, but would you (or anyone else obviously) take another look and see if you can figure out how to make the form combo limited to the relevant supplier's products?

Thank you

 

[CraigDolphin]

Pat,

Ok...there are two things here. The first we've already covered...limiting the display of records in the subform to only those transactions with products supplied by the supplier in the main form.

The second issue is how to limit the products listed in the product combo on the subform to also show only those products that come from that supplier.

Open the properties of the Product combo on your subform. Go to the data tab and look at the rowsource property. Click the ellipse to the right of the rowsource and the query editor should open up showing you the query used to populate the combo list.

In the criteria for the Supplier column (if the supplier field is not already in the grid then add it) add [Forms]![YourMainFormNameHere]![SupplierID] (and uncheck the 'Show' checkbox if you added the field to the grid)

Close the query designer and say Yes you want to save the changes when prompted.

Also, in the on_enter event for the combo you may wish to place some code like
Private sub combonamehere_onEnter()
Me.combonamehere.requery
End sub

This will ensure that if you move to a new record in the main form that the values in the product combo will also change

 

[Big Pat]

Works perfectly. Now that's a result!

Wow, thank you SO much for your patience and your understanding, not to mention your time.

I have learned so much from this forum over the past couple of years, thanks to people like you. Sometimes, I'm just ABOUT to post a question on something that had stumped me, but just typing it in the box and explaining what I've tried already can open up a new mindset and suddenly something clicks and I solve it myself.

This place is a mother-lode of expertise and patience. I'd be lost without it.

Thanks again, Craig.

I'm off to click your scales now!

 

[CraigDolphin]

This place is a mother-lode of expertise and patience. I'd be lost without it.

Indeed it is. I learned most everything I know about Access right here. Glad you got it working.