Opening same Report with criteria from two forms

[d9pierce1]

I would think there would be a simple way to determine what form was used to open the report? Noot that I know anything about how to!


The code runs the first IF but not the Else IF and not the Else on Account Form.
I hate to say it but it may just be simpler to duplicate the report so they are separate reports

[Welshgasman]

Yes there is, you pass in the name of the form, or something to identify which form opened the report.

[Micron]

I don't get what's going on here. If you want to know which form opened a report, what does that have to do with all that code? If that code matters, did you verify the values you're passing as dates? Consider cutting to the chase by posting the db.

[d9pierce1]

Thank you W, Never have done this before and both forms being sub forms on different forms I dont have a clue to be honest.
Does anyone have an example of this. I will assume on the DoCmd Open Report Line under openargs I will need ? form/subform name?
And on Report I will need some thing to determine what form on enter?

[d9pierce1]

Thanks micron,
I would love to post the db but it is 39MB and cant shrink it enough to get on here.
However, I did post the code on form for the Report to open, and the code I had on the report.

[Micron]

You could copy only what's needed to a new db? Unfortunately, your code doesn't help me at all when I can't know what the variables/references equate to. Besides, I don't see how it's even relevant to using open args. AFAIC, using open args avoids the possibility that the subform is visible to the report code since subform are not part of the open forms collection.

[Micron]

this presented a message box with Test is the word

DoCmd.OpenReport "attendance log", acViewPreview, , , , "test"

Private Sub Report_Open(Cancel As Integer)
If Me.OpenArgs = "test" Then MsgBox "Test is the word"
End Sub

[d9pierce1]

Micron,
Here are the two forms. One is subform on main menu, one is a subform on Account

The code listed below is to Open the report with the codes criteria!
Both forms are the same except for the Account form I have a check box to open an individual record.
Both codes are the same except for the form/subform
If I have no date in forms and is #2 in optiongroup, then I dont want the txt boxes to be visible opening from either form.
If I open form from Main Menu with dates and Option #1, then I want one of the two txt boxes to be visible and one not visible
If I open from Account with dates and Option #1, then I want one of the two txt boxes to be visible and one not visible

I beleive the criteria is all there except for what form it is in the report~ Hope this makes sense


These are the two txt boxes on report form
BetwenTransDateMM
="For Transactions Between: " & [Forms]![frmMainMenu].[sfrmMainMenu].[Form]![BeginningTransDate] & " and " & [Forms]![frmMainMenu].[sfrmMainMenu].[Form]![EndingTransDate]
BetwenTransDate
="For Transactions Between: " & [Forms]![frmAccount].[sfrmAccount].[Form]![BeginningTransDate] & " and " & [Forms]![frmAccount].[sfrmAccount].[Form]![EndingTransDate]

This is code from MainMenu, Error code removed

Private Sub CmdReport_Click()


Dim varItem As Variant
Dim strDoc As String


If IsNull(Me.LstCategoryReport.Column(0)) Then
    MsgBox "You must select a Report from Reprot List!"
    Exit Sub
End If


   With Me.LstCategoryReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
    Next
    End With


If Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstCategorySub.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[CategoryID] in(" & getLBX(Me.LstCategory) & ")"
ElseIf Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstCategorySub.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ")"
Else
    If Me.grpFilterOptions = 2 Then
        DoCmd.OpenReport strDoc, acViewPreview
    Else
        DoCmd.OpenReport strDoc, acViewPreview, , Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, acNormal
    End If


End If
End Sub

This is the code from Account, Error code removed

Private Sub CmdReport_Click()


Dim varItem As Variant
Dim strDoc As String


If IsNull(Me.LstCategoryReport.Column(0)) Then
    MsgBox "You must select a Report from Reprot List!"
    Exit Sub
End If


   With Me.LstCategoryReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
    Next
    End With


If Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstCategorySub.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[CategoryID] in(" & getLBX(Me.LstCategory) & ")"
ElseIf Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstCategorySub.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ")"
Else
    If Me.grpFilterOptions = 2 Then
        DoCmd.OpenReport strDoc, acViewPreview
    Else
        DoCmd.OpenReport strDoc, acViewPreview, , Forms![frmAccount].[sfrmAccount].Form.txtReportFilter, acNormal
    End If


End If


    If Forms!frmAccount.sfrmAccount.Form.chkSingle = True Then
        DoCmd.OpenReport strDoc, acViewPreview, , "AccountID=" & Forms!frmAccount!AccountID
    Else
        DoCmd.OpenReport strDoc, acViewPreview
    End If
End Sub

