How to retrieve data from an Union query

  • Thread starter Thread starter Booster
  • Start date Start date
B

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
 
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
Click to expand...

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?
 
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.
 
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
Click to expand...

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
 
Hi John,


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

Marc.
 
Booster said:
Thanks for your help, but I already thried this methode
I also have tried your code and I get error 3061
Click to expand...

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
 
Back
Top