Using function as query criteria

[latex88]

I don't know what's common practice, but I've been using functions to pass values to queries as criteria. I would often set a global variable from a dlookup function or from a record selected in a form, then I'd pass the value to a query. I'm not sure if this not a good practice or something is wrong with my app. I would often get the error message of "Unknown Access database engine error" when the queries are triggered. What baffles me is that I do not always get that message. Is there a better and stable way to pass values to queries? Below is an idea how I set the variable and passing it to a query.

' This line is declared in a module
Public strOrderID as Integer

'Below may be inside a form when a record is selected
strOrderID = dlookup ("MyOrderID", "MyOrderTable",....)

Public Function OrderID ()
OrderID = strOrderID
End Function

 

[gemma-the-husky]

I do that, but tend to set the global value before executing the query

I suspect the lookup may be failing. if no record is returned you will be trying to set strorderid to null, which will not work. add some error trapping

maybe just using nz() will suffice.

strOrderID = nz(dlookup ("MyOrderID", "MyOrderTable",....) ,0)

 

[latex88]

Thanks for responding, Husky. I check the variable and the functions before running the query. Both returns the values I expect, but I still get that error message.

 

[Solo712]

Thanks for responding, Husky. I check the variable and the functions before running the query. Both returns the values I expect, but I still get that error message.

Latex,
I suspect that your issues stem from your having a double reference to "OrderID", ie. as a field and as a function. Definitely not a good idea.

The way I usually pass run-time values as query criteria is that I tweak the row source by inserting the criteria restriction at run time and then requery the set.

In the example below a list box of budget items is restricted to values used by a specific condo (specified by variable 'glbCID'). The routine is run as part of the OnLoad event. If the global variable changes with each record, this routine needs to be placed in (or called by) the OnCurrent event.

'-------------------------------------------
   ' Requery the Contract Budget Items list
   ' to filter by current Condo ID
   '-------------------------------------------------
 
    SQLstr = Me!ContractOBItem.RowSource
    ordrstr = InStr(SQLstr, " ORDER")
    wherestr = InStr(SQLstr, " WHERE")
    SQLStr2 = Mid(SQLstr, ordrstr)
 
    ' this removes result of previous filter
    ' from the new SQL string
 
    If wherestr <> 0 Then ordrstr = wherestr
 
    ' expunges the previous WHERE clause if any
 
    SQLstr = Mid(SQLstr, 1#, ordrstr - 1)
 
 
    SQLstr1 = " WHERE qryContrItems.OBCondoID = " & glbCID
 
    SQLstr = SQLstr + SQLstr1 + SQLStr2
 
    Me!ContractOBItem.RowSource = SQLstr
    Me!ContractOBItem.Requery

Best,
Jiri

 

[latex88]

Hi Jiri,

Actually, the I name my functions typically like OrderID_Func (). I just generically made it for the example above. I suspect the naming is not the issue, as I have seen this error message in other queries when I pass the value via functions. Again, I don't always get the errors. I also reset the variables to 0 or "" after I'm done with the queries. Shall I something else additional?

Recently I learned that TempVars can be quite useful for what I'm doing. Since I've been using them, I've not seen the error messages, until when I try to create recordsets with TempVar as criteria. I get something like "The expression On Click you entered as the event property setting product the following error."

 

[Solo712]

Hi Jiri,

Actually, the I name my functions typically like OrderID_Func (). I just generically made it for the example above.

Hi latex,
What does the criteria for the OrderID actually show in the QBE rubric ? the name of the function ? an expression ?

Recently I learned that TempVars can be quite useful for what I'm doing. Since I've been using them, I've not seen the error messages, until when I try to create recordsets with TempVar as criteria. I get something like "The expression On Click you entered as the event property setting product the following error."

I suspect whether you use tempVars or global variables, you will run into problems if the reference is unclear or the function returns Null or the data type returned is a mismatch.

BTW, the code that I have shown you works well with form's controls. With the form itself, Me.Filter and Me.FilterOn = True is all I ever needed. See example here.

Best,
Jiri

 

[latex88]

What does the criteria for the OrderID actually show in the QBE rubric ? the name of the function ? an expression ?

Please see the image I attached earlier. The sql for this example is

SELECT tblOrderCurrentHeader.OrderID
FROM tblOrderCurrentHeader
WHERE (((tblOrderCurrentHeader.OrderID)=OrderID()));

BTW, the code that I have shown you works well with form's controls. With the form itself, Me.Filter and Me.FilterOn = True is all I ever needed. See example here.

My browser gave me a severe warning about this link you provided concerning malware.

 

[Solo712]

My browser gave me a severe warning about this link you provided concerning malware.

The link is an Access World Forums post #184997.

J.