To many reports 2

[paul123456]

I have 5 different letters that we use for our database as reports. all 5 require different queiries because of the criteria. I'm not sure if im doing this correctly but i have created 5 of the same report each pointing to 5 different queries. so i have a total of 25 reports. 5 of them are the same, but they are just pointed to a query that will ask a different question. IT "enter social" or "enter id NUmber" and so on. im i doing this corretly or is there an easier was to do it?

Thanks, PAUL

 

[scriverb]

There are a number of ways to do this. But, a simple way would be for one of your reports "Report1" just use one query as the Record Source and then when making your VBA call to run the report use the WHERE criteria parameter to prompt for the selection needed.

DoCmd.OpenReport "Report1", acViewPreview, , "(((tblYourTableName.Field1) = [Enter selection data]))"

This would get you down to one report with one query just different ways of prompting for the correct records. Your queries would be created WITHOUT the criteria row information. They would just be Select of all the records. The WHERE parameter of the OpenReport command would prompt for the user input and select the correct records.

Let me know if this technique is useful to you.


Bob Scriver

 

[GingerR]

You can create a form where the user chooses if they are wanting a report By Social or By ID Number or anything else you like, then they hit a button and in a small, simple bit of code, you can open one single report that shows the data they want. There are several ways to accomplish this; this one is pretty simple and looks good too.

1) Create a new Form. Call it ReportScreen.
2) Make an OPTION GROUP (in the toolbox: the thing that looks like a box with 'xyz' at the top of it). Fill in the choices with By Social, By ID Number, etc, whatever your choices are. Name the Option GROUP fraReportChoice. each of the five choices you enter in have an ID. look at the properties of each radio button (or check box or whatever style you made each one). each has a value (Option Value), see? when someone clicks one of those choices, the value of the OPTION GROUP changes to whatever that radio button's value is. then, depending on which one the user has chosen, we'll ask them a different question.
3) Put a button on the Form. hit CANCEL if the wizard comes up. name it btnViewReport.
4) in the OnClick event of the button, choose [Event Procedure] and click the little button to the right with the three dots on it. In the routine, paste this code and substitute whatever the questions are you want to ask when each of the buttons/radio buttons is selected
Sub btnViewReport_Click()

Dim strWhere
    Select Case fraReportChoice
    Case 1
        strWhere = "Social = '" & InputBox("Enter Social:", "CRITERIA") & "'"
        
    Case 2
        strWhere = "ID = " & InputBox("Enter ID Number:", "CRITERIA")
    Case 3
        
        
    Case 4
    
    Case 5
    
    End Select
    
    DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

End Sub

I'm guessing at your field names.
for example, what i'm doing is building the "WHERE" clause, which is what happends sort of behind the scenes when you put criteria in a query. this way you dont have to build lots of diff queries, just build diff "WHERE" clauses.

picking apart my "WHERE" clause, it's basically this:

strWhere = "FIELD1 = 'XYZ'"

so only records where FIELD1 = 'XYZ' will be returned.

