MS Access - trouble passing variable to function for use in query (1 Viewer)

[notsirk]

Okay, so I'm using a form to launch a query and I need this query to set the criteria based on what is being viewed in the form. I've researched and found that in order to use a variable in a query, you have to pass it to a function.

Here's my code for the form that sets the variable:

Private Sub cmdClassReport_Click()

Dim txtModelClass As String

'uses open form to lookup info in related table and set the variable
txtModelClass = Nz(DLookup("[fleetModelClass]", "tblFleetCopiers", "[fleetAutoID] =" & Me!locFleetIDAuto), 0)

GetModelClass txtModelClass

DoCmd.OpenQuery "qryCopiers"

End Sub

And here is my function:

Public Function GetModelClass(ByVal txtModelClass As String) As String

'variable set in Sub used as value of function
GetModelClass = txtModelClass

End Function

Here is what I have as my criteria in my query:

GetModelClass("«txtModelClass»")

I've verified that the variable (txtModelClass) gets set correctly in the Sub by displaying a message box but my query keeps returning blank.

What am I doing wrong?

 

[boblarson]

Do this:

Create a global variable in a standard module's GENERAL DECLARATIONS section.

Public strModelClass As String

Then the wrapper function

Function GetModelClass() As String
   GetModelClass = strModelClass
End Function

And then your code:

Private Sub cmdClassReport_Click()
Dim txtModelClass As String
'uses open form to lookup info in related table and set the variable
txtModelClass = Nz(DLookup("[fleetModelClass]", "tblFleetCopiers", "[fleetAutoID] =" & Me!locFleetIDAuto), 0)
 
[B][COLOR=red]strModelClass =[/COLOR][/B] txtModelClass
 
DoCmd.OpenQuery "qryCopiers"

And in your query

GetModelClass()

 

[notsirk]

I'm still getting an empty query.

Now, my code is in a form class object whereas the function is in a module- would that be my problem?

 

[boblarson]

The Function and the variable need to be in the STANDARD module (not form, report, or class module).

The click event would be in the form's module.

But now that I was typing that it dawned on me what is happening. You CAN'T do a query like that. I thought you were wanting to DISPLAY the function value in the query and not use it for criteria.

For criteria, you can either change the query's SQL from a QueryDef object in the click event or you can just make a report from that query - getting rid of that criteria and then the code in your click event becomes even easier:

DoCmd.OpenReport "ReportNameHere", acViewPreview, , txtModelClass

 

[notsirk]

Okay, here's what I have now:

Private Sub cmdClassReport_Click()

Dim txtModelClass As String
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = Application.CurrentDb
Set qdf = db.QueryDefs("qryCopiersByClass")

   
txtModelClass = Nz(DLookup("[fleetModelClass]", "tblFleetCopiers", "[fleetAutoID] =" & Forms![frmFleetMenu]!sbfFleetDetail!locFleetIDAuto), 0)

strSQL = Left(qdf.SQL, InStr(qdf.SQL, "HAVING") - 1) & _
" HAVING (((tblFleetCopiers.fleetModelClass)="" & txtModelClass & ""));"

qdf.SQL = strSQL

DoCmd.OpenReport "rptCopiersByClass", acViewPreview

End Sub

It's soooo close to working but for some reason, it puts " & txtModelClass & " into the criteria box. If I take out a set of quotes, it will put in the correct value but as an integer giving me a data type mismatch.

 

[boblarson]

Change this

" HAVING (((tblFleetCopiers.fleetModelClass)="" & txtModelClass & ""));"

To

" HAVING (((tblFleetCopiers.fleetModelClass)=" & Chr(34) & txtModelClass & Chr(34) & "));"

 

[notsirk]

There we go. Thank you so much for your help!