Solved Generate report from form and subforms (1 Viewer)

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
Hi Everyone,
I've got a main form (frm_AddEdit), a related subform (frm_subCalRecords) and a second subform (frm_subStds)
The main form is related to both subforms using the PK field 'EquipID'
I want to put a 'Print' command button on the main form which will print the contents of that main form and also the contents of the subforms which are showing at the time. The subforms have many records but the print button always only prints the first one which isn't what i need. The table structure is attached.

I know my normalization isn't exactly spot on but each piece of equipment can have many calibration records and the 'Standards' table relates to the equipment and not the calibration records. Hope i've explained it ok ? I've tried the following code but get a data mismatch error

Code:
DoCmd.OpenReport "rpt_PrintRecord", acViewPreview, WhereCondition:="[EquipID]=" & Me.frmsub_CalRecords.Form.CalibrationID

Thanks guys,
Em
 

Attachments

  • Tables.PNG
    Tables.PNG
    14.1 KB · Views: 373

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
create appropriate Report/Sub Reports and use this to print.

DoCmd.OpenReport "rpt_PrintRecord", acViewPreview, WhereCondition:="[EquipID]=" & Me!EquipID

Note:
subreports should have Master/Child Link fields [EquipID].
 
Last edited:

Ranman256

Well-known member
Local time
Today, 09:27
Joined
Apr 9, 2015
Messages
4,339
Code:
DoCmd.OpenReport "rpt_PrintRecord", acViewPreview, , "[EquipID]=" & Me.frmsub_CalRecords.Form.CalibrationID
 

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
create appropriate Report/Sub Reports and use this to print.

DoCmd.OpenReport "rpt_PrintRecord", acViewPreview, WhereCondition:="[EquipID]=" & Me!EquipID

Note:
subreports should have Master/Child Link fields [EquipID].
Arnel.....i've added the code and when the button is activated i get a pop-up message with the EquipID value at the top (attached). If i enter that value into the parameter box then the report opens fine
 

Attachments

  • Error.PNG
    Error.PNG
    2.2 KB · Views: 376

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
Code:
DoCmd.OpenReport "rpt_PrintRecord", acViewPreview, , "[EquipID]=" & Me.frmsub_CalRecords.Form.CalibrationID
Ranman....i get a data mismatch error on clicking the button
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
EquipID is string?

DoCmd.OpenReport "rpt_PrintRecord", acViewPreview, WhereCondition:="[EquipID]='" & Me!EquipID & "'"
 

Mike Krailo

Well-known member
Local time
Today, 09:27
Joined
Mar 28, 2020
Messages
1,030
I know unrelated to your question but what exactly is the Standards table for? It just has a Manufacture attribute in it which is already imbedded in the EquipID so it makes no sense at all to me what that table is for.
 

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
EquipID is string?

DoCmd.OpenReport "rpt_PrintRecord", acViewPreview, WhereCondition:="[EquipID]='" & Me!EquipID & "'"
Yes i'm sorry....EquipID is always a letter followed by digits.
The report is opening up ok now but the subreport displays all the records and not the one i'm viewing. For example....i might need to print off the subreport record from a year ago and so i need that one to appear on it's own
 

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
I know unrelated to your question but what exactly is the Standards table for? It just has a Manufacture attribute in it which is already imbedded in the EquipID so it makes no sense at all to me what that table is for.
Mike.....the screenshot doesn't show all the fields in the Standards table. Each piece of equipment has a manufacturer but each standard also has a manufacturer. Each piece of equipment also uses a certain standard. The Standards are also completely unrelated to the Calibration records. Confused ??....so am i :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
the subreports must have a Master/Child Link Fields.

goto design view of your Main report, click on the subreport and on it's property
add the Master/Child link fields, which is the EquipID
 

Mike Krailo

Well-known member
Local time
Today, 09:27
Joined
Mar 28, 2020
Messages
1,030
I see, then you should name that second Manufacture differently, like Manufacture2 or SManufacture for clarity. Then also the data for "Certain Standard" is still unclear (I know unrelated to your question). It seems like a many to many relationship exists between Equipment and Standards. If that is the case, then there should be a junction table that joins them together.

The reason I'm asking all this, is to save you the frustration of trying to create forms and reports when you haven't sorted out your tables yet. Much better to get your tables nailed down solid first and then design your forms and reports.
 

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
the subreports must have a Master/Child Link Fields.

goto design view of your Main report, click on the subreport and on it's property
add the Master/Child link fields, which is the EquipID
I checked both subreports and the master/child link fields are set to EquipID
Each piece of equipment may have several calibration records in the subreport and these are what's printing. So for example say that a particular piece of equipment has 10 calibration records, these are what's printing but i just want the one which is selected on the screen at the time
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
on your "calibration" subform's current event add code:

private sub form_current()
[tempvars]![tvarCalibID] = Nz(Me!CalibrationID, 0)
end sub


back to your report, on the calibration subReport, add to Master/Child Link fields:

EquipID, [Tempvars]![tvarCalibID]
 

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
on your "calibration" subform's current event add code:

private sub form_current()
[tempvars]![tvarCalibID] = Nz(Me!CalibrationID, 0)
end sub


back to your report, on the calibration subReport, add to Master/Child Link fields:

EquipID, [Tempvars]![tvarCalibID]
Thanks Arnel.....there was already some code in the OnCurrent event of that subform so i just added that code as a new line. Is that ok ?
I added the other code to the Calibration subreport properties (attached)......is that what you meant ?
When i click the print button now i'm still getting all the calibration records for that equipment
 

Attachments

  • Code.PNG
    Code.PNG
    3.3 KB · Views: 374

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
on the Link Child field, instead of the Tempvars!tvarCalibID, change it to [CalibrationID].
 

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
I've changed it and the calibration subreport is blank. I've attached the change so you can see if i did it right
 

Attachments

  • Capture.PNG
    Capture.PNG
    2.9 KB · Views: 383

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
almost, change their position the Master becomes the Child and vice versa.
ohh, no, i am the one who is mixed up:

try:

Master Link Fields: [EquipID];[Tempvars]![tvarCalibID]
Child Link Fields: [EquipID]; [CalibrationID]

you make sure on the current event of yourr sub, you create the tempvar:

private sub form_current()
[Tempvars]![tvarCalibID] = Me!CalibrationID
'other codes here
end sub
 
Last edited:

Emma35

Registered User.
Local time
Today, 06:27
Joined
Sep 18, 2012
Messages
455
almost, change their position the Master becomes the Child and vice versa.
ohh, no, i am the one who is mixed up:

try:

Master Link Fields: [EquipID];[Tempvars]![tvarCalibID]
Child Link Fields: [EquipID]; [CalibrationID]

you make sure on the current event of yourr sub, you create the tempvar:

private sub form_current()
[Tempvars]![tvarCalibID] = Me!CalibrationID
'other codes here
end sub
Wow....working perfectly. You are a genius Mr Arnel....thank you very much. Also, thanks to everyone who took the time to make a suggestion. Much appreciated

Em x
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169

Users who are viewing this thread

Top Bottom