Printing current record as a report 3

[SBelyea]

This is driving me absolutely nuts!!!

Within my database, you can open a form based off of tbl_streets that has a bunch of different data. tbl_streets has a PK named SID (an AutoNumber). I want to be able to print everything I can see in this form view (data from tbl_streets, tbl_maintenance, tbl_distance) as a report. My current (not working) method has been to create a report based off of all three tables, and then attempt to print a report based on the SID of the current record being viewed. I've only had dismal failures trying this. Here is my current code:

Private Sub Image13_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
On Error GoTo Err_Image13_MouseUp
'   Special Effect (Flat)
    Image13.SpecialEffect = 0
'   Open Form
    Dim strDocName As String
            
    strDocName = "rpt_streets"
        
    DoCmd.OpenReport strDocName, aViewNormal, "qry_rpt_streets"

Exit_Image13_MouseUp:
    Exit Sub

Err_Image13_MouseUp:
    MsgBox Err.Description
    Resume Exit_Image13_MouseUp
    
End Sub

My report is called rpt_streets.

 

[kjv1611]

Can't you just create the report from the form, instead of from the tables? It seems I remember the Report wizard giving the option to build from a form..

--

"If to err is human, then I must be some kind of human!" -Me

 

[SBelyea]

Thanks for the heads up - I tried that option, however, and it doesn't return what I need. I'm using a report built from the ground up because I need to show multiple attached records for each database entry - with my form saved as a report, it still only shows single attached records for each entry.

Is there a simple way to pass my SID from my form to my report so that I can print my specified record?

 

[kjv1611]

At least on a form, not 100% sure with a report, you can change the layout to "Tabular" which shows more than one record.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

Is there a simple way to pass my SID from my form to my report so that I can print my specified record?

http://www.mvps.org/access/reports/rpt0002.htm

I would not attempt to print a form. You can use subreports like you would use subforms.

Duane
Hook'D on Access
MS Access MVP

 

[SBelyea]

Thanks for the heads up dhookom! I tried to use this code, but I get prompted for my SID value each time I try to run it. Here's my code:

Dim strDocName As String
Dim strWhere As String
    strDocName = "rptSomeReport"
    strWhere = "[SID]=" & me!SID
    DoCmd.OpenReport strDocName, acPreview, , strWhere

There is an SID value on my form, as well as an SID field on my report. The SID field is an autonumber and a PK. Do I need to change the syntax in the code to make this work?

 

[kjv1611]

Try changing [blue]me![/blue] to the actual path of the object, something like:

[blue]Forms!frmMyForm[/blue]!SID

Or

[blue]Reports!rptSomeReport[/blue]!SID

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And if it is a text/string value, and not numeric, then you may need to add in apostrophes, something like this:

strWhere = "[SID]= [highlight]'[/highlight]" & me!SID [highlight]& "'"[/highlight]

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

'Course, I meant "single quotes", but it's all the same on the keyboard.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

SBelyea stated SID is an autonumber primary key. That suggests the value is numeric.

I would confirm there is actually a field named SID in the record source of the form and the report.

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

SBelyea stated SID is an autonumber primary key. That suggests the value is numeric.

Must've missed that part - yeah, on the very first line.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SBelyea]

kjv1611:

I tried your changes in the code (explicitly naming my form and report) to no avail. When I changed it to

 strWhere = "[SID]=" & Forms!frm_street_name!SID

I am still prompted for an SID value, and even after I put one in the report shows up blank.

For the code

strWhere = "[SID]=" & Reports!rpt_streets!SID

I receive an error stating that "rpt_streets" does not exist (although it most certainly does it exist.]

dhookom:

The report has the SID field from tbl_streets on it, and the form was constructed based on tbl_streets. I do have these fields not visible, but they do exist on the form. Would this make a difference?


Thanks for all of you help you two have already provided - I have no idea why this won't work!

 

[Remou]

If it is not open, it does not exist in the reports collection. Is SID a field in the report? Is your report based on a query that asks for an SID?

strWhere = "[SID]=" & Forms!frm_street_name!SID

Would normally be correct.

 

[SBelyea]

Here is the record source for my report:

SELECT [tbl_street_name].[SID] AS tbl_street_name_SID, [tbl_street_name].[street_name], [tbl_street_name].[DID] AS tbl_street_name_DID, [tbl_distance].[DID] AS tbl_distance_DID, [tbl_distance].[from], [tbl_distance].[to], [tbl_distance].[length], [tbl_distance].[SID] AS tbl_distance_SID, [tbl_maintenance].[MID], [tbl_maintenance].[type_of_maintenance], [tbl_maintenance].[date_planned], [tbl_maintenance].[date_completed], [tbl_maintenance].[maintenance_evaluation], [tbl_maintenance].[pavement_evaluation], [tbl_maintenance].[DID] AS tbl_maintenance_DID, [tbl_maintenance].[pavement_notes], [tbl_maintenance].[maintenance_notes], [tbl_distance].[width], [tbl_maintenance].[paved_width] FROM tbl_street_name INNER JOIN (tbl_distance INNER JOIN tbl_maintenance ON [tbl_distance].[DID]=[tbl_maintenance].[DID]) ON [tbl_street_name].[SID]=[tbl_distance].[SID];

Could it be (perhaps) that since my [tbl_street_name].[SID] is stored as tbl_street_name.SID?

 

[kjv1611]

Looks like you should then be referencing either

[blue]tbl_street_name_SID[/blue]
or
[blue]tbl_distance_SID[/blue]

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

You have an alias on the name. Try:


strWhere = "tbl_street_name_SID=" & Forms!frm_street_name!SID

 

[SBelyea]

You guys are absolutely amazing!!!!!

Changing the line of code to

strWhere = "tbl_street_name_SID=" & Forms!frm_street_name!SID

worked!

Remou, kjv1611 and dhookom, thanks for helping me figure this out! I appreciate all of the help you provided! Shiny stars all around to this crowd!

 

[dhookom]

I would not use Forms!frm_street_name since this can be replaced by Me.

strWhere = "tbl_street_name_SID=" & Me.SID

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

Or if your code is Form-Level, and will never be anything but, then you can just do:

strWhere = "tbl_street_name_SID=" [highlight]& SID[/highlight]

--

"If to err is human, then I must be some kind of human!" -Me

 

[SBelyea]

Thanks again for all of your help!