Subform linking 2 combo boxes does not work

[bishop0071]

In my main form I have a subform that has 2 combo boxes that links, cmbCategoryName and cmbExerciseName. This works when I got into the frmSubform but when I am in the main form and try it I receive a popup box: forms!frmProgramDesignSubform.cmbCategoryName

Once I put in the cmbCategoryName in the popup box I can then choose the ExerciseName related to the CategoryName.

Help please.

[pbaldy]

The syntax changes when it's a subform:

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

[bishop0071]

Thank you. That worked!

[pbaldy]

Happy to help!

[RonL]

I have used this link many times as a reminder and want to thank you for it.

Two followup questions if I may, also elementary, but which periodically confuse me:

1. When "me" is used within a control event, say the after update event for a combobox which is located on a main form, does it refer to the main form or the combobox?

2. Syntax requirements change when using control variables within a SQL string, eg. one being prepared for use in changing the recordsource of a form. Is there a definitive link/tutorial on this?

Many thx, Ron

[pbaldy]

Me always refers to the object the code is in, ie the form. I'm not clear on the second question. Perhaps this?

http://www.baldyweb.com/BuildSQL.htm

[RonL]

Me always refers to the object the code is in, ie the form. ........

One reason I was asking is the the first line of the following works for me, but the second, as an alternative, does not:

strWhere = " where tblshoots.Shootsid = forms!frmMain.cboShootName"
 strWhere = " where tblshoots.Shootsid = Me.cboShootName"

This is in the construction of a SQL fragment in the afterupdate code of a combobox contained in frmMain. So I'm still not clear whether one can categorically use "me" for the form object in all contexts.

Thanks for the response and the link. -Ron

[pbaldy]

That's because Me is only valid within VBA code. When you build and execute the SQL, it's executing outside VBA; JET/ACE has no understanding of "Me". In other words, VBA is building the SQL but then handing it off to be processed by the database engine. Most of us concatenate anyway, which avoids the issue:


strWhere = " where tblshoots.Shootsid = " & Me.cboShootName

[RonL]

Ah, thanks, that clarifies. I do see a lot of the syntax you show, but have also read that the entire SQL string must be in quotes, so still get confused about when to take that literally.

Interesting that while JET doesn't get "me" it does understand the "forms!myform.mycontrol" Both are object references. Guess that's just the way it is.

Thx, Ron

[pbaldy]

That link explains it better, but when you concatenate you're still building a string, you're just joining literal text with values from variables or form controls. so

strWhere = " where tblshoots.Shootsid = " & Me.cboShootName

resolves to this when passed to the db engine:

where tblshoots.Shootsid = 123

And again, Me is a VBA shortcut to the full object reference; it is only understood within VBA.