Using Public Declared Var in Query Builder

[ItsDouglas]

On the premise there is no dumb question. And I have searched the forums and cannot find exactly what I need to know here.

I have a publically declared variable in a module called 'publicdecs'

Public varResClass As Integer

I populate this variable within a form. Now I need to use this variable's value in a very simple query being used as RowSource of a cmbobox on the form:

cmbobox rowsource: SELECT EntryTypes.txtType, EntryTypes.numResClass FROM EntryTypes WHERE (((EntryTypes.numResClass)='" & varResClass & "'));

When it's run I receive Data Type Missmatch errors. When I change the syntax to:
[Forms]![Main]![varResClass} and DO NOT surround with & or quotes, I do not get any errors but I do not get any data returned either. I realize I could just populate this rowsource from the Form_Load event but I would prefer to make this work. I know it is something VERY simple I am missing here. (I have confirmed that data types are the same)

Thanks

 

[DanJR]

have you tried it with the & and without the single quotes?
[tt](((EntryTypes.numResClass)=" & varResClass & ")); [/tt]

 

[ItsDouglas]

DanJr,

Thanks for the quick reply. Yes, I just tried that and received the following error when trying to drop down the cbobox list on the datasheet:

'Data type missmatch in criteria expression'

Typically I do not have this kind of difficulty when using the Query Builder to create my SQL statements as the differences between it's syntax and writing them directly in code are minor. It seems almost as it is interpreting the quotes as definining the varResClass as a string. Frustrating as all get out..

Thanks though

 

[glalsop]

You can't directly reference a public variable from a query. You will need to create a wrapper function in a code module (not a form code module) that returns the variable, and use that:

Public Function GetResClass() As String

GetResClass = varResClass

End Function

Then you can reference the function:

SELECT EntryTypes.txtType, EntryTypes.numResClass FROM EntryTypes WHERE (((EntryTypes.numResClass)='" & GetResClass() & "'));




-Gary

 

[citychap26]

Please please please you correct naming conventions

Public iResClass As Integer

Or

Public vRecClass As Variant

You then need to establish whether the value is a string or inface an integer.

if string '" & strValue & "'"

if int .... Argh, i've forgotten and now too tired to look it up.... will finish it tomorrow!!

Sunil

 

[ItsDouglas]

Gary,

Wow, Ok. So there is something I didn't know. Thank you. I made the changes you suggested yet still receive the "Data type missmatch in criteria expression". Both the Public variable and the EntryType.numResClass are declared as Integers. Yet your function declares as a string. So I changed the 'GetResClass() as String' to be 'GetResClass() as Integer. Unfortunately this still generates the same error.

FYI: I used my ErrorManagement module for the time being to locate this function. It is not in a form module.

Again, thank you for your help. I am beginning to think that the problem lies elsewhere (not sure where that could be though) because even after implementing your entire change I still get the same error.

 

[ItsDouglas]

Sunil,

Goood advice and you are correct. To my humble defense, however, I am merely trying to work through some design challenges right now and hence did not pay much attention to naming conventions. That's a bad habit on my part though. I should at least clean it up before posting here amongst the experts! ;-)

Perhaps I will just declare this as a variant just to make this easy for now and see if that changes anything.

 

[ItsDouglas]

Thanks to everyone that helped me with this. I think it was a combination of these answers that fixed this. Trial & Error.

In case anyone is interested, here is how I got it to work:

Used Gary's suggestion and created the funtion he suggested but changed it to an Integer instead of a String.

Then, within Query builder in the Criteria row within the EntryTypes.NumResClass column, I have this:

GetResClass()

No formatting at all. When I look at the SQL view in the Query Builder, here is the syntax displayed:


SELECT EntryTypes.txtType, EntryTypes.numResClass
FROM EntryTypes
WHERE (((EntryTypes.numResClass)=GetResClass()));

Thanks again for the help!!!!!

Doug