Transfer Form with Macros to Report

[jds]

Hello all

I am working on fire dispatch form for our service and have run into a snag with Hide/Unhide macros when transfering data from a form to a report.

What I have on the form is 3 option buttons that will hide or unhide certain fields as it pertains to different call types. Although these work fine, I have made a mirror image of the form in a report and have a command button transfering all the data to the report. The problem I am having, is trying to find a code that will distinguish which fields are hidden and which are not. Once the report has been created, I would then export it as a snapshot so it can be emailed to the girls at the office for processing. The code I have for transfering data was from a user here named Rusty and it looks like this:
Private Sub command703_Click()
On Error GoTo Err_command703_Click

'*** open the report
DoCmd.OpenReport "report name", acPreview, , "[SS#] like '" & Me![SSNum] & "'"
'*** [SS#] is the Control Source name of the field on the report
'*** [SSNum] is the field name as it appears on the form

Exit_command703_Click:
Exit Sub

Err_command703_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click
End Sub

This code works great. I hope I have made a clear discription of what I am aiming for. If not please let me know, and if needed I can post my DB and hopefully it might help.

Great Thanxs ahead of time

JDS

 

[WayneRyan]

jds,

Haven't done this exact thing, but I'd forget about the Macro approach.

If the form is open when the report is run, you can use something like
the OnOpen event:

If Forms![YourMainForm]![Field#1] = "SomeValue" Then
   Me.ReportsControl#1.Visible = False
Else
   Me.ReportsControl#1.Visible = True
End If

You can repeat that for all desired fields.

Wayne

 

[jds]

Need a few more pointers

Hi Wayne

Thanks for the reply. Yours is an excellent idea, I'm just having a little trouble getting it to function appropriately. What I should say is I'm not quite sure where to insert the coding you gave me. So here is my code for opening the report. (report name is Test Report. Identified by IncidentNumber)

Private Sub Command704_Click()
On Error GoTo Err_Command704_Click


DoCmd.OpenReport "Test Report", acPreview, , "[IncidentNumber] like '" & Me![IncidentNumber] & "'"

Exit_Command704_Click:
Exit Sub

Err_Command704_Click:
MsgBox Err.Description
Resume Exit_Command704_Click

End Sub



And here is what you have given me with my form name and one of the fields. I think my other question is where you have "SomeValue" and "ReportControl#1", am I to insert some of my data or report names in there?


If Forms![Sceneform]![Driver] = "SomeValue" Then
Me.ReportsControl#1.Visible = False
Else
Me.ReportsControl#1.Visible = True
End If


I really appreciate your help

Thank You

Jason

 

[Pat Hartman]

DoCmd.OpenReport "Test Report", acPreview, , "[IncidentNumber] like '" & Me![IncidentNumber] & "'"

- The LIKE operator is ONLY used when you have apartial value and you are also using wild card characters to supply the missing characters. Change the statement to:

DoCmd.OpenReport "Test Report", acPreview, , "[IncidentNumber] = '" & Me.IncidentNumber & "'"

When you reference a form field from outside the form, you ONLY have access to the CURRENT record. Rather than referencing a field on a form which might not even be for the same record the report is displaying, make sure the necessary field is included in the report's RecordSource so you can reference the report's field.

If you want to control the visibility of controls, put the code in the Format event of the appropriate section. If you want to hide/show controls in the Report's detail section, put the code in the Format event of the detail section.

If Me.SomeField = something Then
    Me.SomeOtherField.Visible = True
Else
    Me.SomeOtherField.Visible = False
End If