InputBox is a command that does that prompting thing. the first part is the question you want to ask, the second part is the title that appears at the top of the box whcn it is displayed ("CRITERIA&quot. you can make that whatever you want.

so,

strWhere = "Social = '" & InputBox("Enter Social:", "CRITERIA") & "'"

will only return records where the field "SOCIAL" = value entered in the input box.

so fill in that code with your five choices, fill in your report name in place of my report "ReportName", and view the form in FORM VIEW, make a choice and hit the button.

hope this helps.

 

[paul123456]

Hey bob i was just wondering were i would put the where criteria at? For example i have most of my reports executing at the switchboard manager. so if a user want to view or print a report based on a group of id numbers i would like it to ask for which starting and ending id numbers to use. but i have multiple questions i would ask. and as u know i would like to only use one report. for example. The user can view report but putting in the social security number, or by using a group of id number, or by a specific date. right now i could do all that but i created 6 of the same reports all with different queries. its kinda a pain to keep updated because if you have changes to one report then you have to make changes to all 6. But like i said i would like all of this to be able to execute from the switchboard manager. but i don't konw were to put the where statement in?

Thanks, PAUL

 

[paul123456]

OK Ginger i have done this and I like the way this works..Its very easy to set up and I think this will look the best.. I get it to work But i am having a problem with one thing..I can't seem to get it to print a group of ID numbers..this is what i have

Case 2
strWhere = "IdNum '= " & InputBox("Between [Enter start ID Number] And [Enter end ID Number]", "CRITERIA&quot

How can i get it to print a group of id numbers?

On my query that I used before i had Between [Enter start ID Number] And [Enter end ID Number]

Im not sure how to set it up to do this? could you plz point me in the right direction? or someone?


Thanks, PAUL

 

[GingerR]

Case 2
dim FromID, ToID as string
FromID = inputbox("Enter start ID Number","CRITERIA")
ToID = inputbox("Enter end ID Number","CRITERIA")

strWhere = "IDNum between '" & FromID & "' and '"& ToID & "'"

if your ID number is an integer (vs. a text/string type) then do this instead

dim FromID, ToID as integer
FromID = inputbox("Enter start ID Number","CRITERIA")
ToID = inputbox("Enter end ID Number","CRITERIA")

strWhere = "IDNum between " & FromID & " and "& ToID

the single quotes in the strWhere accomodate a string/text field type; without them is for numbers.

so try that out....


another option is this: if there will be several diff reports based on these ID numbers, you can put text boxes on your form (i.e. txtFromID and txtToID). then

 strWhere = "Between " & forms!formname![txtFromID] & " and " & forms!formname![txtToID][code]

then users dont have to keep entering the same ID's over and over.

 

[paul123456]

Hi Ginger first off thnx for helping me out. The social and group of ID numbers works great. But i am having one problem. I can't get a date range to work. The Format of the date is Date/Time so im not sure what i should put in.. I have tried it as "Date" and like you said played with the simicolons but had no luck. here is the code i am using

Case 4
Dim FromID1, ToID1 As Date
FromID1 = InputBox("Enter start Verified Date:", "CRITERIA&quot
ToID1 = InputBox("Enter end Verified Date:", "CRITERIA&quot

strWhere = "VD between " & FromID1 & " and " & ToID1


when i put my date ranges i simply get a error on all of my fields in the report.

I am putting in 01/01/2000 - 01/01/2003 as my range

Maybe you can see something wrong in this..untill then i will keep playing with it


Thanks, PAUL

 

[GingerR]

strWhere = "VD between #" & FromID1 & "# and "# & ToID1 & "#"

try that--the number signs signal to access that it's a date.
so the statement translates as

"VD between #1/1/00# and #1/1/03#"

 

[paul123456]

No go on that Gin...it gives me a syntax error. Im gonna read some help files and maybe i'll find something that'l work.. I'll post it if it find the solution, thnx for the try though

Thanks, PAUL

 

[GingerR]

oops juxtaposed a quote and a #:


strWhere = "VD between #" & FromID1 & "# and #" & ToID1 & "#"

 

[paul123456]

Very nice and fancy...wow this is very nice to use makes then a lot easier to setup and manage. I have one more to do...and i saved the best for last. I got this module from a post on tek-tips and the writing said he got it from microsoft site. What is allows me to do is print letters with mutiple ID numbers. For example i can put in the box 1751,154,5116,1561,6164,212 and it will view those id numbers for me so i can print them out. I have no idea how to set this up on one of the events for the option group. here is the module code used.
-------------------------------------------------------
Option Compare Database
'************************************************************
'Declarations section of the module.
'************************************************************

Option Explicit

'============================================================
' The GetToken() function defines the delimiter character.
'============================================================

Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = &quot;&quot;
End If
GetToken = stToken
End Function

'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the In() operator.
'============================================================
Function InParam(Fld, Param)
Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = &quot;&quot;
Do While (Len(Param) > 0)
stToken = GetToken(Param, &quot;,&quot
If stToken = LTrim$(RTrim$(Fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function

---------------------------------------------------

also on my query that i use for the report on the &quot;field&quot; space i have
----------------------------------
IDList: InParam([ID],[Enter mulitiple ID numbers])
----------------------------------

and i have the criteria set to &quot;True&quot;

I was thinking this is to hard to set up for the option group and was thinking of just making a copy of the report and pointing it to my query.

Thanks, PAUL

 

[GingerR]

ok i figured something out that's at least a little more efficient than making two dup reports:
first, have you saved that code into a module, and understand how it works and everything?
do you have the second query written and saved?

ok let's assume you have a second saved query with that code in it form your previous post. pretend it's called Query2, and your orig query is Query1.

put a text box on your form. call it txtReportName.
in all of your CASE statements, set txtReportName to the name of the query that is appropriate to be the recordsource of the report for that option choice:
i.e.
CASE 1
blah
me.txtReportName = &quot;Query1&quot;

CASE 2
blah
me.txtReportName = &quot;Query2&quot;

etc.

now in your report's OnOpen event, put
me.recordsource = forms!FormName!txtReportName.

all done!

by the way you can then make the txtReportName invisible.

g

 

[paul123456]

WOW! this is real nice...with all the info you gave me im gonna make another option group in that same form so that the user can hit what report to print..then that option group will be invisible and this option group will appear so the user can pick by what info to print it by..but i never thought how fancy this thing really is. thnk you very much for your help.. I couldn't have done it without you..

The only thing i had to change in you above code is the brackets in

me.recordsource = forms![FormName]!txtReportName

thnx again GingerR for ALL YOUR HELP!

Thanks, PAUL

 

[GingerR]

have fun! yes there is a lot of neat stuff you can do!!

g