Run report from button on form

[dc.gypsy]

I'm a beginner at VB and programming. I have created a button on my form that runs a report whose underlying query prompts the user to enter a case number to print a single record. On button click, I would like the query to use the current record's case number without requiring the user to enter it. I also still need to run the report without opening the form, so I don't want to modify the query unless the user can still set the criteria for the case number when running the report by itself. Any suggestions would be welcome.

 

[john471]

use Report's OnOpen event

dc.gypsy,

This may work for you:

Change the query to remove the parameter / criteria, such that it returns the required data for all cases (presuming you only use this query for the report).

Then, in the Report's OnOpen event, amend this code to suit your form and field names, and place it in there.

This code will determine if the form is open, if it is then the report will filter itself on the current record of the form. If the form is not open, it will prompt the user for a case number, and filter itself on that. (also allows user to enter 'ALL' and then not filter itself).

Private Sub Report_Open(Cancel As Integer)
    On Error Resume Next
    Dim lngCaseID As Long
    Dim szInput As String
    If bIsLoaded("frmReportCaseSelector") Then
        Me.Filter = "[CaseID] = " & Forms!frmReportCaseSelector.CaseID
        Me.FilterOn = True
    Else
        szInput = InputBox("Enter The Case ID" & vbCrLf & "Or enter 'ALL' for all cases.", "Case Number for Report", "")
        If UCase$(szInput) <> "ALL" Then
            lngCaseID = CLng(szInput)
            If Err.Number <> 0 Then
                Cancel = True
                MsgBox "Sorry.  The data you entered was invalid.", vbOKOnly + vbExclamation, "Non Numeric Case Number"
            End If
            Me.Filter = "[CaseID] = " & lngCaseID
            Me.FilterOn = True
        Else
            Me.Filter = vbNullString
            Me.FilterOn = False
        End If
    End If
End Sub

I added some functionality to allow the same report to print all cases... should be fairly self-explanatory, if you wanted to amend/remove it.

Create a new VBA Code Module, and paste in the following function (plagerised entirely from the AC97 Solutions.mdb : modified to suit hungarian naming convention)

 Function bIsLoaded(szFrmName As String) As Boolean
    
    '  Determines if a form is loaded.
    
    Const conFormDesign = 0
    Dim n As Integer
    
    bIsLoaded = False
    For n = 0 To Forms.Count - 1
        If Forms(n).FormName = szFrmName Then
            If Forms(n).CurrentView <> conFormDesign Then
                bIsLoaded = True
                Exit Function  ' Quit function once form has been found.
            End If
        End If
    Next
End Function

Attached is my prototype / working model (AC97).

HTH

Regards

John