CODEWITHSUNDEEP
vbams-accesscheckboxcontinuous-forms
Grant Grencer
Grant Grencer

Reputation: 11

Access VBA for checkboxes

I'm currently working on a project that gave me a lot of raw data I am trying to filter in a continuous form. I have one field that has a possibility of five letters (GRUBW) with each multiple letters per entry. The goal is to filter the form with a checkbox for each letter. Am I missing something or going about this the wrong way?

Below is the coding I'm attempting to use and when trying to debug I keep getting an error of an undefined variable. As you can see in the coding, I have a lot of ways I want to filter. I have a feeling that I'm missing some double quotes somewhere or my understanding of the way checkboxes are to be coded is totally wrong. I've only been working with VBA for a few months, so I may be out of my depth here. Thanks.

Option Compare Database
Option Explicit
    Private Sub CBTN_CLICK()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acCheckBox
ctl.Value = Null
End Select
Next
Me.FilterOn = False
    End Sub
    Private Sub requeryform()
Dim strwhere As String
Dim lnglen As Long

If Not IsNull([Forms]![sbx]!CTB) Then
    strwhere = strwhere & "([CARD] LIKE ""*" & [Forms]![sbx]!CTB & "*"") AND "
End If
If Not IsNull([Forms]![sbx]!OTB) Then
    strwhere = strwhere & "([ORACLE] LIKE ""*" & [Forms]![sbx]!OTB & "*"") AND "
End If
If Not IsNull([Forms]![sbx]!ARTB) Then
    strwhere = strwhere & "([ORACLE] LIKE ""*" & [Forms]![sbx]!OTB & "*"") AND "
End If
If Not IsNull([Forms]![sbx]!TTB) Then
    strwhere = strwhere & "([ctype] LIKE ""*" & [Forms]![sbx]!TTB & "*"") AND "
End If
If Not IsNull([Forms]![sbx]!CTTB) Then
    strwhere = strwhere & "([ORACLE] LIKE ""*" & [Forms]![sbx]!OTB & "*"") AND "
End If
If Me.RC = True Then
    strwhere = strwhere & "([color] LIKE ""*" & R & "*"") AND "
End If
If Me.UC = True Then
    strwhere = strwhere & "([color] LIKE ""*" & u & "*"") AND "
End If
If Me.BC = True Then
    strwhere = strwhere & "([color] LIKE ""*" & b & "*"") AND "
End If
If Me.GC = True Then
    strwhere = strwhere & "([color] LIKE ""*" & g & "*"") AND "
End If
If Me.WC = True Then
    strwhere = strwhere & "([color] LIKE ""*" & w & "*"") AND "
End If
lng = Len(strwhere) - 5

If lnglen <= 0 Then
Else
strwhere = Left$(strwhere, lnglen)
Debug.Print strwhere
Me.Filter = strwhere
Me.FilterOn = True
End If
End Sub
Private Sub STBN_CLICK()
requeryform
End Sub

Upvotes: 1

Views: 87

Answers (1)

June7
June7

Reputation: 21379

Undefined variable error is due to not enclosing literal text within quote marks and using lng instead of lnglen. Consider revised code:

Dim strGRUBW As String

If Not IsNull([Forms]![sbx]!CTB) Then
    strwhere = strwhere & "([CARD] LIKE ""*" & [Forms]![sbx]!CTB & "*"") AND "
End If
If Not IsNull([Forms]![sbx]!OTB & [Forms]![sbx]!CTTB & [Forms]![sbx]!ARTB) Then
    strwhere = strwhere & "([ORACLE] LIKE ""*" & [Forms]![sbx]!OTB & "*"") AND "
End If
If Not IsNull([Forms]![sbx]!TTB) Then
    strwhere = strwhere & "([ctype] LIKE ""*" & [Forms]![sbx]!TTB & "*"") AND "
End If
With Me
strGRUBW = IIf(.RC, "r,", "") & IIf(.UC, "u,", "") & IIf(.BC, "b,", "") & IIf(.GC, "g,", "") & IIf(.WC, "w,", "")
End With
If strGRUBW <> "" Then strwhere = strwhere & "[color] LIKE '*[" & strGRUBW & "*]' AND "
lnglen = Len(strwhere) - 5

Upvotes: 0

Related Questions