Create Reports using a variable, possibly SQL?

[adamrace]

Hi,
I'd like to create a report applying possibly SQL or even a filter? To a set report.
I have a basic design but the thing is i cant just use Access to select which fields i want to include because the fields themselves are always changing. So i need it so that in essence you select from a combo box or type in the name of the field you want to select into the variable FIELDNAME for example, and then using that it would select all of the records in the database which have a "true" in the Column FIELDNAME.

Any help would be massively appreciated.

Thanks

 

[AlexCuse]

What you want to do is build a SQL String. This will basically be a constant, but you will change your WHERE clause based on drop-down. You can then set your reports' RecordSource property programmatically to this SQL String. (You might need to open it in design view). Here is example:

Dim strSQL as String

strSQL = "select some, columns, here from some table where " & _
        USER_SELECTION & " = TRUE"
    
    
    'set warnings off, open in design mode, and assign recordsource
    DoCmd.SetWarnings WarningsOff
    DoCmd.OpenReport "rpt_name", acDesign
    Reports("rpt_applications2_w/seeds").RecordSource = strSQL
    'close design mode, set warnings on
    DoCmd.Close acReport, "rpt_name"
    DoCmd.SetWarnings WarningsOn

Once the recordsource is set, you can do as you please with it.

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[adamrace]

Thanks i think im getting somewhere just one question, what does the

Reports("rpt_applications2_w/seeds").RecordSource = strSQL

line of code mean? Specifically the w/seeds bit, thanks for your help again.

 

[adamrace]

Actually forget that, my problem now is getting the correct data into the fields, any ideas how i can go about this? Thankfully now it is selecting the correct records but it isn't filling in the boxes correctly. As i am being made to use Access to design the reports it is hindering me but if anyone can help it would be greatly appreciated.

 

[adamrace]

It's sorted now, thanks for your help, in future im gonna wait five minutes and have a long hard head scratch before i post haha!

Thanks

 

[AlexCuse]

WHat did you end up using for your solution? YOu should post it, so that if someone else has the same problem they can implement the same thing. I imagine it was something about adding column aliases to your SQL String, so they would be picked up by your report properly?

rpt_applications2 was the report I took the code from I forgot to change name on that line.

Ignorance of certain subjects is a great part of wisdom

 

[adamrace]

I pretty much used your idea except i changed the combo to a text box.

Dim strSQL As String
strSQL = "select * from qryEverything where " & txtBox1 & "_Delivered" & " = 0 AND RepName = Enter_Rep_Name "

ReportCaption = txtBox1
DoCmd.SetWarnings WarningsOff
DoCmd.OpenReport "rptTestVariable", acDesign
Reports("rptTestVariable").RecordSource = strSQL
DoCmd.Close acReport, "rptTestVariable"
DoCmd.SetWarnings WarningsOn
Dim stDocName As String
stDocName = "rptTestVariable"
DoCmd.OpenReport stDocName, acPreview

End Sub

I then just applied the correct records to the fields in the report.

Thanks for all your help

 

[AlexCuse]

Right on. Glad you got it to work.

Ignorance of certain subjects is a great part of wisdom