Calling a Union query with parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I need to use a recordset based on a union query that includes 3 parameters.
The calling instructions are:

Dim Base01 As DAO.Database
Dim ReqPCTUnion As Recordset
Const NmReqPCTUnion = "R_PCTUnion"
Set Base01 = CurrentDb

'Here I manually set the 3 parameters (this will be included in a loop later
on)
'CtrlPropF, CtrlCompteF and CtrlTitreF are 3 controls on the calling opened
form
'and are used in the query as parameters.

rep = FnCritèreNoProp("FermerTrans", 1)
CtrlPropF = rep
rep = FnCritèreNoCompte("FermerTrans", 1)
CtrlCompteF = rep
rep = FnCritèreNoTitre("FermerTrans", 11)
CtrlTitreF = rep

' DoCmd.OpenQuery "R_PCTUnion" 'Works fine and opens the querie correctly

Set ReqPCTUnion = Base01.OpenRecordset(NmReqPCTUnion) 'Produces an error
message


My problem occurs with the Set ReqPCTUnion =
Base01.OpenRecordset(NmReqPCTUnion)
I'm getting an error message from Access saying something like:

"Not enough parameters, 3 expected" (my translation, since I'm using a
french version of Access)

In my debug process, I tried to open the query with the DoCmd.OpenQuery
instruction and everything works fine and opens the query with the proper
values as parameters, no error. So I believe my SQL is OK.

I reproduce here the query SQL, in case someone finds something wrong. The
3 parameters are in the HAVING clause and the WHERE clause.

'R_PCTUnion

SELECT T_ÉtatTitre.NoTransaction, T_ÉtatTitre.NoPropriétaire,
T_ÉtatTitre.NoCompte, T_ÉtatTitre.NoTitre, T_ÉtatTitre.DateTrans,
T_ÉtatTitre.NoTypeTrans, T_ÉtatTitre.EnMain,T_ÉtatTitre.Statut
FROM T_ÉtatTitre
GROUP BY T_ÉtatTitre.NoTransaction, T_ÉtatTitre.NoPropriétaire,
T_ÉtatTitre.NoCompte, T_ÉtatTitre.NoTitre, T_ÉtatTitre.DateTrans,
T_ÉtatTitre.NoTypeTrans, T_ÉtatTitre.EnMain,T_ÉtatTitre.Statut
HAVING (((T_ÉtatTitre.NoPropriétaire)=[forms]![F_MenuPrincipal]![CtrlPropF])
AND ((T_ÉtatTitre.NoCompte)=[forms]![F_MenuPrincipal]![CtrlCompteF]) AND
((T_ÉtatTitre.NoTitre)=[forms]![F_MenuPrincipal]![CtrlTitreF]) AND
((T_ÉtatTitre.NoTypeTrans)<>0) AND ((T_ÉtatTitre.EnMain)=0))
ORDER BY T_ÉtatTitre.NoPropriétaire, T_ÉtatTitre.NoCompte,
T_ÉtatTitre.NoTitre, T_ÉtatTitre.DateTrans;
UNION SELECT T_ÉtatTitre.NoTransaction, T_ÉtatTitre.NoPropriétaire,
T_ÉtatTitre.NoCompte, T_ÉtatTitre.NoTitre, T_ÉtatTitre.DateTrans,
T_ÉtatTitre.NoTypeTrans, T_ÉtatTitre.EnMain,T_ÉtatTitre.Statut
FROM T_ÉtatTitre
WHERE (((T_ÉtatTitre.NoPropriétaire)=[forms]![F_MenuPrincipal]![CtrlPropF])
AND ((T_ÉtatTitre.NoCompte)=[forms]![F_MenuPrincipal]![CtrlCompteF]) AND
((T_ÉtatTitre.NoTitre)=[forms]![F_MenuPrincipal]![CtrlTitreF]) AND
((T_ÉtatTitre.NoTypeTrans)=0) AND ((T_ÉtatTitre.EnMain)=0))
ORDER BY T_ÉtatTitre.NoPropriétaire, T_ÉtatTitre.NoCompte,
T_ÉtatTitre.NoTitre, T_ÉtatTitre.DateTrans;

Since the Docmd.OpenQuery works, I could use that method to feed another
query that would fill a temporary table that I could then use as recordset,
but this seems to be a long, not very efficient way of doing things. I would
prefer to use a recordset based directly on the union query especially since
this portion of code will be included in a loop and be executed a certain
number of passes (~75-100).

I have searched few specific newsgroups without success.

Any suggestion on how to set a recordset based on a union query including
parameters ?

Any help would be greatly appreciated.

Michel
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to declare a QueryDef object to assign the parameters to and to
open the recordset on:

== air code ==
Dim db AS DAO.Database
Dim qd As DAO.QueryDef

Set db = CurrentDb
set qd = db.QueryDefs(NmReqPCTUnion)
qd(0) = FnCritèreNoProp("FermerTrans", 1)
qd(1) = FnCritèreNoCompte("FermerTrans", 1)
qd(2) = FnCritèreNoTitre("FermerTrans", 11)

Set ReqPCTUnion = qd.OpenRecordset()
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmmb44echKqOuFEgEQKJXgCg/FFAxFQpZKTVEz4xXs2w7GgLMKsAn3NH
z5ufhwOGFHUHhSXM2MG2ac0n
=JCsH
-----END PGP SIGNATURE-----


Michel said:
Hi everyone,

I need to use a recordset based on a union query that includes 3 parameters.
The calling instructions are:

Dim Base01 As DAO.Database
Dim ReqPCTUnion As Recordset
Const NmReqPCTUnion = "R_PCTUnion"
Set Base01 = CurrentDb

