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!

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"));

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?

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 !

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

Me.[frmConsole subform].Form.Filter = strWhere
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!!

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!

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?

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.

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!

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.

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.

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

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....

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

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!!

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.

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...

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.

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

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 :)

I wanted to post it exactly as it came out of VBA in case there was too many spaces or something like that - I know it looked horrible, sorry.

I'm geussing the error is the INNER JOIN for tblPartnerset.PartnersetID? It looks out of place. The 4108 without quotes is ok though right? So I need to fix that INNER JOIN...

I think I need to study queries after this... :D

No your joins look to be fine!

Look at the select part! There is an empty line there... with only a comma, you have a comma to many just before the FROM .

On thing about spaces, you can never have to many... mostly the problem with spaces is that you have to few.

OK, update - after ironing out a few small issues, everything is working great! I'm really happy with the solution we worked out - it is very simple and clean and runs really fast, considering I have 300,000+ records.

The one final thing I need to do is to link my two macros - the first being the VBA code that edits the query criteria and the second being the Access-macro (i.e. built using the macro editor on the main screen). This is the macro that exports the edited query as XLS - I found it much easier to do it this way than to code it. I just need to know how to call an "Access macro" from a vba procedure.

If you help me with this one I promise to leave you alone :D

EDIT: docmd.runmacro Worked well. Thanks for all of your help on this, I learned a lot!

The one final thing I need to do is to link my two macros - the first being the VBA code that edits the query criteria and the second being the Access-macro


:mad: :eek:

Nooooooooooooooooooooooooooooooooooooooooooooooooo ooooo!

Dont use MACRO's !!!!

Nooooooooooooooooooooooooooooooooooooooooooooooooo ooooo!

Macro's are worse than bad... why not simply code it ? Docmd.transferspreadsheet!
Much easier much more flexible and all your code (re-write the query + export) is in one place therefor much easier and to maintain.

OK, OK , OK. Is it just then docmd.transferspreadsheet ("querynamehere")?

Can I still get the 'save to' browse box to come up?

lookup the transferspreadsheet command in the help, it is pretty obvious...

A save to box is a little more complex (unfortunatly).
Samples can be found:
http://www.access-programmers.co.uk/forums/showthread.php?t=153350&highlight=Browse
More samples:
http://www.access-programmers.co.uk/forums/search.php?searchid=2355600

Hrmm, the solution you suggested is a bit more complex but I like the neatness and flxibility. The save as dialogue box wasn't so hard. It has all come together really nicely now, thanks!