Filter combo box 1

[MAINOH]

Using Access 2003. I have a sub-form which I am trying to filter a combo box from an entry on the same form.

Specifically:

The user selects a projnum then the 2nd combo box is PONUM. I want the 2nd combo box to be filtered based on the projnum selected.

The PONUM combo box source is a query named qryPORemaining

The 1st box projnum source is the forms source tblSDCF_Detail

Please any help is greatly appreciated.

 

[yummy7]

Hi MAINOH,
The 1st box projnum source is the forms source tblSDCF_Detail ?
Explain it please.

 

[MAINOH]

thanks for the quick reply yummy7.

What I am trying to say is that the 1st combo box for projnum gets it's information(data) from the same table as the rest of the form. It all comes from tblSDCF_Detail.

But the 2nd combo box for PONUM gets the data from a different source. The data for PONUM comes from a query named qryPORemaining.

Hope that makes it a little clearer.

I've tried putting a criteria in the qryPORemaining for PROJNUM of [Forms]![subfmSDCF_DETAIL]![PROJNUM]. But when I select the drop down box for PONUM it says "Enter Parameter Value Forms!subfmSDCF_DETAIL!PROJNUM"

thanks for your help.

 

[yummy7]

if you are trying to make the second combox's(pronum)data source a query so you should select query name in the datasource propery of this combobox.
OR
if you want to decide the selection of data source of this combo after making selection of first combo(projnum)?

 

[MAINOH]

I want PONUM to only show values for the selected PROJNUM.

I don't understand your question about deciding data source? It isn't a matter of deciding the data source. It is a matter of filtering PONUM based on PROJNUM.

I only mention data source - in that they have two different data sources: PROJNUM = tblSDCF_DETAIL
PONUM = qryPO_Remaining.

I think the issue is that when the query executes - the subform does not have focus - so it can't find [Forms]![subfmSDCF_DETAIL]![PROJNUM] to use it for a criteria in the query.

thoughts?

 

[yummy7]

Sorry MAINOH,
I have stil confusions.
1.Are both combo on same form(subform)?
2.I want PONUM to only show values for the selected PROJNUM
on the other hand you have set datasource of pronum a 'query',So it means you have decided pronum to show the only values in query.

Or send me your code to understand what you are trying to do. Also tell me the fields in combo because the whole table is not going to unclose in combo.

 

[MajP]

Take a look at faq702-4289

 

[MAINOH]

MajP - Best I can tell - I have everything as the FAQ you mention, with the exception of the check to see if PROJNUM was input 1st.

Here is my code:

FORM: subfmSDCF_DETAIL

Name: PROJNUM
Control Source: PROJNUM
Row Source Type: Table/Query
row source: SELECT tblPROJECTS.PROJID FROM tblPROJECTS
ORDER BY tblPROJECTS.PROJID;
column count 1


Name: PONUM
ControlSource: PO_NUM
Row Source Type: Table/Query
Row source: SELECT qryPORemaining.PO_NUM, qryPORemaining.AI, qryPORemaining.RemainingHRS, qryPORemaining.[Remaining$] FROM qryPORemaining WHERE (((qryPORemaining.PROJNUM)=[Forms]![subfmSDCF_DETAIL]![PROJNUM])) ORDER BY qryPORemaining.PO_NUM, qryPORemaining.AI;

Events on the PROJNUM combo box:
Private Sub PROJNUM_AfterUpdate()
DoCmd.Requery "PONUM"
End Sub

Private Sub PROJNUM_Change()
DoCmd.Requery "PONUM"
End Sub

 

[MAINOH]

One other thing.

This filtering works fine when I just open subfmSDCF_Detail by it self.

But does not work when I view the form as a subform on my main form.

 

[LawnRanger]

I've used this method, as shown in the FAQ, on several forms with no problem at all. But take that form and implant it on a plain, unbound form as a subform, and the results are different.

You can select the manufacturer fine, but when you click on the model combo box, you get a "Enter Parameter Value" pop-up box. If you enter the value in the pop-up box, it merrily lets you select the model from the selected manufacturer. If someone can tell me how to get around this and make it work on a subform, I would be most appreciative as I have a database project that is stalled until I get past this problem.

