Generating Reports from a form query

[tmar89]

I'm pretty new to Access and I was asked by my department to write a program to store alot of data, which I did already. They want to be able to do many types of reports based on many more combinations of data than I could set as static queries. For instance, we have 25 fields and we could have up to 5 combinations of those 25 fields in a query, which is a very large subset of all the possible combinations.

Now my question seems simple but I don't know how to approach it. I want to have a form with 5 combo boxes that I could select any one of the 25 fields from the table and then generate a report query with just those 5 fields and the primary key sorted by the field(s) that I check with a check box. I hope that makes sense and I would appreciate any help.

 

[WayneRyan]

tmar,

Base your report on a query.

The query will have a criteria for each of the five important
fields like:

Forms![YourMainForm]![YourCombo]

The only provision is that your form must be open and have
the values in the appropriate fields for the query to work.

hth,
Wayne

 

[tmar89]

It's a good suggestion but I would need to add all 25 fields in the query then. Any other thoughts?

 

[WayneRyan]

tmar,

I just re-read your post.

You can have five combo-boxes each with an associated
text box. You can make a query (QueryDef) that will be
the source for your report.

Basically, you construct a SQL statement:

strSQL = "Select * " & _
         "From   YourTable " & _
         "Where " & Me.Combo1 & " = " & Me.TextBox1 & " And " & _
         "      " & Me.Combo2 & " = " & Me.TextBox2 & " And " & _
         "      " & Me.Combo3 & " = " & Me.TextBox3 & " And " & _
         "      " & Me.Combo4 & " = " & Me.TextBox4 & " And " & _
         "      " & Me.Combo5 & " = " & Me.TextBox5 & ";"

See the Help files and use the Search facility here for QueryDef.

You can refine the process by using an If statement, so that
the user doesn't have to fill out All five combos (field names).

Wayne

 

[tmar89]

Thanks.. i will play with this concept!

 

[WayneRyan]

tmar,

Great!

Your report button, can do something like"

strSQL = "Select * from YourTable"
If Me.Combo1 <> "" Then
   strSQL = strSQL & " where " & Me.combo1 & " = '" & Me.Textbox1 &  "'"
End If
If Me.Combo2 <> "" Then
   strSQL = strSQL & " and " & Me.combo2 & " = '" & Me.Textbox2 &  "'"
End If

and so on.

You could also make an operator box for "=", "Like" and so forth.

You might also use search here for "Multi-select" listboxes.
Because the way you traverse them to generate SQL is
somewhat similar.

hth,
Wayne

 

[tmar89]

one quick question.. i created the SQL statement and when I click the button it pops up a box asking for the criteria that I entered. So for instance, my combo box is "Lastname", my textbox is "Jones" and then when I hit SEARCH, a window pops up and says "Jones" and I have to type in the criteria. Why does it do that?

 

[WayneRyan]

tmar,

Are you using a query? Did you create a QueryDef? You did
that pretty fast.

You can't use a query, because the criteria fields change. Are
you building Text SQL strings to put into a QueryDef?

Wayne

 

[WayneRyan]

tmar,

Thought you might like some reading.

Roughly how to construct the SQL:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=60690&highlight=Querydef

Thread about QueryDefs and an example database:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=58923&highlight=Querydef

Wayne

 

[tmar89]

Private Sub Command5_Click()
Dim MyDB As Database
Dim qdf As QueryDef
Dim strSQL As String

Set MyDB = CurrentDb()

strSQL = "Select * " & _
"From tblX " & _
"Where " & Me.Combo0 & " = " & Me.Text0 & ";"

MyDB.QueryDefs.Delete "qryX"
Set qdf = MyDB.CreateQueryDef("qryX", strSQL)

DoCmd.OpenReport "qryX", acPreview
End Sub

how's that look?

 

[WayneRyan]

tmar,

Looks good, how'd it work.

Wayne

 

[tmar89]

its getting there. i still need to figure out a way to dynamically link the t fields in the report to only 5 field I select in the query

 

[WayneRyan]

tmar,

First ...

Select fldA, fldC, fldD, fldF, fldI
From   YourTable
Where fldA = "..." And
            fldB = "..."

But you'll never "know" which fields they might choose. The
SQL generates OK, but the report doesn't know which fields.

Try:

Select fldA As Field1, fldC As Field2, fldD As Field3, 
           fldF As Field4, fldI As Field5
From   YourTable
Where fldA = "..." And
            fldB = "..."

Then the Report Writer knows their names.

You could even make new fields in the query:

"'" & Me.Combo1 & "' As Field1Label

Those could be your headings on the report.

Wayne

 

[tmar89]

Wayne,

Thank you so much for your help! I have been messing with it and I now have a user defined field based search and they can sort the data based on any field they choose! Im learning as I go with this but you were a big stepping stone with this.

Tommy

 

[WayneRyan]

Tommy,

Glad to help. Look forward to seeing you here again.

Wayne