Union Query if Yex/No

[Ryan]

I have a form, frmParameterForm, that has 10 checkboxes on it, chk1-chk10. I
also have 10 queries, qry1-qry10, that are corespond with the same #
checkbox. I also have a button, btnRunUnionQuery, that I want to use to run
a union query. The union query would be determined buy which checkboxes were
checked. For example, if chk1, chk3 and chk8 were checked, the a query like
this wouild be run.
Select * From qry1
UNION ALL
Select * From qry3
UNION ALL
Select * From qry8

Basicaly, I need the checkbox's that are checked to build a simple union
query using the corresponding numberd query when the button is clicked.
Thank you in advance for any help provided.

 

[Ken Snell \(MVP\)]

Example VBA code to build an SQL string into a strSQL string variable:

Dim strSQL As String
Dim lngLoop As Long
Const strUNIONALL As String = " UNION ALL "
Const strSELECT As String = "SELECT * FROM qry"
strSQL = ""
For lngLoop = 1 To 10
If Me.Controls("chk" & lngLoop).Value = True Then
If Len(strSQL) > 0 Then strSQL = strSQL & strUNIONALL
strSQL = strSQL & strSELECT & lngLoop
End If
Next lngLoop

 

[Ryan]

How do pass the results to a query that I can view on a form?

 

[Ken Snell \(MVP\)]

Assuming that you want to show this on the form that is running the code,
set the form's RecordSource to the SQL statement:

Dim strSQL As String
Dim lngLoop As Long
Const strUNIONALL As String = " UNION ALL "
Const strSELECT As String = "SELECT * FROM qry"
strSQL = ""
For lngLoop = 1 To 10
If Me.Controls("chk" & lngLoop).Value = True Then
If Len(strSQL) > 0 Then strSQL = strSQL & strUNIONALL
strSQL = strSQL & strSELECT & lngLoop
End If
Next lngLoop
Me.RecordSource = strSQL
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ryan]

Awsome, that did the trick. I do however have one more question that may
make this alot more complicated. What if I changed the name of chk1 to
C28Charges and the name of qry1 to C28Charges and wanted to collect the names
of the yes/no boxes to replace the & lngLoop part of If Me.Controls("chk" &
lngLoop).Value = True Then. Is there a way to grab the name of the control
and use it as my query name. It would make the Union Query look like this.

Select * From C28Charges
Union All
Select * From C42Charges

instead of

Select * From chk1
Union All
Select * From chk2

 

[Ken Snell \(MVP\)]

Are the 10 checkboxes the ONLY checkbox controls on the form? If yes:

Dim strSQL As String
Dim ctl As Control
Const strUNIONALL As String = " UNION ALL "
Const strSELECT As String = "SELECT * FROM "
strSQL = ""
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
If Len(strSQL) > 0 Then strSQL = strSQL & strUNIONALL
strSQL = strSQL & strSELECT & ctl.Name
End If
Next ctl
Me.RecordSource = strSQL

 

[Ryan]

Your the man!! The only thing im having problems with is the Me.RecordSource
= strSQL. I want to run your code in the On Click event of the
btnRunUnionQuery on the frmParameterForm that opens another form called
frmResults. If I set the frmParameterForm's record source to =strSQL it will
not open. It says that datasource strSQL Doesnt exist. How do I display my
results??

 

[Ken Snell \(MVP\)]

For frmParameterForm's RecordSource in the form's design view, either leave
it blank or set it to a query that returns no records for the table(s) whose
fields are on the form. Save and close this form.

Then, the code would look something like this:

Dim strSQL As String
Dim ctl As Control
Const strUNIONALL As String = " UNION ALL "
Const strSELECT As String = "SELECT * FROM "
strSQL = ""
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
If Len(strSQL) > 0 Then strSQL = strSQL & strUNIONALL
strSQL = strSQL & strSELECT & ctl.Name
End If
Next ctl
DoCmd.OpenForm "frmParameterForm", acNormal, , , acFormEdit, acHidden
Forms!frmParameterForm.RecordSource = strSQL
Forms!frmParameterForm.Visible = True

 

[Ryan]

Ok, so I got that to work, but here is my problem. I have a crosstab query
that I need to use the results of the query we have been building. Is it
possible to use the solution we have been building to create a temporary
table or query? There will be multiple users in this database which may
create problems too.

 

[Ken Snell \(MVP\)]

Ok, so I got that to work, but here is my problem. I have a crosstab
query
that I need to use the results of the query we have been building. Is it
possible to use the solution we have been building to create a temporary
table or query? There will be multiple users in this database which may
create problems too.

Are you using a split database, where each user has his/her own copy of the
frontend file, and all the frontend files link to one backend file on the
network? If yes, then it's very possible to create/use a temp query.

If all users are sharing the same database file, then it might not be a good
idea to save a temp query -- conflicts might result, though there are some
ways to minimize this.

What is your database setup?

 

[Ryan]

The application is not in production yet. I usually use one front end file,
however, I would be willing to give every user a front end of their own if it
were to help me finish this project. I was wondering about the multi-user
issue of saving a temp query, but now that you mention using multiple front
ends it makes sense to give each user their own copy. Could you please help
with creating the temporary query?

 

[Ken Snell \(MVP\)]

Are you sayng that the crosstab query would need to use the temp query that
would be built by the code that we've been developing? If that is what you
seek, that is going to be a problem, because it's possible that two users
want to use the crosstab query at the same time, so it cannot point to one
temp query for one user when the other user wants to have it point to a
different temp query.

Tell me more about the object that will use the crosstab query. Perhaps we
can build the crosstab query's SQL statement so that it uses the "built"
query from the code, and then use that SQL statement for the object.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ryan]

Thank you for all your help Ken! I have, however, solved my problem. I was
successfull in creating a Dynamic crosstab form. Thanks for sticking with me
on this subject.

 

[Ken Snell \(MVP\)]

Thank you for all your help Ken! I have, however, solved my problem. I
was
successfull in creating a Dynamic crosstab form. Thanks for sticking with
me
on this subject.

Congratulations on your success; you're welcome.