How to limit a subform combo to what is on the main form

[DaveMac]

I have a main form with formula. In the main form I have a subform with Goods and qty. I would like whne I select a formula on the main form that the combo on the subform only shows the goods that are within that formula. I do have tables and queries with both formulas and goods so I was trying this with distinct queries but not seeing how to filter down to just what is on the main form.

Thanks for any help

 

[dhookom]

The row source of the combo box on your subform could be something like:

SELECT GoodsID, Goods 
FROM tblGoods 
WHERE Formula = Forms!frmYourMainForm!txtYourFormula
ORDER BY Goods;

You may need code to requery the combo box on current of the main form.

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

So, only one formula at a time can be on the main form? If so, for the subform, you need to use the build tool for the record source for the subform to build a query matching your critieria. Either that or create a query finding what you need, and use that as your record source in the subform.

It sounds pretty simple unless I'm missing something.

If you can tell us:
[ol][li]The table name that contains the data you're pulling into your subform[/li]
[li]The control name on the mainform that contains the formula name/id/whatever[/li]
[li]What is the format of the formula or formulaID - whichever is to be used for comparison[/li]
[/ol]

Then I (or someone else I'm sure) can put together the SQL that should go in your record source field for the subform.

Or if it's more complex, give us some more details to see what all is going into this one.

 

[DaveMac]

the sub form is Tbl_schedule
fields - formula, goods, qty, notes, needed_date

the data for the combo comes from qry_all_goods
fields in qry_all_goods - formula, goods, desc

the main form's data is qry_active_formulas
qry_active_formauls - formula, formula_description

thanks for all the help

 

[DaveMac]

OK I am getting somewhere

this in the source for the combo gets the list limited. But...

now it only shows 2 goods no matter what formual is on the main.

Also as you can see I have gone back and better names my fields.

SELECT qry_all_parts.ProductNumber, qry_all_parts.Description, qry_all_parts.Formula
FROM qry_all_parts
WHERE (((qry_all_parts.Formula)=[Forms]![frmpacklineup]![PrimaryFromula]))
ORDER BY qry_all_parts.Description;

 

[dhookom]

Are you requerying the combo box anywhere?

Duane
Hook'D on Access
MS Access MVP

 

[DaveMac]

No

 

[DaveMac]

When I added this it blows up for some reason.

Private Sub prodno_combo_GotFocus()
[Forms]![frmpacklineup]![PrimaryFromula].Requery
End Sub

 

[DaveMac]

OMG!!!!!! I see that the spelling error is actually a spelling error on my AS/400!!! So I have gone back to make sure I am not hosed up because of this! Still failing ever with the correct incorrect spelling. Damn RPG programmers!!!

 

[DaveMac]

OK so spelling fooxes in place and I am not blowing up but my got focus event does not error out but it does not work right either. I still get the same 2 items no matter what formula I enter

 

[DaveMac]

just tried this and it too fails.

Private Sub prodno_combo_GotFocus()
DoCmd.RunMacro (prodno_requery_bas)
End Sub


made a macro that is a requery of prodno_combo

 

[DaveMac]

Got it. Thanks everyone who does know how to spell

Had several errors. the query, form and control all were not in alignment. This worked once I fixed all the spots

Private Sub prodno_combo_GotFocus()
DoCmd.Requery "prodno_combo"
End Sub

 

[kjv1611]

Glad you got it resolved.