Criteria Problem

[Gilrucht]

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 and state

The query for my second combobox(cities) has 4 fields: CityID,city, zip, stateid.
I have the following criteria in my cities query in the stateid field:
[Forms]![frm_clientinformation]![cboRegions]. This was taken from a sample db here on the forum which I modified for my db. Thanks, Miles. The samples here are great.

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? Any help would be greatly appreciated.

 

[ScottGem]

If you are copying and pasting the controls the RowSources should also copy and paste. All you would need to change is the form name.

 

[Gilrucht]

Scott I think my first post was confusing. When I said the criteria [Forms]![frm_clientinformation]![cboRegions] was in the stateid field I meant the stateid field in the querybuilder itself not in the stateid field on each form so I really can't just change the name of the form without creating new queries for each form which is exactly what I am trying to avoid.

Pat, I like your idea but am not sure what my globalvariant would be. Am I correct it would be the stateid

I tried:

Public Function ReturnMyValue() As Variant
ReturnMyValue = [StateID]
End Function

 

[ScottGem]

Gil,
I understood exactly where the criteria was. Pat is correct that you would still need to change that form name in the criteria. What I think Pat is suggesting is to use:
=GetStateID()
as the criteria instead of what you have. Then, in the After Update event of the State combo assign the Global like:

gStateID = Me!cboState

 

[Gilrucht]

Pat, Thank you. I'm close but still having a problem. The first combo is working(cboRegions) but the second(cboAreas) is returning no values or asking me for a parameter depending on what criteria statement I use

I created the module as you wrote it. Then I modified the afterupdate event of the first combo StateID is actually named RegionID in everything. When I tried to change the names from Region and Area in Miles downloaded example to States and cites it wouldn't work so I left them at Regions for States and Areas for cities)
---------------------
Private Sub cboRegions_AfterUpdate()
gRegionID = Me.cboRegions
Me.cboAreas.Requery
Me.cboAreas = Null
Me.cboAreas.Enabled = IIf(IsNull(Me.cboRegions), False, True)

End Sub
--------------------


Finally, I added the criteria to my query. I think the error is in my query but I'm not sure what it should be.

Field: Expr1:AreaID Expr2:Area Expr3:Zip Expr4:RegionID
Table: tblAreas tblAreas tblAreas tblAreas
Sort:
Show: Criteria: Where AreaID = gRegionID()
or:


I also tried just " gRegion " with the same result. I'm not getting an error message, Just no values in the second combobox with either of those two. I then tried " "Where 'gRegionID"=[me]![cboregions]" That asked me for the parameter of "=[me]![cboregions]. I also tried Scott's suggested criteriawith the same result. It asked me for the parameter. So I know I am close. I think if I get the right criteria in the query I will have it. I'm just not sure what other criteria to try. Any suggestions?

 

[ScottGem]

Did you create the module EXACTLY as Pat posted it or did you change to accomodoate your actual names? You state used the criteria of:

=gRegionID()

If so your module should have been entered as:

Global gRegionID As Variant

Public Function GetRegionID()
    GetRegionID = gRegionID
End Function

 

[Gilrucht]

Pat,

That did it. Thank you. Much appreciated.