List boxes, functions and query criteria

[Sandy]

I have a form with a subform. The subform has a pivot chart frm as its
source object. On the main form I have a number of controls that allow the
user to specify the criteria for the query of my pivotchart. A command
button initiates a requery of the pivotchart. The controls are two list boxes
(allowing muliselection) and three text boxes. When the text boxes are
updated a VBA sub updates a global variable. When the list boxes are updated
a VBA sub creates a global string is built based on the selection the user
makes or doesn't make. ( I got this bit from Mr Allen Browne on this forum)

To access the global variables in the query I have used simple functions

The txt box stuff works fine but it get complicated with the multiselect
list boxes

Say the function is called SetMyCriteria.

I have set the criteria in the query as

SetMyCriteria() OR Like ( IFF(SetMyCriteria() = "*", "*","")

This gives me the following based on how many if any the user selects
"*" or like "*" which gives all results when the use has
not selected OK
"sarms" 0r Like "" which the user selects only one persons id - ok
"IN ('sarms','abart')" or Like "" which gives me zip instead of all the
records for sarms and abart

I tried
IN (SetMyCriteria()) OR Like ( IFF(SetMyCriteria() = "*", "*","")

and changing SetMyCriteria = "'sarms','abart'" but no luck

I think I have decided this way is impossible but I'm not sure what my other
options are. This chart will be featured in other froms as well so I was
trying to find a tidy non repetive approach to being able to requery it from
different reports and forms. Any Ideas? I am a long time reader of the forum
but a first time poster.

 

[Klatuu]

First, you don't want to use a Global variable. You should avoid using
Global variables. They have some shortcomings that can cause problems.

A better solution for a case like this is to build the SQL of your query by
hand in VBA base on the selections in the List Boxes and any other controls
you may be using for criteria selection. The following code is from one of
my apps where there are six list boxes used to select criteria and the user
may select multiple options from none to all six of the boxes. This code
builds a string of the conditions.

Since you have two list boxes to include, you also have to concatenate the
two condition strings with AND. It would be like:

Dim strSQL As String
Dim strConditionOne As String
Dim strConditionTwo As String
Dim strWhereString As String

strSQL = "SELECT * FROM MyTable"
strConditionOne = BuildWhereCondition("FirstListBox")
strConditionTwo = BuildWhereCondition("SecondListBox")
strWhereString = strConditionOne
If Len(strConditonOne) > 0 And Len(strConditonTwo) > 0 Then
strWhereString = strWhereString & " AND "
End If
strWhereString = strWhereString & strConditionTwo
If Len(strWhereString) > 0 Then
strSQL = strSQL & " WHERE " & strWhereString
End If
strSQL = strSQL & ";"

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

Private Function BuildWhereCondition(strControl As String) As String
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

 

[Sandy]

Thanks for your reply . Ok so I'm heading into uncharted territory with SQL .
I understand the BuildWhereCondition function and most of the code until I
get to the last two lines

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

so I think the StrSQL would look some thing like

Select * From MYTable where ='sarms' And In ('Smith','Brown') ;

What I don't get is how access can tell which fields to apply the where
condition too. Apologies if I am completely off track, I'm not being
intentional stupid - I am trying to do some more reading on SQL but would
appreciate any more help you could give me.

One other thing presuming i can get to a SQL statement in order to change
the recordsource of the pivotchart will this destroy my pivot chart layout? I
have found them to be very touchy beasts!

Thanks again Sandy

 

[John W. Vinson]

Thanks for your reply . Ok so I'm heading into uncharted territory with SQL .
I understand the BuildWhereCondition function and most of the code until I
get to the last two lines

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

so I think the StrSQL would look some thing like

Select * From MYTable where ='sarms' And In ('Smith','Brown') ;

What I don't get is how access can tell which fields to apply the where
condition too. Apologies if I am completely off track, I'm not being
intentional stupid - I am trying to do some more reading on SQL but would
appreciate any more help you could give me.

You're partway there. The SQL string should be more like

Select * From MYTable where [firstfieldname] ='sarms' And [secondfieldname] In
('Smith','Brown') ;

 

[Sandy]

Thanks John. I am now all the way there and my string looks as it should. But
when I set the new recordsource for the Pivotchart form it loses its layout
and appears as a blank Pivotchart. I dont want users to have to reset the
chart everytime they want to change the criteria. When I was using the global
variables and the functions the form was just requeried so it maintained its
layout but this proved limited when I added a listbox to the mix ( as well as
Daves advise to steer clear of globals)

I might be able to set the pivot chart format for a single series graph in
VBA but this pivot chart has more than two series and I haven't been able to
find a way of doing that for a Pivot chart based on a query.

I did have the chart as interactive where the user set up how they wanted it
to look but that proved impossible to print (can't get the export picture
function to work on the software versions my work is using and updating is
out of my control). So I though if i used list boxes ect I could capture the
users intention to recreate a chart for the report.

I feel like I am going around and around Any ideas to put me out of my
misery?

Thanks for all the help so far Sandy

Thanks for your reply . Ok so I'm heading into uncharted territory with SQL .
I understand the BuildWhereCondition function and most of the code until I
get to the last two lines

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

so I think the StrSQL would look some thing like

Select * From MYTable where ='sarms' And In ('Smith','Brown') ;

What I don't get is how access can tell which fields to apply the where
condition too. Apologies if I am completely off track, I'm not being
intentional stupid - I am trying to do some more reading on SQL but would
appreciate any more help you could give me.

You're partway there. The SQL string should be more like

Select * From MYTable where [firstfieldname] ='sarms' And [secondfieldname] In
('Smith','Brown') ;

 

[Klatuu]

Sandy,

John added what I erroneously left out. Sorry about that. You do have to
include the field names. I think the issue is how your pivot chart is
needing the data. It may be that you need to actually create the stored
query for the chart.

If this is the case, please post back and I will dig out the code for
creating or modifying a stored query on the fly.
--
Dave Hargis, Microsoft Access MVP

Thanks John. I am now all the way there and my string looks as it should. But
when I set the new recordsource for the Pivotchart form it loses its layout
and appears as a blank Pivotchart. I dont want users to have to reset the
chart everytime they want to change the criteria. When I was using the global
variables and the functions the form was just requeried so it maintained its
layout but this proved limited when I added a listbox to the mix ( as well as
Daves advise to steer clear of globals)

I might be able to set the pivot chart format for a single series graph in
VBA but this pivot chart has more than two series and I haven't been able to
find a way of doing that for a Pivot chart based on a query.

I did have the chart as interactive where the user set up how they wanted it
to look but that proved impossible to print (can't get the export picture
function to work on the software versions my work is using and updating is
out of my control). So I though if i used list boxes ect I could capture the
users intention to recreate a chart for the report.

I feel like I am going around and around Any ideas to put me out of my
misery?

Thanks for all the help so far Sandy

Thanks for your reply . Ok so I'm heading into uncharted territory with SQL .
I understand the BuildWhereCondition function and most of the code until I
get to the last two lines

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

so I think the StrSQL would look some thing like

Select * From MYTable where ='sarms' And In ('Smith','Brown') ;

What I don't get is how access can tell which fields to apply the where
condition too. Apologies if I am completely off track, I'm not being
intentional stupid - I am trying to do some more reading on SQL but would
appreciate any more help you could give me.

You're partway there. The SQL string should be more like

Select * From MYTable where [firstfieldname] ='sarms' And [secondfieldname] In
('Smith','Brown') ;

 

[Sandy]

Dave,
I would like to give "creating or modifying the stored query on the fly a
go" . I would appreciate you hep with this . Thanks Heaps

Sandy,

John added what I erroneously left out. Sorry about that. You do have to
include the field names. I think the issue is how your pivot chart is
needing the data. It may be that you need to actually create the stored
query for the chart.

If this is the case, please post back and I will dig out the code for
creating or modifying a stored query on the fly.
--
Dave Hargis, Microsoft Access MVP

Thanks John. I am now all the way there and my string looks as it should. But
when I set the new recordsource for the Pivotchart form it loses its layout
and appears as a blank Pivotchart. I dont want users to have to reset the
chart everytime they want to change the criteria. When I was using the global
variables and the functions the form was just requeried so it maintained its
layout but this proved limited when I added a listbox to the mix ( as well as
Daves advise to steer clear of globals)

I might be able to set the pivot chart format for a single series graph in
VBA but this pivot chart has more than two series and I haven't been able to
find a way of doing that for a Pivot chart based on a query.

I did have the chart as interactive where the user set up how they wanted it
to look but that proved impossible to print (can't get the export picture
function to work on the software versions my work is using and updating is
out of my control). So I though if i used list boxes ect I could capture the
users intention to recreate a chart for the report.

I feel like I am going around and around Any ideas to put me out of my
misery?

Thanks for all the help so far Sandy

Thanks for your reply . Ok so I'm heading into uncharted territory with SQL .
I understand the BuildWhereCondition function and most of the code until I
get to the last two lines

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

so I think the StrSQL would look some thing like

Select * From MYTable where ='sarms' And In ('Smith','Brown') ;

What I don't get is how access can tell which fields to apply the where
condition too. Apologies if I am completely off track, I'm not being
intentional stupid - I am trying to do some more reading on SQL but would
appreciate any more help you could give me.

You're partway there. The SQL string should be more like

Select * From MYTable where [firstfieldname] ='sarms' And [secondfieldname] In
('Smith','Brown') ;

 

[Sandy]

Hi Dave

I think I have broken through the haze although somewhat still a little
confused.

I added the code
Dim qdfNew As DAO.QueryDef
With CurrentDb
.QueryDefs.Delete ("qryInteractiveCharts")
Set qdfNew = .CreateQueryDef("qryInteractiveCharts", strSQL)
.Close
End With

and then I tried to requery the subform Pivotchart but nothing happens - the
chart just stays the same
Me.subformInteractive.form.requery

so then I tried
Me.subform.sourceobject = "frmInteractivechart"
This seemed to force the chart to reload I guess and it worked! Is that kind
of weird?

Do I need to check that the query exist before I delete it just to be tidy?
How would I do That?

Thanks for the idea re creating saved query
Sandy

Sandy,

John added what I erroneously left out. Sorry about that. You do have to
include the field names. I think the issue is how your pivot chart is
needing the data. It may be that you need to actually create the stored
query for the chart.

If this is the case, please post back and I will dig out the code for
creating or modifying a stored query on the fly.
--
Dave Hargis, Microsoft Access MVP

Thanks John. I am now all the way there and my string looks as it should. But
when I set the new recordsource for the Pivotchart form it loses its layout
and appears as a blank Pivotchart. I dont want users to have to reset the
chart everytime they want to change the criteria. When I was using the global
variables and the functions the form was just requeried so it maintained its
layout but this proved limited when I added a listbox to the mix ( as well as
Daves advise to steer clear of globals)

I might be able to set the pivot chart format for a single series graph in
VBA but this pivot chart has more than two series and I haven't been able to
find a way of doing that for a Pivot chart based on a query.

I did have the chart as interactive where the user set up how they wanted it
to look but that proved impossible to print (can't get the export picture
function to work on the software versions my work is using and updating is
out of my control). So I though if i used list boxes ect I could capture the
users intention to recreate a chart for the report.

I feel like I am going around and around Any ideas to put me out of my
misery?

Thanks for all the help so far Sandy

Thanks for your reply . Ok so I'm heading into uncharted territory with SQL .
I understand the BuildWhereCondition function and most of the code until I
get to the last two lines

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

so I think the StrSQL would look some thing like

Select * From MYTable where ='sarms' And In ('Smith','Brown') ;

What I don't get is how access can tell which fields to apply the where
condition too. Apologies if I am completely off track, I'm not being
intentional stupid - I am trying to do some more reading on SQL but would
appreciate any more help you could give me.

You're partway there. The SQL string should be more like

Select * From MYTable where [firstfieldname] ='sarms' And [secondfieldname] In
('Smith','Brown') ;

 

[Klatuu]

Here is a function that will return true if the query exists and false if it
does not

Public Function FindQuery(QueryName As String) As Boolean
Dim qdf As QueryDef

For Each qdf In CurrentDb.QueryDefs
If qdf.Name = QueryName Then
FindQuery = True
Exit For
End If
Next qdf
End Function

--
Dave Hargis, Microsoft Access MVP

Hi Dave

I think I have broken through the haze although somewhat still a little
confused.

I added the code
Dim qdfNew As DAO.QueryDef
With CurrentDb
.QueryDefs.Delete ("qryInteractiveCharts")
Set qdfNew = .CreateQueryDef("qryInteractiveCharts", strSQL)
.Close
End With

and then I tried to requery the subform Pivotchart but nothing happens - the
chart just stays the same
Me.subformInteractive.form.requery

so then I tried
Me.subform.sourceobject = "frmInteractivechart"
This seemed to force the chart to reload I guess and it worked! Is that kind
of weird?

Do I need to check that the query exist before I delete it just to be tidy?
How would I do That?

Thanks for the idea re creating saved query
Sandy

Sandy,

John added what I erroneously left out. Sorry about that. You do have to
include the field names. I think the issue is how your pivot chart is
needing the data. It may be that you need to actually create the stored
query for the chart.

If this is the case, please post back and I will dig out the code for
creating or modifying a stored query on the fly.
--
Dave Hargis, Microsoft Access MVP

Thanks John. I am now all the way there and my string looks as it should. But
when I set the new recordsource for the Pivotchart form it loses its layout
and appears as a blank Pivotchart. I dont want users to have to reset the
chart everytime they want to change the criteria. When I was using the global
variables and the functions the form was just requeried so it maintained its
layout but this proved limited when I added a listbox to the mix ( as well as
Daves advise to steer clear of globals)

I might be able to set the pivot chart format for a single series graph in
VBA but this pivot chart has more than two series and I haven't been able to
find a way of doing that for a Pivot chart based on a query.

I did have the chart as interactive where the user set up how they wanted it
to look but that proved impossible to print (can't get the export picture
function to work on the software versions my work is using and updating is
out of my control). So I though if i used list boxes ect I could capture the
users intention to recreate a chart for the report.

I feel like I am going around and around Any ideas to put me out of my
misery?

Thanks for all the help so far Sandy

:

Thanks for your reply . Ok so I'm heading into uncharted territory with SQL .
I understand the BuildWhereCondition function and most of the code until I
get to the last two lines

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

so I think the StrSQL would look some thing like

Select * From MYTable where ='sarms' And In ('Smith','Brown') ;

What I don't get is how access can tell which fields to apply the where
condition too. Apologies if I am completely off track, I'm not being
intentional stupid - I am trying to do some more reading on SQL but would
appreciate any more help you could give me.

You're partway there. The SQL string should be more like

Select * From MYTable where [firstfieldname] ='sarms' And [secondfieldname] In
('Smith','Brown') ;

 

[Sandy]

Sorry have just got back to this. Thanks for the function I am on my way again

Thankyou for all your help it was greatly appreciated
Sandy

Here is a function that will return true if the query exists and false if it
does not

Public Function FindQuery(QueryName As String) As Boolean
Dim qdf As QueryDef

For Each qdf In CurrentDb.QueryDefs
If qdf.Name = QueryName Then
FindQuery = True
Exit For
End If
Next qdf
End Function

--
Dave Hargis, Microsoft Access MVP

Hi Dave

I think I have broken through the haze although somewhat still a little
confused.

I added the code
Dim qdfNew As DAO.QueryDef
With CurrentDb
.QueryDefs.Delete ("qryInteractiveCharts")
Set qdfNew = .CreateQueryDef("qryInteractiveCharts", strSQL)
.Close
End With

and then I tried to requery the subform Pivotchart but nothing happens - the
chart just stays the same
Me.subformInteractive.form.requery

so then I tried
Me.subform.sourceobject = "frmInteractivechart"
This seemed to force the chart to reload I guess and it worked! Is that kind
of weird?

Do I need to check that the query exist before I delete it just to be tidy?
How would I do That?

Thanks for the idea re creating saved query
Sandy

Sandy,

John added what I erroneously left out. Sorry about that. You do have to
include the field names. I think the issue is how your pivot chart is
needing the data. It may be that you need to actually create the stored
query for the chart.

If this is the case, please post back and I will dig out the code for
creating or modifying a stored query on the fly.
--
Dave Hargis, Microsoft Access MVP


:

Thanks John. I am now all the way there and my string looks as it should. But
when I set the new recordsource for the Pivotchart form it loses its layout
and appears as a blank Pivotchart. I dont want users to have to reset the
chart everytime they want to change the criteria. When I was using the global
variables and the functions the form was just requeried so it maintained its
layout but this proved limited when I added a listbox to the mix ( as well as
Daves advise to steer clear of globals)

I might be able to set the pivot chart format for a single series graph in
VBA but this pivot chart has more than two series and I haven't been able to
find a way of doing that for a Pivot chart based on a query.

I did have the chart as interactive where the user set up how they wanted it
to look but that proved impossible to print (can't get the export picture
function to work on the software versions my work is using and updating is
out of my control). So I though if i used list boxes ect I could capture the
users intention to recreate a chart for the report.

I feel like I am going around and around Any ideas to put me out of my
misery?

Thanks for all the help so far Sandy

:

Thanks for your reply . Ok so I'm heading into uncharted territory with SQL .
I understand the BuildWhereCondition function and most of the code until I
get to the last two lines

WHERE " & BuildWhereCondition("FirstListBox")
strSQL = strSQL & " AND "

so I think the StrSQL would look some thing like

Select * From MYTable where ='sarms' And In ('Smith','Brown') ;

What I don't get is how access can tell which fields to apply the where
condition too. Apologies if I am completely off track, I'm not being
intentional stupid - I am trying to do some more reading on SQL but would
appreciate any more help you could give me.

You're partway there. The SQL string should be more like

Select * From MYTable where [firstfieldname] ='sarms' And [secondfieldname] In
('Smith','Brown') ;