How to retrieve data from an Union query

[Booster]

Hi all,

I have folowing problem
I made a Union query and want now to retrieve the data with vba code

this seems not to work
Anyone an idea

Thanks

Marc.
Belgium

 

[John Vinson]

Hi all,

I have folowing problem
I made a Union query and want now to retrieve the data with vba code

this seems not to work

Give us some more information, Marc.

What's the SQL of the query? How are you trying to retrieve data? What
isn't working - you get an error message, a compile error, no data,
wrong data, or what?

 

[Booster]

Hi ,

below the union querry this is working fine but I cant retrieve on no way
the data by vba or otherwise
The query shows exactly what i need

SELECT tblAdressen.AdresID, tblAdressen.Bruidegom, tblFreelancers.VoornaamFL
FROM (tblBestellingenUitvoerders RIGHT JOIN (tblAdressen LEFT JOIN
tblBestellingen ON tblAdressen.AdresID = tblBestellingen.AdresId) ON
tblBestellingenUitvoerders.RekeningId = tblBestellingen.RekeningID) LEFT
JOIN tblFreelancers ON tblBestellingenUitvoerders.ploegId =
tblFreelancers.FreelancersID
WHERE
(((tblAdressen.AdresID)=[forms]![frmAdressenbestand]![cboKlantKeuze]));
UNION SELECT tblAdressen.AdresID, tblAdressen.Bruidegom,
tblFreelancers.VoornaamFL
FROM (tblBestellingenUitvoerders RIGHT JOIN (tblAdressen LEFT JOIN
tblBestellingen ON tblAdressen.AdresID = tblBestellingen.AdresId) ON
tblBestellingenUitvoerders.RekeningId = tblBestellingen.RekeningID) LEFT
JOIN tblFreelancers ON tblBestellingenUitvoerders.ploegId2 =
tblFreelancers.FreelancersID
WHERE
(((tblAdressen.AdresID)=[forms]![frmAdressenbestand]![cboKlantKeuze]));
UNION SELECT tblAdressen.AdresID, tblAdressen.Bruidegom,
tblFreelancers.VoornaamFL
FROM (tblBestellingenUitvoerders RIGHT JOIN (tblAdressen LEFT JOIN
tblBestellingen ON tblAdressen.AdresID = tblBestellingen.AdresId) ON
tblBestellingenUitvoerders.RekeningId = tblBestellingen.RekeningID) LEFT
JOIN tblFreelancers ON tblBestellingenUitvoerders.ploegId3 =
tblFreelancers.FreelancersID
WHERE
(((tblAdressen.AdresID)=[forms]![frmAdressenbestand]![cboKlantKeuze]));

Marc.

 

[John Vinson]

Hi ,

below the union querry this is working fine but I cant retrieve on no way
the data by vba or otherwise
The query shows exactly what i need

Let's say this query is saved in the Query window as uniMyQuery.

You should be able to base a Form or a Report on uniMyQuery; it will
have the fieldnames from the first SELECT statement but display
records from all the SELECTs.

In VBA code you can open a Recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("uniMyQuery", dbOpenDynaset)
Debug.Print rs!VoornaamFL
<do whatever you want with the recordset>
rs.Close
Set rs = Nothing

 

[Booster]

Hi John,


Thanks for your help, but I already thried this methode
I also have tried your code and I get error 3061

Marc.

 

[Tony Toews]

Thanks for your help, but I already thried this methode
I also have tried your code and I get error 3061

What's the text for 3061?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[John Vinson]

What's the text for 3061?

Application or User Error, IIRC. Not much help!