Parameter error in Subform Combo box

[elizabethcke]

Hello,

I remember having this problem before but can't remember how to fix it. I have an orders form that has two combo boxes which are both filtered based on what is selected in another combo box. The subform works fine alone but once I add it to my main form as a control, I get requests for parameters. I've named the subform the same, frmOrders, but still no luck. I tried setting the focus to the subform and nothing. The combo boxes that are filtered are cboSize and cboColor based off the selection in cboItem.

In the filtered combo boxes query for criteria under Item# I have [Forms]![frmOrders]![cboItem].

I have the following code in the cboItem's After Update

Private Sub cboItem_AfterUpdate()

Me.cboSize = Null
Me.cboSize.Requery
Me.cboSize = Me.cboSize.ItemData(0)

Me.cboColor = Null
Me.cboColor.Requery
Me.cboColor = Me.cboColor.ItemData(0)

End Sub

I realize the subform is acting as a control in the main form but I have no idea how to fix it.

Can anyone tell me what I am doing wrong. I've attached the database so you can check it out. Any help is appreciated. Thank you! :banghead:

 

[pbaldy]

The syntax to refer to the form changes when it becomes a subform:

http://www.mvps.org/access/forms/frm0031.htm

 

[elizabethcke]

Thank you. Still confused. So in my subform where I have my criteria for the Size combo box Row Source should change from [Forms]![frmOrders]![cboItem] to what? Sorry, I'm really blanking here.

 

[pbaldy]

To the full reference following this syntax:

Forms!Mainform!Subform1.Form!ControlName

 

[elizabethcke]

Okay, I put the reference as follows:

[Forms]![frmMain]![frmOrders].[Form]![cboItems]

in the Size combo box Row Source in my subform. Now I don't get any parameter request error which is good but nothing populates in the drop down box. Thanks again for all your help!

 

[elizabethcke]

Wait, it's working! I had an extra "s" in there. Thank you again! I appreciate your help!

 

[pbaldy]

Ah good, glad you got it sorted out. I just got back from some errands.

 

[riverphoenix]

Hi! I am not quite understanding what mine would look like...

I created a MainSubForm with a Combo Box (name = cboMedName, query = MedicationLookupQuery) and Run Button that points to a Report (name = MedicationsPatientsReport, query = MedicationsPatientsQuery).

When I run the reports directly from the MainSubForm, they run perfectly. The report pulls up the results showing the specific medication chosen.

I placed the MainSubForm into the MainForm. When I run the reports from there, I get a parameter error: "Forms!MainSubForm!cboMedName".

Question: How do I avoid this error when running the report from the MainForm?

[Form]![MainForm]![MainSubForm].[Form]![cboMedName] ? If so, where do I put this? In the MedicationPatientsQuery?

 

[riverphoenix]

That didn't work :-/

 

[riverphoenix]

got it!

[Forms]![MainForm]![MainSubForm].[Form]![cboMedName]

 

[pbaldy]

Glad you got it sorted out and welcome to AWF.

 

[riverphoenix]

I actually ran into another issue I can’t seem to figure out :-/


If I select Med1 from the combo box , click run, get the report with accurate results, click Home (back to MainForm) in the report, change to Med2 and click run again, the report still displays results for Med1. If I were to click close instead of home, and then run, the report will display results for Med2.

not sure where the issue is here but ideally would be that Med1 results wouldn’t appear. It would “reset”.

 

[pbaldy]

I'm not sure I'm visualizing the problem. If you've changed the combo value, the report should use it. Can you attach a sample db here?

 

[arnelgp]

report don't refresh, so you need to Close and Re-open it to see changes.

 

[riverphoenix]

@arnelgp right!

@pbaldy
So let’s say I chose Tylenol > Ran Report > Tylenol Results > Home button on report > Chose Advil > ran Report > Tylenol Results

essentially not refreshing the results to the new selection (advil).

 

[pbaldy]

Again, can you attach a sample db here? Reports in report view can be refreshed/requeried, but I can't tell how you've got this structured.

 

[riverphoenix]

@pbaldy attached.

 

[pbaldy]

Which report? The first one seems to perform exactly as you desire. What exact steps do I take to recreate your problem?

 

[riverphoenix]

All the reports.

It is working right now because I removed the "Home" button (like you see on other forms) and renamed the "Close" button to "Home". There should be a Home and Close button on each report.

So if I had left the Home button, I run the report > report with correct results > select Home > re-run report with different medication > previous medication appears.

 

[pbaldy]

Not sure how I'm supposed to fix something that isn't broken. What did the missing button do differently? If it left the report open then as was mentioned the report would still reflect the previous selection. Since it's in report view you could requery it instead of opening it if it's already open, but you'd probably have to switch to VBA. I'm not sure that can be done with a macro, I don't use them.