Print Report Showing Current fori info

[Killdozer]

Hi, I created a report from a form with 3 subforms. The report works fine, but how to I get it print with the current infomation or current record that is showing on the form, beside forwarding to the next record once in the report. Not sure I explain this correctly, basically when I open the report it should show the same info from the source form.

Phil

 

[wiklendt]

you need to pass the current pimarykey ID form your form to your report. in the DoCmd.OpenReport code for the button on your form which envokes the report, you should have a WHERE argument (without the word WHERE - OpenReport has an inherent where argument - i think it's third argument along?)

this argument should be something like YourReportID = Me.txtYourCurrentFormID, which you can delcare as a string and then just have the string as your argument

i.e.;

Private Sub cmdMassagePreview_Click()
On Error GoTo Err_cmdMassagePreview_Click

    Dim strDocName As String
    Dim strCriteria As String
    
    strDocName = "rptHorseMassage"
    [COLOR=Red]strCriteria = "[MassageID]= " & Me!cmbSelectPrintSession[/COLOR]
    
    DoCmd.OpenReport strDocName, acPreview, , [COLOR=Red]strCriteria[/COLOR], acDialog

End Sub

to explain the important bit:

strCriteria = "[MassageID]= " & Me!cmbSelectPrintSession

[MassageID]

is the primary ID in your report, that you want filtered.

Me!cmbSelectPrintSession

is the primary ID in your form, that you are currently viewing and want to appear in the report.

strCriteria

is just making the code tidy, and also helps in customisation in future (easier to see etc of that's going on)

you need all that punctuation so that the WHERE statement makes sense.

 

[Killdozer]

OK thanks, sounds complicated though, as I don't have any visual basic skils. Not sure how to bring up the "DoCMD". Is there a way under properities of the report under data source or someething like that to link my primary key from form to my report. or can I write a macro that would do the same thing?
Thanks for being patience
Phil

 

[wiklendt]

hey killdozer. no worries.

no, what you do is on the button - that i presume you have - which opens the report (you still haven't said how you're calling the report), you go to the properties of that button, then press the elipsis ("...") next to the "on click", and select "code builder"

then you simply copy and paste the code i have in my post and change the bits that pertain to you.

come back if you have any issues.

 

[Killdozer]

hey killdozer. no worries.

no, what you do is on the button - that i presume you have - which opens the report (you still haven't said how you're calling the report), you go to the properties of that button, then press the elipsis ("...") next to the "on click", and select "code builder"

then you simply copy and paste the code i have in my post and change the bits that pertain to you.

come back if you have any issues.

Thanks Wiklendt
I tried to get it to work today, using various ways and locations for the script. So for the button, are you referring to a command button on my form to open the report in print preview? I tried putting the command in the prosperities of the report so when it opens it would draw the info from the form, but I keep getting script errors.
My form name is "TrialInfo" my report name is "TrialInfoRpt" my primary key field is called “Trial File NO" would the spaces in the primary key field cause a problem? As I know it was a problem when I was making a calculation expression. So knowing my form, report, and primary key names, could you rewrite the script using my names? as I get confused easily
Oh by the way, I Like $3.00 bills

Killdozer

 

[jsv2002]

Is your report based on a query? If so and your form "TrialInfo" is the only form you intend to open it from you could but something like [Forms]![TrialInfo].[Trial File NO] in the criteria box of the query under your "Trial File No" field.

This will ONLY work when that form is open so is not ideal but if you are struggling with VB might be a short term solution.

Good luck John

 

[wiklendt]

Thanks Wiklendt
I tried to get it to work today, using various ways and locations for the script. So for the button, are you referring to a command button on my form to open the report in print preview? I tried putting the command in the prosperities of the report so when it opens it would draw the info from the form, but I keep getting script errors.
My form name is "TrialInfo" my report name is "TrialInfoRpt" my primary key field is called “Trial File NO" would the spaces in the primary key field cause a problem? As I know it was a problem when I was making a calculation expression. So knowing my form, report, and primary key names, could you rewrite the script using my names? as I get confused easily
Oh by the way, I Like $3.00 bills

Killdozer

script errors are usually very helpful in telling you where or what the problem actually is. if you can tell us what those errors say, along with a copy/paste of the code as you have it and where the error is pointing you to or highlighting, that would allow us to pin-point the problem and find a solution.

don't know why you'd need to put the code in various locations - just on the on click event of the button.

and as jsv2002 said, you'd need to put square brackets round field names with spaces... so you change “Trial File NO" to [Trial File NO].

There may be other reasons your code is error-ing. below i've highlighted the only bits you need to change (or that may be different) in the code (as a basic start) to make it work for you...

Private Sub [COLOR=Red]cmdYourButtonName[/COLOR]_Click()

    Dim strDocName As String
    Dim strCriteria As String
    
    strDocName = "[COLOR=Red]rptYourReportName[/COLOR]"
    [COLOR=Black]strCriteria = "[[COLOR=Red]FieldInYourReportThatYouWantToFilterBy[/COLOR]]= " & Me![COLOR=Red]controlWithSelectedItemToFilter[/COLOR][/COLOR]
    
    DoCmd.OpenReport strDocName, acPreview, , [COLOR=Black]strCriteria[/COLOR], acDialog 'acDialog optional - opens report in a "pop-up" view

End Sub

 

[Killdozer]

Is your report based on a query? If so and your form "TrialInfo" is the only form you intend to open it from you could but something like [Forms]![TrialInfo].[Trial File NO] in the criteria box of the query under your "Trial File No" field.

This will ONLY work when that form is open so is not ideal but if you are struggling with VB might be a short term solution.

Good luck John

My report is based on my form, basically I justed saved my form as a report. My form is based on 4 seperate queries. I did try placing the info in the criteria of the querry which makes up the main form in the [trial file no] field. The rest are sub forms, I then opened the form then the report, but the report still opens showing the first record. regardless of which record the form is showing

 

[wiklendt]

My report is based on my form, basically I justed saved my form as a report. My form is based on 4 seperate queries. I did try placing the info in the criteria of the querry which makes up the main form in the [trial file no] field. The rest are sub forms, I then opened the form then the report, but the report still opens showing the first record. regardless of which record the form is showing

ok, i think we have a design issue. that and no one form or report can be based on four separate queries. each form or subform is only based on their own one, which is what i think you mean.

your subforms ought to be getting their data from the main form/report. you need to filter JUST the main report, and teh data should 'flow through'.

out of curiosity, do your subforms display as subreports ok? or do they show up blank? i only ask because i didn't think it was possible to display a subform as a subreport...

please do this:

go to your record source property of your main report (not the subs) - what do you see? i.e., what is your 'report' REALLY based on? a snapshot view of this would be uber helpful.

i.e., go to your MAIN report (that you are trying to filter), and in the 'record source' property (or "row source", i alwyas get mixed up which is which), click on the elipsis and tell us what is there (or make a picture of it). THEN tell us which field it is exactly that you want to filter by, and tell us exactly which field in the form you want the 'current' record to be displayed in the report.

then, go to the code that you have for your button. copy and paste that code for us so we can look at it.

 

[jsv2002]

"My report is based on my form, basically I justed saved my form as a report."

You mean when the form was in design view you went to file save as and selected Report?

That is not the way to make a report unfortunately essentially it will just make a form within a report and be totally useless I'm afraid

You really need to build a report o a table or better still a query to pull together the information you want you can include subreports within this with linked fields to the primary key the same as subforms.

Good luck John

 

[Killdozer]

What I have is a main form with just a few fields, like the [trial No], [Trial name], ect. This is shown in the Form view. Then I have four other forms inserted into the main form as subforms, these subforms show their data in the data sheet view, all are linked by the [Trial No] which is the primary key. All data and calculations in the form is displayed, as well as when I view the report. To make the report I used my form (Main form with subforms) and saved as and then chose a report type to save it. It shows up under the report list and looks like any other normal report. The reason I chose this method is my form has several calculations and was I was having trouble with the sum calculations in the queries. But could do them in the form based off the query. I did make an all encompassing query based on all five separate query, but did know how to do sum calculations in a report. But as of now I will need to wait until Mon to get my other copy of my dbase, as for some reason my form will no longer execute an open report button or macro, even though the report will open if clicked on directly. I did get the script on the properties of the preview report button, as shown. The report would open but again the data did not match the form record. But after a couple more tries it would no longer open the report. I sense erase it as I believed it must had prevented the report from opening.
My dbase is approx only 1 mb in size, if any of you would like to take a look at it, maybe I could sent it by email or FTP it using the SendthisFile program.

Phil

 

[wiklendt]

ok, i suggest you do this: create a query with as many calculations as you can put into it. then, create a report from scratch (not based off a form). we can then help you with any summing you need.

if you still want to attach your db, 1mb could easily be reduced (without removing things from it) - do this:

1) "compact and repair" your database.

this will be located differently in different versions of access. (i usually set my preferences as "compact on close" - unfortunately this must be done individually for each database created).
​

2) compress the file into a zip.

