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

  • Thread starter Thread starter Don
  • Start date Start date
D

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
 
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.
 
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()
 
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
 
Back
Top