Union query - using VBA?

[Guest]

Hi!

I created four simple select queries, Query1, Query2, Query3, Query4 - each
query uses the same table but retrieves a unique set of records. Then i
created a continuous form, and slapped FOUR Checkboxs, box1, box2, box3, box4
onto the forms header. And on their onclick event, each checkbox changes the
forms recordsource to the corresponding query. For example, clicking on
checkbox1 sets me.form.recordsource = "Query1" or clicking on checkbox 3 sets
me.form.recordsource = "Query3".

However, if the user selects more then one checkbox, say checkbox1 &
checkbox4 id like the queries to combine and display the resulting records on
the continous form.

So, with my limited knowledge of MSACCESS, to acheive this, i create & saved
**SIXTEEN (or so)** seperate UNION queries inorder to cover any possible
combination of the four original queries.


"Query1 + Query2"
"Query1 + Query2 + Query3"
"Query1 + Query2 + Query3 + Query4"
"Query1 + Query3 + Query4"
"Query1 + Query4"
"Query2 + Query3 + Query4"
"Query2 + Query3"
"Query1 + Query4" ..etc


....then in the Checkboxs ONCLICK event, i called a VBA procedure which i
wrote (a massive collection of IF Statements ) that verifies which checkboxs
are selected and THEN changes the forms recordsource to the appropriate query.



QUESTION:

i was hoping there was an easy way in VBA, rather then having to create &
save every possible combination of the four originaly queries, to combine
qeuries or add recordsets. Perhaps something like, me.form.recordsource =
"query1" + "query2" or maybe something like me.form.recordsource =
"query1" UNION "query4" or perhaps me.form.recordset (??) =
theRecordSetReturnFromQuery1 + TheRecordSetFromQuery3...

Id like to get around saving the original select statements in a NEW query
everytime i want to "UNION" them with each other... having to save them
everytime makes for a long list of queries in the database window when they
are pratically made up of identical SQL code.


err, im i making any sense?
id like to be brighter.
WebDude
(jeff)

 

[Marshall Barton]

I created four simple select queries, Query1, Query2, Query3, Query4 - each
query uses the same table but retrieves a unique set of records. Then i
created a continuous form, and slapped FOUR Checkboxs, box1, box2, box3, box4
onto the forms header. And on their onclick event, each checkbox changes the
forms recordsource to the corresponding query. For example, clicking on
checkbox1 sets me.form.recordsource = "Query1" or clicking on checkbox 3 sets
me.form.recordsource = "Query3".

However, if the user selects more then one checkbox, say checkbox1 &
checkbox4 id like the queries to combine and display the resulting records on
the continous form.

So, with my limited knowledge of MSACCESS, to acheive this, i create & saved
**SIXTEEN (or so)** seperate UNION queries inorder to cover any possible
combination of the four original queries.


"Query1 + Query2"
"Query1 + Query2 + Query3"
"Query1 + Query2 + Query3 + Query4"
"Query1 + Query3 + Query4"

...then in the Checkboxs ONCLICK event, i called a VBA procedure which i
wrote (a massive collection of IF Statements ) that verifies which checkboxs
are selected and THEN changes the forms recordsource to the appropriate query.

QUESTION:

i was hoping there was an easy way in VBA, rather then having to create &
save every possible combination of the four originaly queries, to combine
qeuries or add recordsets. Perhaps something like, me.form.recordsource =
"query1" + "query2" or maybe something like me.form.recordsource =
"query1" UNION "query4" or perhaps me.form.recordset (??) =
theRecordSetReturnFromQuery1 + TheRecordSetFromQuery3...

Id like to get around saving the original select statements in a NEW query
everytime i want to "UNION" them with each other... having to save them
everytime makes for a long list of queries in the database window when they
are pratically made up of identical SQL code.

Maybe you're just using a private shorthand, but
me.form.recordsource = "query1" + "query2"
would be an illegal form ot the record source property.

The way to do this kind of thing is to use code like:

Dim strSQL As String

If Me.chkbox1 = True Then
strSQL = strSQL & "UNION SELECT * FROM query1"
End If
If Me.chkbox2 = True Then
strSQL = strSQL & "UNION SELECT * FROM query2"
End If
. . .
Me.RecordSource = Mid(strSQL, 7)

The key requirement with this is that there must be the same
number of fields of the same type in each Select statement's
field list.

 