if you are using windows xp or vista, (not sure about previous versions) you can just right-click on the file, and select "send to..." then "compressed folder", and this ought to create a zip file for you.
​

3) check file size.

if it is under 785.9 KB, then you can attach it here.
​

 

[wiklendt]

and what do you mean by "other" copy of the db? i generally make a backup before i fiddle with any db, so that i can revert to it if is stuff something up. i suggest you do something similar - believe me, it will come in handy one day!

 

[Killdozer]

Re: Print Report Showing Current form info

Ok, I will make a query wilth the calculations I can, and do a report from that. The db I messed up was my backup copy on my mem stick. My good copy is on my work computer. I will try to shrink my db down enought to attach to the forum.
I will be back Mon Night.

Thanks again

 

[jsv2002]

BTW you can create subreports from your existing tables/querys that your forms/subforms draw there information from this should make things easier providing that you have referantial integrity and the appropriate linked fields (or foriegn keys)

good luck John

 

[Killdozer]

Hi again, I was able to get my report to reopen from my form, the one part of the script that I;m not sure about is:

strCriteria = "[FieldInYourReportThatYouWantToFilterBy]= " & Me!controlWithSelectedItemToFilter

I changed it to:
strCriteria = "[TrialNO]= " & Me!TrialNO

I renamed the field to remove the spaces, but still doesn't work. I compact the db, and I used a program called 7Zip to compress it. It is now uploaded if anyone wants to test it. Any data is just fictitious and of zero value.

 

