criteria problem

[G deady via AccessMonster.com]

I have 2 cascading combo boxes . The first lists the 50 states. The second
lists the cities and zipcodes for whichever city is chosen.
The query for my first combobox(cities) has 2 fields: stateid. state

The query for my second combobox(cities) has 5 fields: CityID,city, zip,
stateid.
I have the following criteria in my cities query in the stateid field:
[Forms]![frm_clientinformation]![cboRegions]

This works great in the clientinformation form. My problem is I have other
forms I want to use these cascading comboboxes in. Is there a way to write a
generic or universal criteria, perhaps with a me statement? I'm using copy
and paste so the combobox names are the same in every form. The problem is
the form name in the criteria. Or can the criteria be moved to a property
setting on each form?

 

[Marshall Barton]

I have 2 cascading combo boxes . The first lists the 50 states. The second
lists the cities and zipcodes for whichever city is chosen.
The query for my first combobox(cities) has 2 fields: stateid. state

The query for my second combobox(cities) has 5 fields: CityID,city, zip,
stateid.
I have the following criteria in my cities query in the stateid field:
[Forms]![frm_clientinformation]![cboRegions]

This works great in the clientinformation form. My problem is I have other
forms I want to use these cascading comboboxes in. Is there a way to write a
generic or universal criteria, perhaps with a me statement? I'm using copy
and paste so the combobox names are the same in every form. The problem is
the form name in the criteria. Or can the criteria be moved to a property
setting on each form?

Well, I don't really recommend it because the Screen object
is not guaranteed to be the same form, but if you're not
using a timer event it should be relatively safe. However,
you can use Screen.ActiveForm to refer to the current(?)
form.

 

[G deady via AccessMonster.com]

Well, I don't really recommend it because the Screen object
is not guaranteed to be the same form, but if you're not
using a timer event it should be relatively safe. However,
you can use Screen.ActiveForm to refer to the current(?)
form.

Thank you. I tried "Screen.ActiveForm .cboRegions" in the criteria line of
the stateid in place of Forms]![frm_clientinformation]![cboRegions] but it
produced an error message. I searched the forum for the use of of this
statement but truthfully didn't really understand a lot of what I found. Do
I have to create a public function?

 

[Marshall Barton]

Thank you. I tried "Screen.ActiveForm .cboRegions" in the criteria line of
the stateid in place of Forms]![frm_clientinformation]![cboRegions] but it
produced an error message. I searched the forum for the use of of this
statement but truthfully didn't really understand a lot of what I found. Do
I have to create a public function?

The exact error message might have helped.

I ran my test in AXP and didn't have any trouble. (I assume
the space in your criteria was a typo.)

If you do have to use a function like:

Public Function GetCtlValue(ControlName As String) As Form
GetCtlValue = Screen.ActiveForm.Controls(ControlName)
End Function

Please understand that I am not recommending this approach,
I'm just trying to answer your question.

Other than using a line of code in each form to save the
form's name in some public place, I can't think of another
way to do this.

Time out!

Grasping at straws, I just ran another test where a
dependent combo box's RowSource was an SQL statement
with a WHERE clause like:

. . . WHERE field = CodeContextObject.cboxxx

and it worked!? I don't understand why it worked, because
this is not in the form's module. However, if it is legal
to do this, and it seems to be, the CodeContextObject is
probably(?) more reliable than the Screen object.

 

[G deady via AccessMonster.com]

I apologize. You are right. I should have included the error message. Its
after midnight here now so I will have to wait til tomorrow to try your
suggestions. I do appreciate your time and hekp. I will be sure to let you
know if it works. Again, thank you.

 

[G deady via AccessMonster.com]

Marshall, I wanted to let you kinow I got it working. Since you didn't seem
to like the screenactive even though you gave it to me I ended up creating a
globalvariable in a public function and using the variable as the criteria in
my query. It is working so far.

Global gRegionID As Variant

Public Function GetRegionID()
GetRegionID = gRegionID
End Function

Then "GetRegionID()" as my criteria. Then just added a line of code in the
afterupdate event of the combobox. Thanks for your help.

 

[Marshall Barton]

Marshall, I wanted to let you kinow I got it working. Since you didn't seem
to like the screenactive even though you gave it to me I ended up creating a
globalvariable in a public function and using the variable as the criteria in
my query. It is working so far.

Global gRegionID As Variant

Public Function GetRegionID()
GetRegionID = gRegionID
End Function

Then "GetRegionID()" as my criteria. Then just added a line of code in the
afterupdate event of the combobox. Thanks for your help.

As I indicated elsewhere in the thread, that is the mundane,
traditional way to do this kind of thing. The only
variation is that many folks use a hidden text box on an
always open form instead of a global variable. The reason
for using a form control is that Public (same as archaic
Global) variables are reset on any unhandled errors so they
night make it difficult to run test scenarios.