Solved Passing values from form to report (1 Viewer)

ClaraBarton

Registered User.
Local time
Yesterday, 18:04
Joined
Oct 14, 2019
Messages
483
I have 8 fields that are not part of the recordset that I need to pass values from the form to the report. The following code debugs right but the values do not get passed to the report.
Code:
Private Sub cmdPrint_Click()
    On Error GoTo ErrorCode
    Dim frm As Form
    
  Me.Visible = False
    If Me!togMonth = True Then
        DoCmd.OpenReport "rptMonth", acViewPreview
    Else
        DoCmd.OpenReport "rptWeek", _
            View:=acViewPreview
    Set frm = Me!frmCalendarWeek.Form
            
    With Reports("rptWeek")
      .txtDate = Me.[txtDate]
      .NoteSun = frm.[NoteSun]
      .NoteMon = frm.[NoteMon]
      .NoteTues = frm.[NoteTues]
      .NoteWed = frm.[NoteWed]
      .NoteThurs = frm.[NoteThurs]
      .NoteFri = frm.[NoteFri]
      .NoteSat = frm.[NoteSat]
    End With
    
    DoCmd.Close acForm, Me.name

    End If
Exit Sub

ErrorCode:
    If Err = 2501 Then Exit Sub
    MsgBox Err.description

End Sub
I originally tried OpenArgs but it got too cumbersome. Is there some way to make this work?
 

Edgar_

Active member
Local time
Yesterday, 20:04
Joined
Jul 8, 2023
Messages
456
Did you try to do something like this with OpenArgs?
Building the string
Code:
strOpenArgs = _
Me.[NoteSun] & "," & _
Me.[NoteMon] & "," & _
Me.[NoteTues] & "," & _
Me.[NoteWed] & "," & _
Me.[NoteThurs] & "," & _
Me.[NoteFri] & "," & _
Me.[NoteSat]

Passing the built string as OpenArgs
DoCmd.OpenForm "FormName", , , , , , strOpenArgs

Using the OpenArgs
Code:
    With Reports("rptWeek")
      .txtDate = Me.[txtDate]
      .NoteSun = Split(Me.OpenArgs, ",")(0)
      .NoteMon = Split(Me.OpenArgs, ",")(1)
      .NoteTues = Split(Me.OpenArgs, ",")(2)
      .NoteWed = Split(Me.OpenArgs, ",")(3)
      .NoteThurs = Split(Me.OpenArgs, ",")(4)
      .NoteFri = Split(Me.OpenArgs, ",")(5)
      .NoteSat = Split(Me.OpenArgs, ",")(6)
    End With
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 02:04
Joined
Jul 9, 2003
Messages
16,442
As you have discovered using openargs to pass large amounts of data can be complicated.

I discovered a method where the report draws the information in from the Form. I found this method more reliable and easier to set up once you understood how it worked.

See my blog about the process here:-

 

Gasman

Enthusiastic Amateur
Local time
Today, 02:04
Joined
Sep 21, 2011
Messages
14,665
Could also use TempVars?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:04
Joined
May 21, 2018
Messages
8,705
In this case if you do not mind keeping the form Open (visible or hidden) then just hard wire and not pass anything, because this report has to work in conjunction with the form. It is not a generic report that could work in conjunction with other forms nor does it work stand alone. You can hide the form but ensure the report closes the hidden form.

Code:
Private Sub Report_Load()
  Const FormName = "frmWeekCalendar"
  Dim frm As Access.Form
  If CurrentProject.AllForms(FormName).IsLoaded Then
    Set frm = Forms(FormName)
    Me.NoteSun = frm.NoteSun
    Me.NoteMon = frm.NoteMon
 Else
   MsgBox "This report works in conjunction with " & FormName & " and must be loaded."
 End If
 
End Sub
 

Users who are viewing this thread

Top Bottom