RecordSource

[Guirg]

Heya

Hit a snag, im using a RecordSource to add a variant with a filter to a form from another form the odd thing is it sometimes works and then sometimes decideds not to....

    Me.frmSummary1.Form.RecordSource = "SELECT * FROM qrySummary " & BuildFilter
    Me.frmSummary1.Form.Requery
    DoCmd.OutputTo acOutputForm, "frmSummary1", acFormatXLS, , True
[\Code]
 
Any help would be amazing
Cheers

 

[Galaxiom]

Just to confirm:
frmSummary1 is a subform control on the main form.
Is the focus always on the main form when you run the code?

Otherwise perhaps the problem is with your filter.
Maybe post this part of the code too.

 

[Guirg]

Summary1 is a subform datasheet but theres no mainform really, its just a design view with some unbound textboxes linking everything.. yeah i believe it is as thats where the buildfilter is complied from

 

[marianne]

uhm.....there is no mainform. so how do make your code work from there if you are saying that it is a subform. if you have a subform, therefore, there should also have a mainform. or maybe I am missing something here !

 

[Guirg]

theres a subform but theres no relationships to the mainform.. if that makes more sense

 

[marianne]

ok. but why do you choose to output a FORM instead of a QUERY?

I think when you assign new record source in your subform, it will only be applicable to the object you are giving focus. so the original records in your subform is not change so you cannot expect change of record when you output it. I suggest you use query with your filter criteria to export instead.

 

[Guirg]

is it possible to set focus to the original? because it seems to work once or twice and then it wont and then it will again...

 

[marianne]

you can set your focus to the original if that form is open but I dont know know if that original form will have the same record as being a subform with filter criteria recordsource. you can always experiment there.

 

[Galaxiom]

You are changing the Record Source of the subform control on the main form but your export is the subform object itself with the original RecordSource.

Export the query that you applied to the Record Source of the subform control. Note that any filters won't affect the data export. However it appears you are filtering with a WHERE clause in your query so you should be right.

DoCmd.OutputTo acOutputQuery, "queryname", acFormatXLS, , True

 

[Guirg]

Hmmm how do i apply the where clause filter to the query before i export it, or leave it on the subform? The query the subform comes from is a union query, does that effect anything?

 

[Galaxiom]

Could you extend the static where clause of the query in the subform object to be directly dependent on the variants in your filter rather than having to construct it dynamically?

If the where clause became particularly complex you may be able to simplify by joining a table of parameters that control the outcome of the query. To apply the filter simply recreate that table and requery the form to view the change.

Exporting the query would give the same result.

 

[Galaxiom]

Try creating a QueryDef using your VBA to build the SQL as you have already done.
Instead of applying this SQL dierectly to the Record Source of the form apply the QueryDef.

I expect you would be able to export the QueryDef and get the same results as shown on your form.

http://msdn.microsoft.com/en-us/library/bb177500.aspx