Query -> Report (help!)

[deno]

i'm pretty new to access and have got stuck on this problem. i've build a form that has a bunch of check and text boxes which are used to build this (huge) query. everything is good with that but i can't figure out how to get the record set into a report.

thanks,
deno

 

[DJBummy]

I think this is what you want.
Create a report based on the query that you create on the form.
Then on the form put a command button that opens the report after you populate the query.


D.J.

 

[deno]

but how would i do that if my query changes everytime? the form that builds the query has check boxes for each day of the week so the user can check what day of week to search in.

i do have a query that real close but it prompts the user with dialog boxes for input that get used in the WHERE clause.

-deno

 

[DJBummy]

Let me make sure I understand.
Are you using the form to select the criteria for the query?
What is the name of the query?
Will the same fields be used for every report?

[This message has been edited by DJBummy (edited 08-26-2001).]

 

[deno]

>Are you using the form to select the criteria for the query?
yes

>What is the name of the query?
the query is built completely in VBA.

>Will the same fields be used for every report?

yes.

i hope i'm going about this the correct way.
this query does a select (and sub select) across multiple tables and the form i have built lets the user change 5 different seach criteria.


thanks_deno

 

[DJBummy]

Hi deno

Sorry I'm not sure how this may be done. I would have to do some research first.

Try this link it may be what you are looking for.
http://www.access-programmers.co.uk/ubb/Forum5/HTML/000345.html

Good luck

D.J.

 

[Chris RR]

Why not save the query? Then you can build one, create the report based on it (since you said that the fields stay the same) and even though the query changes, Access will be happy.

I do something similar in a system that builds a customized passthrough query. I delete the old version, build the new one and save it and run it. The next person through overwrites the whole business, but you'd be surprised at how often I've had to have someone run their query, then get in quick make a copy of that sql to debug some trouble or another they've had (often because they didn't understand the data.)

The code that writes the query goes like this:
Dim myQ As QueryDef
Dim strQuery As String
' Delete the existing qCustomized_PT
On Error Resume Next
DoCmd.DeleteObject acQuery, "qCustomized_PT"

On Error GoTo My_Error_Routine
Set myQ = myDb.CreateQueryDef("qCustomized_PT")

' lots of other code here
' to build the query in strQuery

myQ.SQL = strQuery

 

[deno]

thanks!!

that seems to work great. if you could just clue me into a couple more things i should be all sorted away. what's your error sub look like and is there anyway to pass inputted data from the form to the report?

thank,
deno

 

[deno]

ok i'm stuck again. how do i to the same thing but with a table instead of a query?

 

[Chris RR]

The error sub is not fancy:
WriteaPT_Error:
Debug.Print Err.Number
If Err.Number = 3021 Then
strMsg = "Your Query did not find any records. Please review your criteria!"
strStyle = vbOKOnly + vbInformation
strResponse = MsgBox(strMsg, strStyle, "No Data Available")
Err.Clear
GoTo WriteaPT_Close
End If

MsgBox Err.Description
DoCmd.Hourglass False
Resume Exit_WriteaPT

Now, on your first question... I write my queries in a subroutine and against an Oracle table, so my code would be a little different than yours... But if you just did it without a sub, you'd end up with code kind of like this.

' First, write the select. Here we will
' take everything from the table MYTABLE
strQuery = "Select * from MYTABLE "
' Then build the WHERE clause
' with as many criteria as you need...
strQuery = strQuery & "WHERE CUSTNO like *'" & me.CUSTNO & "*'" & vbCrLf
strQuery = strQuery & " AND COUNTRY = " & Me.CTRYCODE
' You could add order by clauses if you need to sort