Pass a parameter by code.

  • Thread starter Thread starter Rodolfo Fontes
  • Start date Start date
R

Rodolfo Fontes

Hi,

Sometimes i have to create new queries, just because there's a new
criteria on it.
For Example: I have a querie that list all the countries on a table. But
i need to get just the ones that starts with the letter "A".
This is a simple example, but i think it can demonstrate the trouble.
My question is: Is there anyway to pass the criteria argument by the
code on the form? Or do I have to create a new Querie?

Thanks,
Rodolfo Fontes
 
Hi,

Sometimes i have to create new queries, just because there's a new
criteria on it.
For Example: I have a querie that list all the countries on a table. But
i need to get just the ones that starts with the letter "A".
This is a simple example, but i think it can demonstrate the trouble.
My question is: Is there anyway to pass the criteria argument by the
code on the form? Or do I have to create a new Querie?

Thanks,
Rodolfo Fontes
Click to expand...

Certainly you can pass the criteria. Just set the criterion to

LIKE [Forms]![yourformname]![controlname] & "*"

where controlname is an unbound control on the form containing the
value for which you wish to search.

If I'm misunderstanding the question please post back!
 
Sorry! As i was trying to make the question fast, I've forgot to say that
the criteria argument was to be used on the following sintax:
DoCmd.OpenQuery "C Clientes_por_categoria_(c)_geral"

Is there anyway to pass a paramenter on this case?

Thanks,
Rodolfo Fontes

John Vinson said:
Hi,

Sometimes i have to create new queries, just because there's a new
criteria on it.
For Example: I have a querie that list all the countries on a table. But
i need to get just the ones that starts with the letter "A".
This is a simple example, but i think it can demonstrate the trouble.
My question is: Is there anyway to pass the criteria argument by the
code on the form? Or do I have to create a new Querie?

Thanks,
Rodolfo Fontes
Click to expand...

Certainly you can pass the criteria. Just set the criterion to

LIKE [Forms]![yourformname]![controlname] & "*"

where controlname is an unbound control on the form containing the
value for which you wish to search.

If I'm misunderstanding the question please post back!
Click to expand...
 
Sorry! As i was trying to make the question fast, I've forgot to say that
the criteria argument was to be used on the following sintax:
DoCmd.OpenQuery "C Clientes_por_categoria_(c)_geral"

Is there anyway to pass a paramenter on this case?
Click to expand...

Ah. Ok, that makes more sense!

Don't use OpenQuery; instead, use the Querydef object's Execute
method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
Set db = CurrentDb
Set qd = db.Querydefs("C Clientes_por_categoria_(c)_geral")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
' or you can explicitly set prm(0) to one value, prm(1) to another
qd.Execute dbFailOnError
Set qd = Nothing
 
Thanks for that John!

Just tell me..
let's suppose that on that querie i have the following fields: Code, Product
and Client.
How should the querydef be like to list the criteria for the "code" = 1 ?
 
Thanks for that John!

Just tell me..
let's suppose that on that querie i have the following fields: Code, Product
and Client.
How should the querydef be like to list the criteria for the "code" = 1 ?
Click to expand...

Let's say the Query is

SELECT * FROM tablename
WHERE
Code: 
 = [Enter code:];

If you set prm(0).Value to 1 before the Execute it will pass 1 as the
[Enter code:] parameter.

Alternatively, you can do it without paramters at all, by building up
the SQL string in code:

Dim strSQL As String
Dim lngCode As Long
<do something which sets the variable lngCode to 1>
strSQL = "SELECT * From tablename WHERE [code] = " & lngCode
Set qd = db.CreateQuerydef(strSQL)
qd.Execute dbFailOnError
 
Back
Top