Parameter Query in VBA

[Junkie88]

I have been trying to write some VBA for a parameter query so that I am not required to enter the parameter via an input box.

I tried the first method (the parameter value is criteria comes from txtQuesnID.Text (txtQuesnID.Value), but am getting this error:
Microsoft office cannot move the focus to the control txtQuesnID.

When I try it with the following line removed:
txtQuesnID.SetFocus
I get this error:
You can't reference a property or method for a control unless the control has the focus.

Private Sub cmdDialysis_Click()
Dim num As Integer
num = 8

txtQuesnID.SetFocus
txtQuesnID.Text = num
DoCmd.OpenQuery "qryQuestions", , acEdit
DoCmd.OpenForm "frmQuestionnaire", acNormal
End Sub


Here is my second method, where I get the parameter value by calling GetValue in the criteria section of the query. But it does not work, a input box still shows up asking me to enter a value for my parameter field.

Option Compare Database
Option Explicit
Public numValue As Integer

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

Public Function GetValue()
GetValue = numValue
End Function

Private Sub cmdDialysis_Click()
Dim num As Integer
num = 8

txtQuesnID.SetFocus
txtQuesnID.Text = num
DoCmd.OpenQuery "qryQuestions", , acEdit
DoCmd.OpenForm "frmQuestionnaire", acNormal
End Sub

 

[bubba100]

You might take a look at QueryDef and Parameters.


Dim db as database
Dim prm as Parameter
Dim SelectQry as QueryDef
Set db=CurrentDB
Set SelectQry=db.QueryDefs("YourQuery")
For Each prm in SelectQry.Parameters
prm.Value=Eval(prm.Name)
Next prm

not tested.

 

[PHV]

Replace this:
txtQuesnID.SetFocus
txtQuesnID.Text = num
with something like this:
Forms![name of open mainform]!txtQuesnID.Value = num

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