[Guest]

Hi

Maybe you're just using a private shorthand, but
me.form.recordsource = "query1" + "query2"
would be an illegal form ot the record source property.

Using design view, i created four queries , and saved them as;

"Assigned"
"Not Assigned"
"Retired"
"DoNotPlace"

... that is how they are actually displayed in the DataBase Window. But id
like the user to be able to pick any combination of those original four
queries and have the continous form display the resulting records. So i
figured all the possible combinations the user could select from the original
four queries and created the following queries actually named as follows;

"Assigned + NotAssigned + Retired + DoNotPlace"
"Assigned + NotAssigned + Retired"
"Assigned + NotAssigned"
"NotAssigned + Retired + DoNotPlace"
"NotAssigned + DoNotPlace"
"Assigned + Retired"
(..and so forth and so on)

....those are the actual names which are displayed under "Queries" in the
DataBase Window in MS Access 2000. On my continous form, i have four
checkboxes, each one corresponding to the original four queries. The
checkboxs names are "CheckBoxAssigned" and "CheckBoxNotAssigned" and
"ChecBoxRetired" and "CheckBoxDoNotPlace". SO, when one of those four
checkboxes are clicked, a small vba procedure looks to see which checkboxes
are checked and then figures out which of the 16 queries (listed above) to
load as the forms record source.

in otherwords, there is no SQL in my Visual Basic coding. There are only
references to query names. like,

me.form.recordsource = "Assigned"
or
me.form.recordsource = "Assigned + NotAssigned"
or
me.form.recordsource = "Assigned + Retired"
...etc for all the 16 combinations of the original four queries.


but i didnt like the idea of actually saving all the 16 combinations of the
four original queries. so i was looking for some way to combine the original
four saved queries using some sort of shortcut in vba - and not writing out
the same select statements over and over again.

The way to do this kind of thing is to use code like:

Dim strSQL As String

If Me.chkbox1 = True Then
strSQL = strSQL & "UNION SELECT * FROM query1"
End If
If Me.chkbox2 = True Then
strSQL = strSQL & "UNION SELECT * FROM query2"
End If

now, when you say "query2", do you mean that i can actually refer to the
queries i saved in the database window...


If Me.chkbox1 = True Then
strSQL = strSQL & "UNION SELECT * FROM Assigned"
End If


....OR... do you mean i have to write the actual SELECT statements, say for
the query i named "Assigned" for example, it would be instead as follows......


If Me.chkbox1 = True Then
strSQL = strSQL & "
SELECT DISTINCT Requests.EndDate, Volunteers.pkVolunteerID,
Volunteers.LastName, Volunteers.FirstName, Volunteers.Telephone1,
Volunteers.Telephone2, Volunteers.Telephone3, Volunteers.[E-Mail],
Volunteers.Birthdate
FROM Volunteers INNER JOIN Requests ON Volunteers.pkVolunteerID =
Requests.fkVolunteerID
WHERE (((Requests.EndDate) Is Null));

End If


