Refering To A Control On a Main Form

[CharlieT302]

I have a combo box on a "Subform" that displays a list of vendors. The "County" field in the combo box query must sync up with the County field on the Main form.

Here's what I have:
In the criteria row of Combo Box's underlying query it reads:
[Forms]![Frm Referral Primary]![County Code]

At first, it worked now when I open the form it reads "Enter Parameter Value"

Do I have a syntax problem?

 

[jebry]

Hi!

This is happening because the subform loads before the main form so all of the controls on the subform will be queried before the main form is loaded so the query for the combo box can't find the main form when it runs giving you the parameter error. You can try setting the rowsource of the combo box to the query in the Load event of the main form and requerying it in the Current event of the main form.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[CharlieT302]

Hi Jebry,

"You can try setting the rowsource of the combo box to the query in the Load event of the main form and requerying it in the Current event of the main form."

What would that look like?

 

[jebry]

Hi

In the Load event of the main form:

Me!YourSubFormControl.Form!YourComboBox.RowSource = "YourQuery"

In the Current event of the main form:

Me!YourSubFormControl.Form!YourComboBox.ReQuery

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[CharlieT302]

Alright, I am a little confused. The SQL query that is currently supplying data to the combo box is trying to do what your proposed code is doing... isn't it?

By the way, the query that is attached to the combo box does not exist in the Queries group. It exists only on the combo box.

By the way, thanks for the help.

 

[CharlieT302]

Jebry,

Would you mind walking me through the code. I have not been able to get it to work.

Here is my info:

Main Form = "Frm Referral Primary"
Main Form Field = "County"
Subform = "Frm Referral Foreign"
Subform Combo = "Combo26

This is getting frustrating. Thanks

 

[jebry]

Hi!

Here is the code:

Private Sub Form_Load()

Me!YourSubFormControlName.Form!Combo26.RowSource = "YourQueryAsItAppearsInTheComboBoxNow"

End Sub

Private Sub Form_Current()

Me!YourSubFormControlName.Form!Combo26.Requery

End Sub

Please note that you will be using the name of the subform control on the main form, not the name of the subform. If you didn't change the name then it will be Child and then some number.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[CharlieT302]

Hi Jebry,

Thanks for the help. Forgive me for being a little dense on this issue, but I am not VB expert and am confused on a couple of issues.

1.What do you mean by "YourSubFormControl"? Is this the name of the combo box?

As in...
Me!Combo26.Form!Combo26.RowSource = "YourQueryAsItAppearsInTheComboBoxNow"

2. "YourQueryAsItAppearsInTheComboxNow" Is this the SQL statement that is is the Row Source property? If so, I am assuming that once I have the reference in the code, that I remove the RowSource reference.

 

[CharlieT302]

Eurika!,

I have it working. It turns out my code was working, but in the process of testing, I had incorrectly identified the field whose value I was using to filter my combo box.

However, I want to make sure I understand and that I am not heading for problems.

Here is my situation in recap.

Main Form = "Frm Referral Primary"
Main Form Field = "County"
Subform = "Frm Referral Foreign"
Subform Combo = "Combo26"

1. In the combo box's RowSource (on the Subform), I have the following criteria:

[Forms]![frm Referral Primary].[County]

2. In the "On Current" event of the 'Subform', I have the following code: DoCmd.Requery "Combo26"

That's it. It works. However, subforms load before main forms. I didn't think that should matter. I figured the criteria in the combo box would be ignored until there was Main form data to sync with. Some have suggested (or maybe just surmising since I was having a problem)that the opposite is true and that I would have an error. Apparently (I hope), I was right.

Am I in store for a problem? Also, for reference, I would really like to understand your code from earlier, but have had a hard time with it.

You stated "YourSubformControl" was not the name of the subform. I don't know what you mean.

Also, the "YourQueryAsItAppearsInTheComboBoxNow" line in code. Can you give me an sample using real (or fake) control names?

By the way, thank you so much for your help. This is a terrific site for busy professionals.