Subform combobox to only show records picked by mainfrm combo? 1

[Goger]

I think I need some hand-holding, I've searched but can't seem to find anything written to my level (uh, low).

I'm trying to setup a DB to catalog our injection molds at work. Each customer can have many molds, each mold can have one or many parts in it. So, I have three tables: Customers, Molds, and Parts. You can probably guess that I have relationships: Customers-Molds 1-many, Molds-Parts 1-many.

What I would like to have is a main form (frmCustomers) with a combo-box to choose the customer. Then a sub-form (displaying as a Single Form), sfrm1Molds, that has a combobox that the user can use to choose one of the customer's molds. And of course, have the record in the sub-form jump to that mold's info. There would also be a sub-subform (sfrm2Parts) that lists the parts in the chosen mold... it can be a datasheet.

Now I can get it to work if I have a combo on the main form and datasheets on the subforms, everything updates nicely. But it's not what I'm after, if I change the sfrm1Molds to a Single Form and put a combobox on it, all I get in the combo's list is a listing of all the molds in the DB (not just the customer that was chosen in the combo on the main form). The nav-buttons or the sfrm1Molds seems to work just fine, the only records browsable are the ones chosen by the main form's combo.

I must say I'm new to Access, I'm just using wizards to add subforms and make combos.

I think I need a requery but I'm unsure exactly where that should go and exactly what it should requery.

Thanks in advance!
-Todd

PS: I'd be happy to e-mail the DB to whomever might want to see the whole thing...

 

[mkallover]

To base the molds combo off of the customer combo make the Row Source Type "Table/Query" and the Row Source of your second combo-box something like:

[code}
SELECT Mold FROM tblMolds WHERE CustomerName=Forms!frmCustomers![ComboName];
[/code]

Then, on the After Update event of your Customers combo-box do:

Forms!frmCustomers![ComboName].Requery

Or something like that to get you started at least. I didn't test this but it should be close.

 

[mkallover]

To base the molds combo off of the customer combo make the Row Source Type "Table/Query" and the Row Source of your second combo-box something like:

SELECT Mold FROM tblMolds WHERE CustomerName=Forms!frmCustomers![ComboName];

Then, on the After Update event of your Customers combo-box do:

Forms!frmCustomers![ComboName].Requery

Or something like that to get you started at least. I didn't test this but it should be close.

 

[Goger]

Oh man, I'm getting close! Thanks so much, mkallover!

Now, for some reason, my "molds" combobox does not refresh (requery?) if I go back up and click on a different customer. Perhaps I didn't add the requery correctly, I just added it as a line towards the end of the subroutine already there, like so:

Private Sub Combo2_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer] = '" & Me![Combo2] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Forms!frmCustomersMain![Combo2].Requery
End Sub

Is that not OK? Hmmm. At least it didn't crash.

-Todd

 

[Goger]

Well, thanks to mkallover, I've got it working now. I just didn't quite understand that the requery points to the combobox in my subform... my requery statement should have been:

Me!sfrm1Molds.Form![Combo2].Requery

Of course, making that same combobox look at the mainform combo to get its row source kinda helped too.

Thanks again,
-Todd