Using combobox values in query

[zfind]

Hi guys,

At the moment I've got a form with a subform. On the form are 5 comboboxes that filter the subform. I want to be able to export the filtered recordset to Excel and through my reading it seems that a query is the best way to do it. I'm having problems getting the variables from the combos into the query though. I tried to do it simply but after a lot of frustration I made a new query and just put in an example filter set to see where to insert the variables to get the output I need. Here's what the Query came out as with my imaginary filter set:

SELECT tblPartnersets.[Partner name], tblRecords.WeekID, tblCountries.Region, tblCountries.Country, tblChannels.Channel
FROM tblWeeks INNER JOIN ((tblCountries INNER JOIN (tblChannels INNER JOIN tblPartnersets ON tblChannels.ChannelID = tblPartnersets.ChannelID) ON tblCountries.CountryID = tblPartnersets.CountryID) INNER JOIN tblRecords ON tblPartnersets.PartnersetID = tblRecords.PartnersetID) ON tblWeeks.WeekID = tblRecords.WeekID
WHERE (((tblPartnersets.[Partner name])="PartnerABC") AND ((tblRecords.WeekID)=28) AND ((tblCountries.Region)="RegionABC") AND ((tblCountries.Country)="CountryABC") AND ((tblChannels.Channel)="ChannelABC"));

It looks very complex for what I want to do, but running that query gives the exact results I want in the exported excel. I just need the filter values to be dynamic based on:

vRegion = cboRegion.value
vWeek = cboWeek.value
vPartner = cboPartner.value
vCountry = cboCountry.value
vChannel = cboChannel.value

I don't know if the above is correct being that I've got forms and subforms going on.

Can anyone edit the above to show me how to get the results I need?

Thanks!

 

[namliam]

Do you allways want/need all 5 values to be entered? Or can an export need to happen on only 1 or even no fields??

This is your SQL in a more readable fashion:

SELECT tblPartnersets.[Partner name]
,      tblRecords.WeekID
,      tblCountries.Region
,      tblCountries.Country
,      tblChannels.Channel
FROM         tblWeeks 
INNER JOIN ((tblCountries 
INNER JOIN  (tblChannels 
INNER JOIN   tblPartnersets ON tblChannels.ChannelID = tblPartnersets.ChannelID) 
                            ON tblCountries.CountryID = tblPartnersets.CountryID) 
INNER JOIN   tblRecords     ON tblPartnersets.PartnersetID = tblRecords.PartnersetID) 
                            ON tblWeeks.WeekID = tblRecords.WeekID
WHERE (((tblPartnersets.[Partner name])="PartnerABC") 
  AND ((tblRecords.WeekID)=28) 
  AND ((tblCountries.Region)="RegionABC") 
  AND ((tblCountries.Country)="CountryABC") 
  AND ((tblChannels.Channel)="ChannelABC"));

 

[zfind]

The export could happen if any combination of the comboboxes are selected, so it could range from 0 to 5 fields.

I'll plug that code in and test it out.

EDIT: How can I use the above format in VBA?

 

[namliam]

Well all I did with above query is reformat it so it is a bit more readable... Understanding what one is doing is very important....

To make your solution in VBA:

Dim mySQL as string
mySQL = ""
mySQL = mySQL & " Select "
mySQL = mySQL & " fields "
mySQL = mySQL & " From "
mySQL = mySQL & " ...ETC... "
mySQL = mySQL & " Where 1=1 AND " ' Dummy Where to make sure we always have a where.

