Parameter Query in VBA 1

[Junkie88]

Hello,

I am trying to write some VBA code so that the user of my database does not have to enter the parameter to the query. I have tried unsuccessfully two attempts to do that. neither one is working. Can someone help?

First way:

Option Compare Database

Private Sub cmdDialysis_Click()
Dim qdf As DAO.QueryDef 'Name of the pre-defined Query
Dim rst As DAO.Recordset

'then we'll open up the query:
Set qdf = CurrentDb.QueryDefs("qryQuestions")
qdf.Parameters("[Please enter Questionnaire ID]") = 8


Set rst = CurrentDb.OpenRecordset("qryQuestions", dbOpenDynaset) //I am getting error at this line, I also tried this without any parameters and as qdf.OpenRecordset and using "qdf.Execute dbFailOnError" instead

End Sub


Second way: In this case, the criteria of QUESN_ID field of the qryQuestions was set to =GetValue().
Option Compare Database
Option Explicit
Public numValue As Integer

Function SetValue(num As Integer)
numValue = num
End Function

Function GetValue()
GetValue = numValue
End Function

Private Sub cmdDialysis_Click()
Dim num As Integer
num = 8
Call SetValue(num)
DoCmd.OpenQuery "qryQuestions", , acReadOnly
End Sub

 

[PHV]

Why not simply use a TextBox to store the parameter value ?
In the query you get the value like this:
[Forms]![your form name]![your parameter textbox name]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KenReay]

Hi

You say " I have tried unsuccessfully two attempts to do that. neither one is working"

OK give us a clue

what actually happens, do you get an error message, what does it say?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Junkie88]

For the first method, I have specified that I am getting error at the last line. If you look at my post above, I have written some comments in the last line of the first method that I am using. The error I am getting is saying, Microsoft Office Access database engine cannot find the input table or query '2'. Make sure it exists and that the name is spelled correctly. The name of the query is spelled correctly, I checked.

The query itself is a simple query which is extracting three different values from one table. The criteria for the first field is the parameter that the user is required to enter. In the criteria field, I have written [Enter Questionnaire ID].


In the second method, I am getting error at DoCmd.OpenQuery "qryQuestions", , acReadOnly. The error is a run-time error. It says undefined function 'GetValue' in expression.

 

[MajP]

Need to make sure your function is a public function

 

[Junkie88]

Making the functions public did not do the trick.

 

[MajP]

It says undefined function 'GetValue' in expression.

This means either you do not have a function named GetValue or it can not find it.

So check your spelling
Make sure it is public
And it should be in a standard module or if it is in a form module the form needs to be instantiated (i.e. open).

It is pretty easy to debug. In the immediate window type
?getValue()

 

[Junkie88]

Thanks MajP. I guess my problem was that I did not put the two functions (GetValue and SetValue) in a separate module file. Now it is working!

 

[Junkie88]

I would be nice if someone can also explain what was wrong with the first method.