[d9pierce1]

Micron,
The code for DoCmd Open Report has all the criteria to pass to the report, I just need to get the report to figure out what form it came from
And what txtbox to be visible depending on what form. The txtbox gives me the Begining Date and Ending Date on the report if criteria is selected.
Now this being a form/subform on both instances makes this more difficult i assume.

[d9pierce1]

this presented a message box with Test is the word

DoCmd.OpenReport "attendance log", acViewPreview, , , , "test"

Private Sub Report_Open(Cancel As Integer)
If Me.OpenArgs = "test" Then MsgBox "Test is the word"
End Sub

This is what I have currently, how would this go?
I put in red my notes....

    If Me.grpFilterOptions = 2 Then
        DoCmd.OpenReport strDoc, acViewPreview  Note: This is without Dates
    Else
        DoCmd.OpenReport strDoc, acViewPreview, , Forms![frmAccount].[sfrmAccount].Form.txtReportFilter, acNormal  Note:This is with Dates
    End If

[Micron]

Just as I showed you. Replace "test" with Me.Name - no quotes. That will pass the name of the form to the report open args. You check the form name in the open event. If your code involves trying to get any field values, you can't in the open event. IIRC, once a form/report is open, the OpenArgs are available to any event in that form/report so you could put your code that looks for the form name into the Load event if need be. Unless your questions relate somehow to how a form looks when open, don't waste your time posting pics of it. They are of no help when trying to resolve code issues.

[d9pierce1]

Hi Micron,
First off thank you!
I am doing my best here to figure this out.
Obvious this is not correct or I wouldnt be posting it, but I do not know what I am doing wrong here?
Please see code below.

' This is my form code for open report!


Private Sub CmdReport_Click()


Dim varItem As Variant
Dim strDoc As String


If IsNull(Me.LstCategoryReport.Column(0)) Then
    MsgBox "You must select a Report from Reprot List!"
    Exit Sub
End If


On Error GoTo Err_Handler


   With Me.LstCategoryReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
    Next
    End With
If Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstCategorySub.Column(0)) And (Me.grpFilterOptions = 2) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[CategoryID] in(" & getLBX(Me.LstCategory) & ")"
ElseIf Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstCategorySub.Column(0)) And (Me.grpFilterOptions = 1) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[CategoryID] in(" & getLBX(Me.LstCategory) & ") AND" & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, , Forms![frmMainMenu].[sfrmMainMenu].Form.frmCategoryReportMM
ElseIf Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstCategorySub.Column(0)) And (Me.grpFilterOptions = 2) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ")"
ElseIf Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstCategorySub.Column(0)) And (Me.grpFilterOptions = 1) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ") AND" & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, , Forms![frmMainMenu].[sfrmMainMenu].Form.frmCategoryReportMM
Else
    If Me.grpFilterOptions = 2 Then
        DoCmd.OpenReport strDoc, acViewPreview
    Else
        DoCmd.OpenReport strDoc, acViewPreview, , Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, acNormal, Forms![frmMainMenu].[sfrmMainMenu].Form.frmCategoryReportMM
    End If
End If
Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdReport_Click"
    End If
    Resume Exit_Handler
 
End Sub

'This is my code for On Open In Report


Private Sub Report_Open(Cancel As Integer)


If Me.OpenArgs = Forms!frmMainMenu.sfrmMainMenu.Form.frmCategoryReportMM Then
    If Forms!frmMainMenu.sfrmMainMenu.Form.grpFilterOptions = 1 Then
        Me.BetwenTransDateMM.Visible = True
        Me.BetwenTransDate.Visible = False
    End If
ElseIf Me.OpenArgs = Forms![frmAccount].[sfrmAccount].Form.frmCategoryReport Then
    If Forms!frmAccount.sfrmAccount.Form.grpFilterOptions = 1 Then
        Me.BetwenTransDate.Visible = True
        Me.BetwenTransDateMM.Visible = False
    End If
Else
    Me.BetwenTransDate.Visible = False
    Me.BetwenTransDateMM.Visible = False
End If
    
End Sub

[d9pierce1]

I got it figured out finally
Changed Forms![frmMainMenu].[sfrmMainMenu].Form.frmCategoryReportMM to "frmCategoryReportMM " in form code
Changed If Me.OpenArgs = Forms!frmMainMenu.sfrmMainMenu.Form.frmCategoryRep ortMM to "frmCategoryReportMM " in Report Open Code

And the best part is I learned something new

Thank you so much!
Dave

[Micron]

frmCategoryReportMM

Would that not be the same as Me.Name as I previously suggested?