Problem With Combo Box 'Row Source' Query (Scope of VB Variable)

[Don]

I am debugging some code that someone else wrote and have run into what I
think is a scoping problem. The original writer of the code has a combo box
on a form. Using the properties sheet, he defined:

Row Source Type = Table/Query
Row Source = SELECT qryTestCodes.TestCode, qryTestCodes.TestDes,
qryTestCodes.TestCodeOorE, qryTestCodes.TestCodeId, Right('xxxx' &
[DivPassCode],4) AS DivPass, Left([testcodeOorE],1) AS OE,
qryTestCodes.DivLocation FROM qryTestCodes WHERE
(((Left([testcodeOorE],1))=[sOECode])) ORDER BY Right("00000000000" &
Trim([TestCode]),11);

[TestCode] is in a recordset the form has open and I am assuming
[testcodeOorE] is referring to qryTestCodes.TestCodeOorE. The problem is
with [sOECode]. When a Requery is done on the combo box
(cboProblemComb.requery), a "Enter Parameter Value" combo box pops up asking
for a value of sOECode.

sOECode is declared as follows in the VB module for the form:

Public sOECode As String


So, my guess is that the variable sOECode is not visible to the query in the
Row Source. If this assumption is correct, is there a way to make the
variable 'more globally' visible? If the assumption is incorrect, is there
a way around this problem?

Thanks!

Don

 

[Douglas J. Steele]

Queries know nothing about VBA variables.

You can either store the value that's in sOECode in a text box on the form
(it doesn't even have to be visible), or you can use a Public Function in a
module (not in the code behind the form) that returns the value.

 

[Brendan Reynolds]

Variables are never directly visible to queries, regardless of scope. To
place the value of a variable into a query, you can ...

a) build the query dynamically in code ...

dim strSomeVariable As String
Dim strSQL as String
strSomeVariable = "Whatever"
strSQL = "SELECT SomeField FROM SomeTable WHERE SomeOtherField = '" &
strSomeVariable & "'"
Me.SomeComboBox.RowSource = strSQL

b) Make the variable global (declare it using the Public keyword in a
standard module - not a class module, which includes form and report
modules), write a public function to return the value of the variable, and
call the function from your query ...

Public gstrSomeVariable As String
Public Function GetSomeVariable() As String
GetSomeVariable = gstrSomeVariable
End Function

SELECT SomeField FROM SomeTable WHERE SomeOtherField = GetSomeVariable()

 

[Don]

Doug and Brendan,

Thanks for confirming my suspicions!!

I was already leaning towards the hidden text box idea Doug suggested, so I
think I will just follow through on that for this project. The rest of the
insights you both provided will go into my toolkit of things for future
projects!

Thanks!

Don