If not Isnull(Thisfield) then
' for a text:
mySQL = mySQL & "yourTableField = """ & ThisField & """ AND "
' for a date:
mySQL = mySQL & "yourTableField = #" & ThisField & "# AND "
' for a number:
mySQL = mySQL & "yourTableField = " & ThisField & " AND "
endif

' Remove the last And
mySQL = Left(mySQL, len(mySQL) -4)
' now stick mySQL into your QueryDef 
Currentdb.querydefs("SomeQuery").sql = mySQL
' Now export it
Docmd.transfer-bla bla bla

Good luck !

 

[zfind]

Wow, a lot of that is foreign to me but I will give it a try!

Another small problem I've found is with my "Reset filter" button. It's meant to first turn off the filter and then clear the comboboxes, ready for a new filter set. It works great, except when I try to enter new filter choices after doing a reset. I go to select a new Region, for example and I get "You can't assign a value to this field". To me it seems that the filter has something to do with it, but I am not sure.

Here's the Reset Filter code:

    Me.[frmConsole subform].Form.FilterOn = False
    cboRegion.Value = Null
    cboPartner.Value = Null
    cboWeek.Value = Null
    cboChannel.Value = Null
    cboCountry.Value = Null

When I get the error, debug takes me to the FilterSubform sub:

Private Sub FilterSubForm()
'''Apply filter based on cbo selections
Dim strWhere As String
    If blnRegion Then
    strWhere = "[Region]='" & Me.cboRegion & "' AND "
    End If
    
    If blnPartner Then
    strWhere = strWhere & "[Partner name]='" & Me.cboPartner & "' AND "
    End If
    
    If blnWeek Then
    strWhere = strWhere & "[WeekID]=" & Me.cboWeek & " AND "
    End If
    
    If blnChannel Then
    strWhere = strWhere & "[Channel]='" & Me.cboChannel & "' AND "
    End If
    
    If blnCountry Then
    strWhere = strWhere & "[Country]='" & Me.cboCountry & "'"
    End If
    
    If Right(strWhere, 5) = " AND " Then
        strWhere = Left(strWhere, Len(strWhere) - 5)
    End If
    
    [B]Me.[frmConsole subform].Form.Filter = strWhere[/B]
    Me.[frmConsole subform].Form.FilterOn = True
    
End Sub

With the bold line being the error, hence my thoughts about the filter issue.

Hope I'm not asking too much, thanks for your helps so far!!

 

[namliam]

With the bold line being the error, hence my thoughts about the filter issue.

Indent your code!

    If blnRegion Then
        strWhere = "[Region]=""" & Me.cboRegion & """ AND "
    End If

You should be able to assign that Filter
If you add "Debug.print strWhere" in front of the Filter line... does that show what you expect?

Also you shouldnt need to deactivate the filter to apply a new one...

Note: You cannot export a filtered subform!

 

[zfind]

I went through and indented it all - much nicer!

Debug.print didn't do anything, where should I see the output printed?

Is it correct to set the cboXXX.value to Null to make it show all records again or should it be ""

AAANNND do I need to add those extra quotes to all of the filter if blocks or just region?

 

[namliam]

Debug.print didn't do anything, where should I see the output printed?

In the immediate window, hit CTRL+G to open it or find it in the view menu.

Is it correct to set the cboXXX.value to Null

Yes

AAANNND do I need to add those extra quotes to all of the filter if blocks or just region?

You can use "" or ' it works the same, not something you need to change. Simply my prevered way of working.

 

[zfind]

The filter strings look great, all correct - everything is good until I hit resetfilter:

"Runtime error 3075
Syntax error: missing operator in query expression: '[Region]=" AND [Partner name]=" AND [WeekID]= AND [Channel]=" AND [Country]="".

(that string part is copied letter for letter. I think the " is actually 2 x ' )

The immediate window had:

[Region]='' AND [Partner name]='' AND [WeekID]= AND [Channel]='' AND [Country]=''

WeekID has no quotes because it is a four digit number and it's the only way it would work.

This is quite difficult!

 

[zfind]

Indent your code!

    If blnRegion Then
        strWhere = "[Region]=""" & Me.cboRegion & """ AND "
    End If

You should be able to assign that Filter
If you add "Debug.print strWhere" in front of the Filter line... does that show what you expect?

Also you shouldnt need to deactivate the filter to apply a new one...

Note: You cannot export a filtered subform!

Yep, I've foudn that! I'm trying to use a query that will take the cboBox values and use them as criteria - it's kind of working but only if every cboBox is filled.

 

[zfind]

OK, looks like those boolean flags were causing a problem. As soon as the cboBoxes were changed for the first time, they were switched to true, and the contents of that box was included in the filter, even if empty or the value was left over. Switching them to false in the resetfilter sub seems to have worked. Still testing though....

Now I just need to fix that query.

 

[namliam]

lol, the bln flags was going to be my next suggestion.

 

[zfind]

OK working great now.

So, with this query, I've got this in the criteria for the week field

[Forms]![frmConsole]![cboWeek]

This is repeated for the four other query fields and their respective cboBoxes.

So when the query is run by pressing the "export" button on the form, it should look to the combobox, take the value and use that as the criteria. The problem is that it only works if all five comboboxes have something valid chosen. So if I wanted to export all records irregardless of region or partner, but in one week, it doesn't work.

Any ideas?

edit: Maybe it would work if I could get some sort of "all" value into the combobox instead of just blank....

 

[namliam]

No... Just do it the same way you do your filter.

Just take your filter and stick that into the where clause for your query, presto done!
I allready told you how to do that too... See post #4

 

[zfind]

Hrmm, I'm having trouble getting this to work, mainly in the first part where the query statement is being built. I don't know how to format the variables that I am putting in, like field names and cboBoxes, so that it filters the query properly.

Would you mind filling out the example in post #4 with some data so I could perhaps build from that? I know I am close but I just cant make it work!!

 

[namliam]

To make your solution in VBA:

Dim mySQL as string
mySQL = ""
' Just insert  your SQL statement like I formated it for you in my post #2
mySQL = mySQL & " Select "
mySQL = mySQL & " fields "
mySQL = mySQL & " From "
mySQL = mySQL & " ...ETC... "
mySQL = mySQL & " Where 1=1 AND " ' Dummy Where to make sure we always have a where.

If not Isnull(Thisfield) then
'For yourTableField put in the tableField you are filtering i.e. tblCountries.Region
' and for ThisField replace the field on the form i.e. cboRegion.value

' for a text:
mySQL = mySQL & "yourTableField = """ & ThisField & """ AND "
' for a date:
mySQL = mySQL & "yourTableField = #" & ThisField & "# AND "
' for a number:
mySQL = mySQL & "yourTableField = " & ThisField & " AND "
endif

' Remove the last And
mySQL = Left(mySQL, len(mySQL) -4)
' now stick mySQL into your QueryDef 
Currentdb.querydefs("SomeQuery").sql = mySQL
' Now export it
Docmd.transfer-bla bla bla

See how far you can get and post your code you have... Then I can maybe help you along more, but I dont know what more to do but supply above pseudo code.

 

[zfind]

Dim mySQL as string
mySQL = ""
' Just insert  your SQL statement like I formated it for you in my post #2
mySQL = mySQL & " Select "
mySQL = mySQL & " tblPartnersets.[Partner name],"
mySQL = mySQL & " tblRecords.WeekID,"
mySQL = mySQL & " tblCountries.Region"
mySQL = mySQL & " tblCountries.Country"
mySQL = mySQL & " tblChannels.Channel"
mySQL = mySQL & " From "
mySQL = mySQL & " ...ETC... "
mySQL = mySQL & " Where 1=1 AND " ' Dummy Where to make sure we always have a where.
 
If not Isnull(Thisfield) then
'For yourTableField put in the tableField you are filtering i.e. tblCountries.Region
' and for ThisField replace the field on the form i.e. cboRegion.value
 
' for a text:
mySQL = mySQL & "tblCountries.Region= """ & cboRegion.value & """ AND "
mySQL = mySQL & "tblCountries.Country= """ & cboCountry.value & """ AND "
mySQL = mySQL & "tblChannel.Channel= """ & cboChannel.value & """ AND "
mySQL = mySQL & "tblPartnersets.[Partner name]= """ & cboPartner.value & """ AND "
mySQL = mySQL & "tblRecords.WeekID= " & cboWeek.value & " AND "
 
' Remove the last And
mySQL = Left(mySQL, len(mySQL) -4)
' now stick mySQL into your QueryDef 
Currentdb.querydefs("qryTest").sql = mySQL
' Now export it
Docmd.transfer-bla bla bla

As you can see I left the FROM part as it was, I don't know how to do that part, with or without brackets etc...

 

[namliam]

The from should be as Access tells you it should be, that is the safest way to do it...

Also I notice you are missing a few comma's in the select part.

Plus you will need to do the IF for each field:

If not Isnull(cboRegion.value ) then
    mySQL = mySQL & "tblCountries.Region= """ & cboRegion.value & """ AND "
end if

Otherwize the where will be added always, instead of only when there is data.

 

[zfind]

Ok, I've got this now:

Private Sub btnExport_Click()
Dim mySQL As String
mySQL = ""
' Just insert your SQL statement like I formated it for you in my post #2
mySQL = mySQL & " Select "
mySQL = mySQL & " tblPartnersets.[Partner name],"
mySQL = mySQL & " tblRecords.WeekID,"
mySQL = mySQL & " tblCountries.Region,"
mySQL = mySQL & " tblCountries.Country,"
mySQL = mySQL & " tblChannels.Channel,"
mySQL = mySQL & " From "
mySQL = mySQL & " tblWeeks INNER JOIN ((tblCountries INNER JOIN (tblChannels INNER JOIN tblPartnersets ON tblChannels.ChannelID = tblPartnersets.ChannelID) ON tblCountries.CountryID = tblPartnersets.CountryID) INNER JOIN tblRecords ON tblPartnersets.PartnersetID = tblRecords.PartnersetID) ON tblWeeks.WeekID = tblRecords.WeekID"
mySQL = mySQL & " Where 1=1 AND " ' Dummy Where to make sure we always have a where.
 
If Not IsNull(cboRegion.Value) Then
mySQL = mySQL & "tblCountries.Region= """ & cboRegion.Value & """ AND "
End If
If Not IsNull(cboCountry.Value) Then
mySQL = mySQL & "tblCountries.Country= """ & cboCountry.Value & """ AND "
End If
If Not IsNull(cboChannel.Value) Then
mySQL = mySQL & "tblChannel.Channel= """ & cboChannel.Value & """ AND "
End If
If Not IsNull(cboPartner.Value) Then
mySQL = mySQL & "tblPartnersets.[Partner name]= """ & cboPartner.Value & """ AND "
End If
If Not IsNull(cboWeek.Value) Then
mySQL = mySQL & "tblRecords.WeekID= " & cboWeek.Value & " AND "
End If
 
' Remove the last And
mySQL = Left(mySQL, Len(mySQL) - 4)
' now stick mySQL into your QueryDef
CurrentDb.QueryDefs("qryTest").SQL = mySQL
' Now export it
End Sub

When it gets to the end I get "Runtime error 3625 Item not found in this collection" and it points to the last line of code... Seems to be getting closer! Immediate has:

[Region]='America' AND [Partner name]='PartnerABC' AND [WeekID]=2708 AND [Channel]='ChannelABC' AND [Country]='CountryABC'

That looks good to me...?

EDIT: OK, changed the name from 'qryTest' to the actual name of the query that already exists, 'qryExportFiltered'. Then iäm getting a SELECT syntax error about containing a reserved word, spelling or punctuation.

2nd EDIT: Here's the actual query, sorry the one above isn't fully it obviously:

Select tblPartnersets.[Partner name], tblRecords.WeekID, tblCountries.Region, tblCountries.Country, tblChannels.Channel, From tblWeeks INNER JOIN ((tblCountries INNER JOIN (tblChannels INNER JOIN tblPartnersets ON tblChannels.ChannelID = tblPartnersets.ChannelID) ON tblCountries.CountryID = tblPartnersets.CountryID) INNER JOIN tblRecords ON tblPartnersets.PartnersetID = tblRecords.PartnersetID) ON tblWeeks.WeekID = tblRecords.WeekID Where 1=1 AND tblCountries.Region= "Europe" AND tblCountries.Country= "CountryABC" AND tblChannel.Channel= "ChannelABC" AND tblPartnersets.[Partner name]= "PartnerABC" AND tblRecords.WeekID= 4108

 

[namliam]

mySQL = mySQL & " tblWeeks INNER JOIN ((tblCountries INNER JOIN (tblChannels INNER JOIN tblPartnersets ON tblChannels.ChannelID = tblPartnersets.ChannelID) ON tblCountries.CountryID = tblPartnersets.CountryID) INNER JOIN tblRecords ON tblPartnersets.PartnersetID = tblRecords.PartnersetID) ON tblWeeks.WeekID = tblRecords.WeekID"

Select tblPartnersets.[Partner name], tblRecords.WeekID, tblCountries.Region, tblCountries.Country, tblChannels.Channel, From tblWeeks INNER JOIN ((tblCountries INNER JOIN (tblChannels INNER JOIN tblPartnersets ON tblChannels.ChannelID = tblPartnersets.ChannelID) ON tblCountries.CountryID = tblPartnersets.CountryID) INNER JOIN tblRecords ON tblPartnersets.PartnersetID = tblRecords.PartnersetID) ON tblWeeks.WeekID = tblRecords.WeekID Where 1=1 AND tblCountries.Region= "Europe" AND tblCountries.Country= "CountryABC" AND tblChannel.Channel= "ChannelABC" AND tblPartnersets.[Partner name]= "PartnerABC" AND tblRecords.WeekID= 4108

Why do you keep incisting on splurging your sql on here as well as in your code. Come on, no on can read stuff like that... let alone maintain stuff like that...

Select tblPartnersets.[Partner name]
,      tblRecords.WeekID
,      tblCountries.Region
,      tblCountries.Country
,      tblChannels.Channel
, 
From tblWeeks 
INNER JOIN ((tblCountries 
INNER JOIN (tblChannels 
INNER JOIN tblPartnersets ON tblChannels.ChannelID = tblPartnersets.ChannelID) 
                          ON tblCountries.CountryID = tblPartnersets.CountryID) 
INNER JOIN tblRecords     ON tblPartnersets.PartnersetID = tblRecords.PartnersetID) 
                          ON tblWeeks.WeekID = tblRecords.WeekID 
Where 1=1 
  AND tblCountries.Region = "Europe" 
  AND tblCountries.Country = "CountryABC" 
  AND tblChannel.Channel = "ChannelABC" 
  AND tblPartnersets.[Partner name] = "PartnerABC" 
  AND tblRecords.WeekID = 4108

Now I think in above CLEANED UP sql I bet you can find the error too