Passing a variable to a select query.

[Leo Hamal]

Hi all,

I am trying to select cmr from Customer table and pass this variable [Cust]
to a query.
When I open the form to select the customer it gets copied into the
variable. So far no problem.

When the OpenQuery executes, it asks for the parameter again.

Class module linked to my forms option button :
Private Sub Option2_Click()
Dim Cust
Set Cust = Cmr
DoCmd.OpenQuery "CustBal", acNormal, acEdit
End Sub

Custbal query :
PARAMETERS Cust Text;
SELECT TbTrans1.Tranid, TbTrans1.Cmr, TbTrans1.date, TbTrans1.Amount
FROM TbTrans1
WHERE Tbtrans1.Cmr LIKE '*' & [Cust] & '*'
ORDER BY TbTrans1.Tranid;

I tried it with and without the PARAMETERS line ==> same result.

Any ideas how this can be accomplished?

Thanks,
Leo.

 

[Michel Walsh]

The easiest way is probably to use a public function in a standard module
that returns the said variable:


Public Function GetCust() As Long
GetCust=Cust
End Function


and use:

.... WHERE Tbtrans1.Cmr LIKE '*' & GetCust() & '*' ...



Another way is to open the query def and its parameters collection:


Dim db AS Database : Set db=CurrentDb
Dim qdf AS QueryDef : Set qdf=db.QueryDefs("queryName")
Dim p As DAO.Parameter
For each p in qdf.Parameters
p.value = ... ' specify the values here,
' for each parameter, one at a time,
' you are given its p.name
Next p

Dim rst AS DAO.Recordset
Set rst=qdf.OpenRecordset( ....options here... )




Hoping it may help,
Vanderghast, Access MVP

 

[Leo Hamal]

Michel,

When I added your first suggestion, I got a "variable not defined" error
message for Cust in the public function GetCust().

Did not try your 2nd. suggestion as I am not at the level to understand the
code yet.

Any idea?
Thanks,
Leo.

 

[Michel Walsh]

Sure, the variable Cust is the variable that holds your data. *You* have
another name for it, quite probably.


Hoping it may help,
Vanderghast, Access MVP