Good morning all,
I have a photo of form in question and code for it below! db is to big to upload, sorry!
In any event, I have a frmBusiness with sfrmBusinessContacts
The form Report is an unbound form as a subform.
What I am trying to do is insert an unbound checkbox "chkSingle" on the Report form and when that = true, then it would only show a report for the open record in Business by BusinessID
Something like this: But have tried several methods and cant seem to get it correct
If Forms!frmBusiness.sfrmBusinessContact.Form.chkSing le = True Then
DoCmd.OpenReport strDoc, acViewPreview WHERE BusinessID= Me.BusinessID
DoCmd.OpenReport strDoc, acViewPreview WHERE BusinessID= forms!frmBusiness.BusinessID
Else
DoCmd.OpenReport strDoc, acViewPreview
End If
Code:
Private Sub CmdReport_Click()
Dim varItem As Variant
Dim strDoc As String
If IsNull(Me.LstBusinessReport.Column(0)) Then
MsgBox "You must select a Report from Reprot List!"
Exit Sub
End If
On Error GoTo Err_Handler
With Me.LstBusinessReport
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.LstIndustry.Column(0)) And IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstFunction.Column(0)) Then
DoCmd.OpenReport strDoc, acViewPreview, , "[CategoryID] in(" & getLBX(Me.LstIndustry) & ")"
ElseIf Not IsNull(Me.LstIndustry.Column(0)) And Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstFunction.Column(0)) Then
DoCmd.OpenReport strDoc, acViewPreview, , "[SubCategoryID] in(" & getLBX(Me.LstCategory) & ")"
ElseIf Not IsNull(Me.LstIndustry.Column(0)) And Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstFunction.Column(0)) Then
DoCmd.OpenReport strDoc, acViewPreview, , "[SubSubCategoryID] in(" & getLBX(Me.LstFunction) & ")"
Else
DoCmd.OpenReport strDoc, acViewPreview
End If
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Thank you
Dave