Filter Query

[Guirg]

Hi All,

What i have is a datasheet sitting on a form1 and button1 connecting form1 to form2... On form2 i have radio buttons allowing the user to hide and show columns... the user then hits button2 which runs

    DoCmd.OutputTo acOutputForm, "frmsummary", acFormatXLS, "TblExport.xls", True

I have filter options on form1 that filters "frmsummary", what im unsure of is how to carry the filter across to the export so that the user only ends up with the columns that hes interested in and the specific range, ive tried the net and the "access bible" and havent shown up with anything.

Cheers

Guirg

 

[DCrake]

When your sub form first opens it has all columns in view, your button that hides the desired column does what it is supposed to do but this is dynamic and for visual purposes only. The form itself is not saved in a state where the columns are hidden. This is why when exporting the form all the columns are visible.

What you really need to do is to have a query that matches your datasheet view on the sub form and export that. You should not be exporting forms anyway.

David

 

[Guirg]

The task i was given was to have a way to export data into a pdf or excel file with only certain desired columns and rows chosen by the user. I have afilter on the intial form1 that filters the rows and from playing around and net hunting i found the hiding column function which when i used the output to function it only copied the visible columns which is perfect... What happening is i cant get the row filter to carry across so i only export that data...

If theres an easier way to do this please please enlighten me... heres the basic code im using so far

DoCmd.OpenForm "frmsummary1", acFormDS, , , , acHidden
    If opt1 <> True Then
            Forms!frmsummary1!humidity.ColumnHidden = -1
        ElseIf opt1 = True Then
            Forms!frmsummary1!humidity.ColumnHidden = 0
Forms!frmsummary1.Form.RecordSource = "SELECT * FROM qrySummary " & Buildfilter
Forms!frmsummary1.Requery

 

[DCrake]

Another approach would be to do what you are doing now with the hidden columns but build up a new SQL based on the the non hidden columns:-

Loop thought the columns and if the column is not hidden then add the field to a list with comma delimeters, then parse the sql together to get a Select statement

SQL = "Select " & UnhiddenFieldsList & " From Table Where Condition"

And use this to export to Excel


EDIT
Just re read your last post again and you said the columns were ok it was the row filter that wan not working. What is being returned by BuildFilter?

David

 

[Guirg]

so something liek this?

 varselect = varselect & "[column1]" & ","

and then at the end strip off the last ","? so it should build varselect up and then put it back into the original form and export from there?

 

[Guirg]

buildfilter was a filter that was built up the same way from various textboxes on form1...what if i created a temporary query from the rowfilter and then column filter it, is that possible?

 

[DCrake]

Which of the following is correct

Right number of columns, right number of rows
Right number of columns, wrong number of rows
Wrong number of columns, right number of rows
Wrong number of columns, wrong number of rows

David

 

[Guirg]

Right number of columns, wrong number of rows

The selected Columns are there
All the rows are there, when i just want some of them

 

[DCrake]

Ok then,

Forms!frmsummary1.Form.RecordSource = "SELECT * FROM qrySummary " & Buildfilter

What is this saying?

Are the correct number of rows correct on the form prior to export

David

 

[Guirg]

Forms!frmsummary1.Form.RecordSource = "SELECT * FROM qrySummary " & Buildfilter
Forms!frmsummary1.Requery
I was using this to requery the form so the changes made to the form during the radio button code is carried over. The builtfilter part was me hoping to carry the filter over

In the subform yes there are, its just the export file that isnt usign the filter

 

[DCrake]

What you could try is to physically change the actual query sql so that the filter is applied

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("qrySummary ")
qryTest.SQL = "SELECT * FROM qrySummary " & Buildfilter

This should then revise the query select statement to include your row filter.

David

 

[Guirg]

Hey David,

Had attack at it on the weekend and it ended up causing some avoc with the union query that i had set up, was thinking is there a entirerow.hidden function in access? If i can connect the buildfilter to a hidden row function i may be able to get it to work... ?

 

[DCrake]

If you can post a copy of your db I will take a look at it.

David

 

[Guirg]

ive tried trimming the db down but its still massive, i have no idea how to cut it back further damn this is so frustrating... is there a way to apply the built up filter in a way that if it failes the filter the row gets hidden?

 

[DCrake]

We previously identified that the number of columns were correct but the number of rows wasn't. Right. So this means that the WHERE part of the sql for the query is wrong.

When you are on your main form and you select a specific parent record does it filter the child records on your sub form as you would expect?

If so, what fields are you using for your Master Child relationship between your main and sub form.

Your SQL for the sub form / query should be reading something like...

Select * From qrySummary Where Parent = Child

The Where Parent = Child syntax is being generated by your BuildFilter variable. What is this actually saying. Is BuildFilter a function that generates the string or is it a string variable that contains the where statement?

David

 

[Guirg]

My main forms was build from a design view the only datasheet i have on it is the one that im trying to export.. A part from that i have a whole bunch of search options with radio buttons and textboxes... The frmSummary was created when i put the data form of qrysummary on there.... and query summary is a union query

Buildfilter is a string variant with the where statement in it

 

[DCrake]

Can you send a couple of screen dumps to give me a visual on it save them as jpeg (alot smaller in size)

 

[Guirg]

i hope this helps.... i think ive gone about this the wrong way, or at least it would have been the right way till my supervisor changed his mind again

 

[DCrake]

So You open up the first form and it contains all the possible columns, then you click on a button and turn on/off the columns to hide/unhide. Then depending on the project you have on the main form the datasheet (Shown as a subform ) is re formatted accordingly.

It's when you click export that the export looses its row filtering. What is happening behind your export button?

From what I can gather it is doing a normal transferspreadsheet action but it is nominating the query qrysummary as its rowsource. Its very difficult to determine the problem without seing the full code. There are several solutions available if only I knew what was happening and what was the desired result.

David

 

[Guirg]

So You open up the first form and it contains all the possible columns, then you click on a button and turn on/off the columns to hide/unhide. Then depending on the project you have on the main form the datasheet (Shown as a subform ) is re formatted accordingly.

It's when you click export that the export looses its row filtering

Exactly... the code behind the export button is

    DoCmd.OutputTo acOutputForm, "frmSummary", acFormatXLS, "TblExport.xls", True

The filter is being applied this way:

    Me.frmsummary.Form.RecordSource = "SELECT * FROM qrySummary " & Buildfilter
    Me.frmsummary.Requery