:-(

. . .
Me.RecordSource = Mid(strSQL, 7)

I looked up the MID() function in the MSACCESS Online help.

MID(text,start_num,num_chars)

....is 7 your start_num? If so, then it will return the string less its
first 7 characters?
and thats how you eliminate the inevitable "UNION" at the start of the
strSQL.. but why 7 and not 5? "UNION" is five characters long..?


Cheers,
Jeff

 

[Marshall Barton]

In my suggested UNION query, the query1, query2, etc should
be replace by the names of your original four queries. You
could write out each query's entire SQL but there no
significant benefit to doing that.

OTOH, if the Joins(?) are amenable, it might be
significantly better/faster to get rid of all those queries
and use VBA to construct the required Where clause that
combines the conditions specified in the four check boxes
into a single Select query without the Unions.

Your question about mid is pretty close. The reason for the
7 as the start character is because 5 would include the 5th
character (the last N in UNION) and a 6 would still pick up
the space, which is OK, but why not skip it too.
--
Marsh
MVP [MS Access]

Maybe you're just using a private shorthand, but
me.form.recordsource = "query1" + "query2"
would be an illegal form ot the record source property.

Using design view, i created four queries , and saved them as;

"Assigned"
"Not Assigned"
"Retired"
"DoNotPlace"

.. that is how they are actually displayed in the DataBase Window. But id
like the user to be able to pick any combination of those original four
queries and have the continous form display the resulting records. So i
figured all the possible combinations the user could select from the original
four queries and created the following queries actually named as follows;

"Assigned + NotAssigned + Retired + DoNotPlace"
"Assigned + NotAssigned + Retired"
"Assigned + NotAssigned"
"NotAssigned + Retired + DoNotPlace"
"NotAssigned + DoNotPlace"
"Assigned + Retired"
(..and so forth and so on)

...those are the actual names which are displayed under "Queries" in the
DataBase Window in MS Access 2000. On my continous form, i have four
checkboxes, each one corresponding to the original four queries. The
checkboxs names are "CheckBoxAssigned" and "CheckBoxNotAssigned" and
"ChecBoxRetired" and "CheckBoxDoNotPlace". SO, when one of those four
checkboxes are clicked, a small vba procedure looks to see which checkboxes
are checked and then figures out which of the 16 queries (listed above) to
load as the forms record source.

in otherwords, there is no SQL in my Visual Basic coding. There are only
references to query names. like,

me.form.recordsource = "Assigned"
or
me.form.recordsource = "Assigned + NotAssigned"
or
me.form.recordsource = "Assigned + Retired"
..etc for all the 16 combinations of the original four queries.


but i didnt like the idea of actually saving all the 16 combinations of the
four original queries. so i was looking for some way to combine the original
four saved queries using some sort of shortcut in vba - and not writing out
the same select statements over and over again.

The way to do this kind of thing is to use code like:

Dim strSQL As String

If Me.chkbox1 = True Then
strSQL = strSQL & "UNION SELECT * FROM query1"
End If
If Me.chkbox2 = True Then
strSQL = strSQL & "UNION SELECT * FROM query2"
End If

now, when you say "query2", do you mean that i can actually refer to the
queries i saved in the database window...


If Me.chkbox1 = True Then
strSQL = strSQL & "UNION SELECT * FROM Assigned"
End If


...OR... do you mean i have to write the actual SELECT statements, say for
the query i named "Assigned" for example, it would be instead as follows......


If Me.chkbox1 = True Then
strSQL = strSQL & "
SELECT DISTINCT Requests.EndDate, Volunteers.pkVolunteerID,
Volunteers.LastName, Volunteers.FirstName, Volunteers.Telephone1,
Volunteers.Telephone2, Volunteers.Telephone3, Volunteers.[E-Mail],
Volunteers.Birthdate
FROM Volunteers INNER JOIN Requests ON Volunteers.pkVolunteerID =
Requests.fkVolunteerID
WHERE (((Requests.EndDate) Is Null));

End If

. . .
Me.RecordSource = Mid(strSQL, 7)

I looked up the MID() function in the MSACCESS Online help.

MID(text,start_num,num_chars)

...is 7 your start_num? If so, then it will return the string less its
first 7 characters?
and thats how you eliminate the inevitable "UNION" at the start of the
strSQL.. but why 7 and not 5? "UNION" is five characters long..?

 

[Guest]

Marshall!

In my suggested UNION query, the query1, query2, etc should
be replace by the names of your original four queries.

LOL!! Brilliant!

iT worked perfectly,
just as you described it!

ive streamlined my code,
got rid of redundancies,
added new features to my forms..
And all thanks to you.
im gonna add you to my Xmas list

Jeff

ps: and thanks for replying to my reply

 

[Marshall Barton]

LOL!! Brilliant!

iT worked perfectly,
just as you described it!

ive streamlined my code,
got rid of redundancies,
added new features to my forms..
And all thanks to you.
im gonna add you to my Xmas list

Jeff

ps: and thanks for replying to my reply

Hey, that's great! Glad to play a part of you making some
serious progress on your project.

Gee, Christmas is still a long way off, I'll have to think
about what I want to put on my new Santa's list ;-) Right
now I'm leaning towards a new 4GH quad processor, dual
monitor computer, but I'll let you know for sure later ;-)

 

[Guest]

I'm leaning towards a new 4GH quad processor, dual
monitor computer.....

:-O

Im gonna have to combine my magical powers of giving
with my neighbor the toothfairy and her friend the easter bunny
to pull this one off.

Or somehow figure out a way to dramatacially increase sales
from my lemonade stand.

:-|