As a side note, I don't plan on embedding this kind of form on a plain, unbound form. It's just that it does this regardless of whether it is bound or not.

 

[MajP]

Not sure if this will matter, but replace the
Docmd.requery "controlname"

with
me.controlName.requery

My thought is that the string in the docmd.requery method looks for a control on the active sheet with that name. My default to the main form.

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

But, for me, the best way is to dynamically assign the RowSource property of PONUM in the PROJNUM_AfterUpdate procedure:
Private Sub PROJNUM_AfterUpdate()
Me!PONUM.RowSource = "SELECT PO_NUM, AI, RemainingHRS, [Remaining$] FROM qryPORemaining WHERE PROJNUM='" & Me!PROJNUM & "' ORDER BY 1, 2"
End Sub

If PROJNUM is defined as numeric, get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LawnRanger]

Hehe, while I was posting my reply, I see you posted with the same problem I'm having. I've searched for the answer to this in three different Access books and a VBA book. It seems this is not covered in any of them. I'm sure it is going to be one of those "Doh! Why didn't I think of that." solutions.

 

[MajP]

lawnranger,
Recommend that you start your own post to avoid confusion. Look at the reference PHV posted about referring to controls on main forms and subforms. Your problem lies with how you refer to a control on a subform. Post your code.

 

[MAINOH]

I've tried all suggestions:

I removed the row source from PONUM and changed PROJNUM afterupdate event to:
Me!PONUM.RowSource = "SELECT PO_NUM, AI, RemainingHRS, [Remaining$] FROM qryPORemaining WHERE PROJNUM='" & Me!PROJNUM & "' ORDER BY 1, 2"

Alternatively I also tried using the mainform!subform reference as shown here:
SELECT qryPORemaining.PO_NUM, qryPORemaining.AI, qryPORemaining.RemainingHRS, qryPORemaining.[Remaining$]
FROM qryPORemaining
WHERE (((qryPORemaining.PROJNUM)=[Forms]![frmSDCF_View]![subfmSDCF_DETAIL].[Form]![PROJNUM]))
ORDER BY qryPORemaining.PO_NUM, qryPORemaining.AI;

Finally I also tried changing the requery command as MAJP suggested.

I get the same results with all. It prompts to enter parameter value.

SOUNDS to me like Lawnranger and I have exactly the same problem. So if anyone can solve you will be solving two peoples problems at same time : )

 

[LawnRanger]

Sorry if my problem sounds different, but if you look at MainOH's last post, he is having the same problem as I am. Mine works fine when on a form, but fails when it becomes a subform.

Thanks to PHJ for the reference to the Microsoft article Q209099. The problem is fixed now that I am referencing the combo box correctly. I thought maybe that was my problem all along, but I could not get the syntax correct without help from this article.

Thank you all for your help with this. You've made my day.

 

[LawnRanger]

MainOH, I'll post my SQL statement for the combo box row source that needs the filtered data. My main form is called frmMain, the subform is called frmSub, the table is called tblCities and contains two fields, City and Country. The combo box that selects the country first is called SelectedCountry. Here is the SQL statement...

SELECT tblCities.City, tblCities.Country
FROM tblCities
WHERE (((tblCities.Country)=[Forms]![frmMain]![frmSub].[Form]![SelectedCountry]));

I hope this helps you to fix yours as it fixed mine. Going to lunch and will check this topic when I return.

 

[MAINOH]

Thank you so much to everyone who assisted with this issue.

The answer was in fact the issue talked about in:

http://support.microsoft.com/?kbid=209099

as PHV suggested.

The reason it didn't work the 1st time I tried it is because I was tyring to build query on the subform control source instead of just putting the correct name in the original query and using that as the control source.

Again, I really appreciate everyones time and energy in helping me figure this out!

 

[LawnRanger]

Glad you got yours solved also, MainOH.

You guys are great, and FAST! I'll be checking in here often. Maybe, in the future, I can help someone also.

Thanks again.