Solved Passing values from form to report

[ClaraBarton]

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.

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_]

Did you try to do something like this with OpenArgs?
Building the string

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

    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]

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:-

Pass Info In to a Report – Nifty Access

www.niftyaccess.com

 

[Gasman]

Could also use TempVars?

 

[MajP]

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.

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

 

[ClaraBarton]

Thank you. Thank you. Thank you!