[wiklendt]

Hi again, I was able to get my report to reopen from my form, the one part of the script that I;m not sure about is:

strCriteria = "[FieldInYourReportThatYouWantToFilterBy]= " & Me!controlWithSelectedItemToFilter

I changed it to:
strCriteria = "[TrialNO]= " & Me!TrialNO

I renamed the field to remove the spaces, but still doesn't work. I compact the db, and I used a program called 7Zip to compress it. It is now uploaded if anyone wants to test it. Any data is just fictitious and of zero value.

ok, you had a lot of bits missing in the code... this is what it should look like:

Private Sub Command64_Click()
On Error GoTo Err_Command64_Click

    Dim stDocName As String
    Dim strCriteria As String

    stDocName = "TrialInfoRpt"
    strCriteria = "[TrialNO]= " & Me!TrialNO
    
    DoCmd.OpenReport stDocName, acViewReport, , strCriteria
[INDENT] ' the "acViewReport" can be changed to whatever you want - it changes things like whether you send it direct to the printer, or print-preview it first, or view it as a report...
[/INDENT] 
Exit_Command64_Click:
    Exit Sub

Err_Command64_Click:
    MsgBox Err.Description
    Resume Exit_Command64_Click

BUT, now it's giving a "Data type Mismatch" error, which i think could be caused by the fact that your primary keys are all text, not numbers. let me fiddle a little to see if i can get around that, but you really should have your primary keys as autonumbers... if you then want an intuitive number for your invoices, they can be a separate text or other field, but for access to do its thing, it's best (i think) to use autonumber for primary keys...

 

[wiklendt]

oooo! oooo! i got it.

while i strill strongly urge you to make your primary identifiers as autonumber, the issue in your DB now was passing a string in a string, which actually is accomplished via a clever trick with apostrophes... looks like this:

Private Sub Command64_Click()
On Error GoTo Err_Command64_Click

    Dim stDocName As String
    Dim strCriteria As String

    stDocName = "TrialInfoRpt"
    strCriteria = "[TrialNO]= [COLOR=Red]'[/COLOR]" & Me![COLOR=Red][[/COLOR]TrialNO[COLOR=Red]][/COLOR] [COLOR=Red]& "'"[/COLOR]
    
    DoCmd.OpenReport stDocName, acViewReport, , strCriteria

Exit_Command64_Click:
    Exit Sub

Err_Command64_Click:
    MsgBox Err.Description
    Resume Exit_Command64_Click
    
End Sub

this will get you going for now

 

[Killdozer]

Your a Genius. I just changed it so it would preview the report. Works great. This sure saved me a lot of time from having to build a report from scratch off a new query, then try to figure out the calculations I need.
Thanks ever so much!
Can I add you to my friends list?

Phil
Killdozer

 

[wiklendt]

Your a Genius. I just changed it so it would preview the report. Works great. This sure saved me a lot of time from having to build a report from scratch off a new query, then try to figure out the calculations I need.
Thanks ever so much!
Can I add you to my friends list?

Phil
Killdozer

hey, no, i'm not a genius - i stumble along like everyone else!

you wouldn't need to build a new report or a new query, what was causing all the issues is the fact that you PK is text.