'Here I manually set the 3 parameters (this will be included in a loop later
on)
'CtrlPropF, CtrlCompteF and CtrlTitreF are 3 controls on the calling opened
form
'and are used in the query as parameters.

rep = FnCritèreNoProp("FermerTrans", 1)
CtrlPropF = rep
rep = FnCritèreNoCompte("FermerTrans", 1)
CtrlCompteF = rep
rep = FnCritèreNoTitre("FermerTrans", 11)
CtrlTitreF = rep

' DoCmd.OpenQuery "R_PCTUnion" 'Works fine and opens the querie correctly

Set ReqPCTUnion = Base01.OpenRecordset(NmReqPCTUnion) 'Produces an error
message
Click to expand...

< SNIP>
 
Mr Foster,

Thank you very much to have taken time to read my long post and reply to it.
I'm very confident your suggestion will work. I'll try it and will come
back to give feed back.

Michel Boivin

"MGFoster" a écrit :
 
Mr Foster,

SUPER ! Your suggestion works fine. All I had to do is to copy/paste the
code you suggested and it worked right a way.

Many thanks, I have been struggling with this prolem for more than a full
week ! Now you solved it. SUPER and thank you very much. You are an ace !

Michel boivin

"MGFoster" a écrit :
 
I have a queston that maybe you can help me with. I have a SELECT qry that I
do a UNION ALL to calculate sums. In my Select qry, my fields are
tbl...[co-number] as Branch, etc. When I try and create my Union All it
states missing op in qry expression. If I build my SELECT just as [co-number]
the UNION ALL works fine.
Here's one of my Unions that work


SELECT
qryBadDebtbyCMCustomernumber.[co-number],
qryBadDebtbyCMCustomernumber.[divn-number],
qryBadDebtbyCMCustomernumber.[cust-number],
qryBadDebtbyCMCustomernumber.[cust-name],
qryBadDebtbyCMCustomernumber.[TranType],
qryBadDebtbyCMCustomernumber.[ref-number],
qryBadDebtbyCMCustomernumber.[as-of-date],
qryBadDebtbyCMCustomernumber.[due-date],
qryBadDebtbyCMCustomernumber.[item-amount],
qryBadDebtbyCMCustomernumber.[Today],
qryBadDebtbyCMCustomernumber.[DSO],


Sum(Current) as [Currents] ,
Sum([1-30]) as [1-30s] ,
Sum([31-60]) as [31-60s] ,
Sum([61-90]) as [61-90s],
Sum([91-180]) as [91-180s],
Sum([181-360]) as [181-360s],
Sum([360+]) as [360s],

qryBadDebtbyCMCustomernumber.[CM],
qryBadDebtbyCMCustomernumber.[CA],
qryBadDebtbyCMCustomernumber.[Internal/External],1 As DetailSection


FROM qryBadDebtbyCMCustomernumber


GROUP BY

qryBadDebtbyCMCustomernumber.[co-number],
qryBadDebtbyCMCustomernumber.[divn-number],
qryBadDebtbyCMCustomernumber.[cust-number],
qryBadDebtbyCMCustomernumber.[cust-name],
qryBadDebtbyCMCustomernumber.[TranType],
qryBadDebtbyCMCustomernumber.[ref-number],
qryBadDebtbyCMCustomernumber.[as-of-date],
qryBadDebtbyCMCustomernumber.[due-date],
qryBadDebtbyCMCustomernumber.[item-amount],
qryBadDebtbyCMCustomernumber.[Today],
qryBadDebtbyCMCustomernumber.[DSO],
qryBadDebtbyCMCustomernumber.[CM],
qryBadDebtbyCMCustomernumber.[CA],
qryBadDebtbyCMCustomernumber.[Internal/External]

UNION ALL SELECT

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,sum([item-amount]),NULL,NULL,SUM(CURRENT),SUM([1-30]),SUM([31-60]),SUM([61-90]),SUM([91-180]),SUM([181-360]),SUM([360+]),NULL,NULL,NULL,2

FROM qryBadDebtbyCMCustomernumber
ORDER BY DeTailSection, qryBadDebtbyCMCustomernumber.[co-number],
qryBadDebtbyCMCustomernumber.[divn-number],
qryBadDebtbyCMCustomernumber.[cust-number],
qryBadDebtbyCMCustomernumber.[cust-name],
qryBadDebtbyCMCustomernumber.[TranType],
qryBadDebtbyCMCustomernumber.[ref-number],
qryBadDebtbyCMCustomernumber.[as-of-date],
qryBadDebtbyCMCustomernumber.[due-date],
qryBadDebtbyCMCustomernumber.[item-amount],
qryBadDebtbyCMCustomernumber.[Today], qryBadDebtbyCMCustomernumber.[DSO],
qryBadDebtbyCMCustomernumber.[CM], qryBadDebtbyCMCustomernumber.[CA],
qryBadDebtbyCMCustomernumber.[Internal/External];
 
Never Mind...Thanks away

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to declare a QueryDef object to assign the parameters to and to
open the recordset on:

== air code ==
Dim db AS DAO.Database
Dim qd As DAO.QueryDef

Set db = CurrentDb
set qd = db.QueryDefs(NmReqPCTUnion)
qd(0) = FnCritèreNoProp("FermerTrans", 1)
qd(1) = FnCritèreNoCompte("FermerTrans", 1)
qd(2) = FnCritèreNoTitre("FermerTrans", 11)

Set ReqPCTUnion = qd.OpenRecordset()
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmmb44echKqOuFEgEQKJXgCg/FFAxFQpZKTVEz4xXs2w7GgLMKsAn3NH
z5ufhwOGFHUHhSXM2MG2ac0n
=JCsH
-----END PGP SIGNATURE-----




< SNIP>
Click to expand...
 
Back
Top