Results 1 to 7 of 7
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Open Report to single record if checkbox = true?

    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



    Click image for larger version. 

Name:	BusinessFrm.jpg 
Views:	11 
Size:	267.1 KB 
ID:	49968

    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

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You need to concatenate the business ID
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi all,
    I have added this however it still shows all records. I put TxtBusinessID on form by =[Forms]![frmBusiness]![BusinessID]
    But it still shows all businesses in report
    Do I need some type of filter? I dont know!

    Code:
        If Forms!frmBusiness.sfrmBusinessContact.Form.chkSingle = True Then
            DoCmd.OpenReport strDoc, acViewPreview, , (Forms!frmBusiness.sfrmBusinessContact.Form!TxtBusinessID)
        Else
            DoCmd.OpenReport strDoc, acViewPreview
    End If
    Thanks

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I would expect it would, as it is just nonsense.

    Concatenate the businessID to your WHERE Clause.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Welshgasman,
    Not real sure what you or how you would concat this"
    Code:
        If Forms!frmBusiness.sfrmBusinessContact.Form.chkSingle = True Then
            'MsgBox ("Hello")
            DoCmd.OpenReport strDoc, acViewPreview, , (Forms!frmBusiness![BusinessID])
        Else
            DoCmd.OpenReport strDoc, acViewPreview
    End If

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by d9pierce1 View Post
    Hi Welshgasman,
    Not real sure what you or how you would concat this"
    Code:
        If Forms!frmBusiness.sfrmBusinessContact.Form.chkSingle = True Then
            'MsgBox ("Hello")
            DoCmd.OpenReport strDoc, acViewPreview, , (Forms!frmBusiness![BusinessID])
        Else
            DoCmd.OpenReport strDoc, acViewPreview
    End If
    You would not, as I said it is just nonsense.

    Code:
    tt=4
    docmd.OpenReport "rptTestTransactions",acViewPreview,,"ID=" & tt
    so yours would be
    "BusinessID=" & Me.BusinessID

    if ID is text, then surround it with single quotes (')
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you so much!
    Appreciate it!!!!!

    Dave

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-10-2022, 10:42 AM
  2. Open a Single record in a Report
    By Simonhtc4 in forum Reports
    Replies: 5
    Last Post: 05-25-2017, 11:22 AM
  3. Replies: 9
    Last Post: 10-05-2016, 05:51 PM
  4. Replies: 1
    Last Post: 03-27-2016, 10:29 PM
  5. Replies: 7
    Last Post: 05-30-2013, 04:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums