Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013

    Unanswered: Report Textbox Data Based Off Multiple Checkbox's

    Hello, and thank you in advance for any help you can provide.

    I am currently using Microsoft Access 97 (our company is slow to upgrade to 2010, but is slowly getting there). I have a database that my department has been using for over 6 years that has all of the suppliers our company uses, with their contact information & regulatory certificate data. I have 5 different types of certificates that are checkboxes that I want to list as text into one textbox. The field names for the certificates are:
    FAR 145?
    EASA 145?
    CAR 573-AMO?
    AS 9100?

    I am trying to come up with a formula that will look at the these 5 fields, and if they are TRUE then enter specific data in a textbox in a Report. For instance, if XYZ Company has a FAR 145, an EASA 145 and an AS 9100, I want the text box to say "FAR 145, EASA, AS 9100". I was able to come up with an IIF statement, but can only get it to work for one checkbox =IIf([FAR 145?]=True,"FAR 145","").

    Is it even possible to do this? If so, any help would be appreciated.


  2. #2
    Join Date
    Feb 2013
    I make several assumptions in this answer; change as appropriate with regard to the names of forms and reports: the form that contains the checkboxes is named "frmFORM", and that there is a button on that form (I call it OpenReport) that opens the report "rptREPORT" where you have the textbox that is to contain "FAR 145, EASA, AS 9100" etc.

    Name each checkbox Check1 to Check5, as the code below requires that pattern. Change as appropriate if you want.

    In frmFORM's VBA code add in:

    Dim outputStr as String

    Private Sub OpenReport_Click()

    Dim intCounter as Integer
    outputStr = ""

    For intCounter = 1 to 5

    If Me!("Check" & intCounter) = True Then

    Select Case intCounter

    Case 1
    outputStr = outputStr + "FAR 145, "

    Case 2
    outputStr = outputStr + "EASA 145, "

    Case 3
    outputStr = outputStr + "CAR 573-AMO, "

    Case 4
    outputStr = outputStr + "AS 9100, "

    Case 5
    outputStr = outputStr + "ISO9000, "

    End Select

    End If


    'Strip last ", "
    outputStr = Left(outputStr, (Len(outputStr) - 2))

    DoCmd.OpenReport "rptREPORT", A_PREVIEW

    End Sub

    In the end report "rptREPORT", add in a textbox with Control Source:


    Note that the form MUST still be open when you open the Report for this to work. Hope that helps!

    Last edited by Rogue_Vector; 02-18-13 at 17:41.

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    Here's a possible solution (air code):
    Dim strCtlName as String
    Dim str As String
    Dim i as Long
    For i = 1 to 5
        Select Case i
            Case 1: strCtlName = "FAR 145?"
            Case 2: strCtlName = "EASA 145?"
            Case 3: strCtlName = "CAR 573-AMO?"
            Case 4: strCtlName = "AS 9100?"
            Case 5: strCtlName = "ISO9000?"
        End Select
        If Me.Controls(strCtlName).Value = True then
            If Len(str)> 0 then  str = str & ", "
            str = str & Left(strCtlName, Len(strCtlName) - 1)
        End If
    Next ctl
    You can then use str as the value of the textbox.

    The code would be simpler in a more recent version of Access:
    Dim strCtlName as String
    Dim str As String
    Dim i as Long
    For i = 1 to 5
        strCtlName = Choose(i, "FAR 145?", "EASA 145?", "CAR 573-AMO?", "AS 9100?", "ISO9000?")
        If Me.Controls(strCtlName).Value = True then
            If Len(str)> 0 then  str = str & ", "
            str = Replace(str, "?","")
        End If
    Next ctl
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts