Solved: problem with query in VBA

[venkiatmarut]

Hi,

I have a Access application which has following code.

 
Set qry = CurrentDb.QueryDefs("qrySumOfExpenses")
    qry.Parameters("param1") = pubLocation
    Set rec = qry.OpenRecordset

The above code is giving the error 3001: Invalid argument in the above query.

But when I run the query manually in Access DB, it is running fine, I is asking for the parameter and once I entered the paramter, it showing the result well. But in code, it is showing the error.

[venkiatmarut]

Hi,

Please give me any clues to go further.

[Tommy]

I have moved you question to the Access area so it will be seen by the people that answer Access questions.

[CreganTur]

Have you actually defined the parameter within the query? You can do this by looking at the query in design view. Click on Query-> Parameters; this will open a new window where you can declare your parameter and its expected data type. This must be done in order to use the Parameters function.

If you have done this, then perhaps there is a problem with the pubLocation variable you are passing in; wrong data type, possibly.

[hansup]

Set qry = CurrentDb.QueryDefs("qrySumOfExpenses")
    qry.Parameters("param1") = pubLocation
    Set rec = qry.OpenRecordset

The above code is giving the error 3001: Invalid argument in the above query.

If you haven't resolved this, please show us the SQL for qrySumOfExpenses. (Switch to SQL View for the query, and copy and paste the text in here.)

You might also revise your code as follows, then paste in the output from the Immediate Window to help us figure out precisely what is happening:
[vba]Set qry = CurrentDb.QueryDefs("qrySumOfExpenses")
qry.Parameters("param1") = pubLocation
Debug.Print "TypeName(pubLocation): " & TypeName(pubLocation)
Debug.Print "CStr(pubLocation): '" & CStr(pubLocation) & "'"
DoCmd.RunCommand acCmdDebugWindow
Set rec = qry.OpenRecordset(, dbFailOnError)[/vba] Good luck,
Hans

[venkiatmarut]

HI,

Thanks to all of you for your suggestions.

I did observation on this problem and found that
The above query intern calls another query qryExpDetailSum which has a User defined VBA function in HAVING clause. So when I call the query qrySumOfExpenses this intern calling the query qryExpDetailSum and this second query is again calling the user defined function. This UD function is returning a value and it is not catching that value and throwing the error as Invalid argument.

Can you please give me any clues or suggestion
for this to fix?

[venkiatmarut]

Have you actually defined the parameter within the query? You can do this by looking at the query in design view. Click on Query-> Parameters; this will open a new window where you can declare your parameter and its expected data type. This must be done in order to use the Parameters function.

If you have done this, then perhaps there is a problem with the pubLocation variable you are passing in; wrong data type, possibly.

I did like this but still the same problem, Please check my observation and guide me to correct solution.

[hansup]

I did observation on this problem and found that
The above query intern calls another query qryExpDetailSum which has a User defined VBA function in HAVING clause. So when I call the query qrySumOfExpenses this intern calling the query qryExpDetailSum and this second query is again calling the user defined function. This UD function is returning a value and it is not catching that value and throwing the error as Invalid argument.

Does anything in your user defined function or queries refer to a value on a form?

If so, that value won't be available when you run the parent query from code. When you run the same query from the query editor in Access' UI, the Expression service is available to find and retrieve the value from a from and supply it to your query. But the Expression service is not available to a query run from code. You must find another way to supply the value in that situation.

I hope that points you to the solution. It's my only guess which fits the problem you described.

Good luck,
Hans

[venkiatmarut]

Thanks for your reply and I created another query and removed that user defined function and kept control name to the value from the form. Now it is working fine.

Thanks to all for your replies.