dimanche 5 février 2023

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 totaling 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



Aucun commentaire:

Enregistrer un commentaire