How to carry a public variable value set in a form into a query

[rew2009]

I use a query called ClientQRY with a field called “MetroAreaCode” which needs a value of 1 thru 10 to filter geographic regions. I use this query in my forms. I open my forms with a switch board form currently with different control buttons to open identical second forms with different versions of “ClientQry” each with a different MetroAreaCode value for the filter. But instead of having many forms for each region (which I have to change all of them when I make a minor program change) I want to simplify this by using only one second form with one ClientQRY but to set a public variable in the first switchboard form to the value of 1 thru 10 to filter the query used in my second form.

I have created the public variables called “varAreaCode” in the module:

“Public varAreaCode As Variant”

And set varAreaCode=4 in the control button code when opening the second form that uses the MetroAreaCode


I then tried to change the filter in the query by changing

WHERE (((APTS.MetroAreaCode)=4))

To:

WHERE (((APTS.MetroAreaCode)="varAreaCode"))

However, this does not work.

What am I doing wrong?

Thanks

 

[PHV]

Why not simply use the 4th parameter of the DoCmd.OpenForm when you open the 2nd form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rew2009]

I need to pass the variable to the query. The following is the code for opening the second form. Isn't the the fourth parameter the "stLinkCriteria"? How would that pass to the query? I was thinking that my syntax was wrong in the "Where" statement for the query.
Again, I was using - WHERE (((APTS.MetroAreaCode)="varAreaCode"))

Below is the command button code for opening the second form:

Dim stDocName As String
Dim stLinkCriteria As String

varAreaCode = 4 ' This set the AreaCode criteria

stDocName = "MAIN_POPUP_FORM1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks

 

[dhookom]

You can create a small function that returns the public variable. Then use the function rather than the variable name:

Public Function GetAreaCode() As Integer
    GetAreaCode = varAreaCode
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Irish1957]

I think your problem is your variable is inside Quotation marks so the where statement is passing the string "varAreaCode" rather than the content of the string try
"'" & varAreaCode & "'" instead

 

[PHV]

DoCmd.OpenForm "MAIN_POPUP_FORM1", , , "APTS.MetroAreaCode=4"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I agree with PH regarding using the WHERE CONDITION parameter of the DoCmd.OpenForm. This automatically sets the filter property of the form.

I have seen issues with including the table name ie: "APTS." with the field name. Maybe it's only been an issue with reports. I generally don't have the same field name in two different tables in my applications so a table name is rarely necessary.

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

I combined dhookom ‘s and PHV ‘s method and it works for filtering the main Form: DoCmd.OpenForm "MAIN_POPUP_FORM1", , , "APTS.MetroAreaCode=GetAreaCode() " However, I have a number embedded subforms in the main form but there is no “DoCmd.OpenForn” for opening the subforms so I tried to filter the subforms by placing “APTS.MetroAreaCode=GetAreaCode()” into the filter section of the Property Box for the subform. However, this did not work to set the filter for MetroAreaCode in the subform. I did not get any errors but it just did not work. Any thoughts?

Russ

 

[dhookom]

You can put the function as a criteria in the subform's record source query. I assume you don't have the ability to use the Link Master/Child to filter the MetroAreaCode.

Duane
Hook'D on Access
MS Access MVP