Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    creating a query with a check box form (2000/02/03)

    I have a table, tblSubContarctors, that lists all the subs I deal with. In this table there is a field that consists of a list box, that containd the sub type(ie: fencing, lighting, bridge, etc.). I am wanting to create a form that has a check box for each type of sub, so that depending on what i need for a particular job, I can print out only the ones needed for this job. I am not sure how to acconplish this.
    Thanks in advance
    Jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: creating a query with a check box form (2000/02/03)

    Is this subtype field a text field (containing for example "fencing"), or do you have numeric codes for the subtypes (for example 1 for "fencing", 2 for "lighting" etc.)?

    Do I understand correctly that you want to create an unbound form on which the user can select one or more subtypes, then click a command button that opens a report displaying the subcontractors corresponding to the selected subtypes?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: creating a query with a check box form (2000/02/03)

    Hi Hans-
    The subtype field is text and yes you are correct in your assumption.
    Jackal

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: creating a query with a check box form (2000/02/03)

    Let's say that you already have a report rptSubcontractors that displays a list of all subcontractors.
    The text field that contains the subtype is named SubType.
    Your unbound form has a command button cmdReport and check boxes chkFencing, chkLighting, etc. (Note: if you have many subtypes, a multi-select list box would be more efficient than a large number of check boxes)
    We are going to write VBA code that opens the report when the command button is clicked, and filters the records that will be displayed.
    <UL><LI>With the form open in design view, select the command button.
    <LI>Activate the Event tab of the Properties window.
    <LI>In the On Click event, select [Event Procedure] from the dropdown list.
    <LI>Click the builder button, i.e. the three dots ... to the right of the dropdown arrow.
    <LI>The Visual Basic Editor will be activated. Complete the event procedure to look as follows (you can copy the code here and paste it into the module, then adapt the names where necessary.)

    Private Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    If Me.chkFencing = True Then
    strWhere = strWhere & ", " & Chr(34) & "Fencing" & Chr(34)
    End If

    If Me.chkLighting = True Then
    strWhere = strWhere & ", " & Chr(34) & "Lighting" & Chr(34)
    End If

    ' Repeat for each of the other check boxes

    If strWhere = "" Then
    MsgBox "Please select at least one subtype", vbInformation
    Exit Sub
    End If

    strWhere = "SubType In (" & Mid(strWhere, 3) & ")"
    DoCmd.OpenReport ReportName:="rptSubcontractors", View:=acViewPreview, WhereCondition:=strWhere
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    <LI>Switch back to Access (using the task bar, or Alt+Tab, or Alt+F11)
    <LI>Close and save the form.[/list]Now you can test the form.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: creating a query with a check box form (2000/02/03)

    Thanks for the help.
    Jackal

Posting Permissions

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