Microsoft Access: Adding Text box, Combo box and a way where if no entry is put in, it will still run

Question

I have been working on a project as a volunteer crime analyst, and I have run into issues on how to enter in multiple text boxes, a multi-valued combo box and how to make sure that if there are no entries made that those boxes are ignored in favor of those that have values in them. I have figured out how to have multiple multi-select list boxes return data from a data entry table, what I'm asking is for help on how to add in the rest of the components that are on the MS Access form that I have for a prototype database.

Here is my code, would like to have some advice on how and where the code for the text boxes and multi-valued combo box would go

Private Sub Command62_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCriteria3 As String
Dim strCriteria4 As String
Dim strCriteria5 As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiselect")

For Each varItem In Me!District.ItemsSelected
strCriteria = strCriteria & ",'" & Me!District.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything in the Contract field." _
    , vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

For Each varItem In Me!MOMethodofEntry.ItemsSelected
strCriteria1 = strCriteria1 & ",'" & Me!MOMethodofEntry.ItemData(varItem) & 
"'"
Next varItem

If Len(strCriteria1) = 0 Then
MsgBox "You did not select anything in the Name field." _
    , vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)

For Each varItem In Me!MOLocation.ItemsSelected
strCriteria2 = strCriteria2 & ",'" & Me!MOLocation.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria2) = 0 Then
MsgBox "You did not select anything in the Name field." _
    , vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)

For Each varItem In Me!MOPointofEntry.ItemsSelected
strCriteria3 = strCriteria3 & ",'" & Me!MOPointofEntry.ItemData(varItem) & 
"'"
Next varItem

If Len(strCriteria3) = 0 Then
MsgBox "You did not select anything in the Name field." _
    , vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria3 = Right(strCriteria3, Len(strCriteria3) - 1)

For Each varItem In Me!CircumstanceCode.ItemsSelected
strCriteria4 = strCriteria4 & ",'" & Me!CircumstanceCode.ItemData(varItem) & 
"'"
Next varItem

If Len(strCriteria4) = 0 Then
MsgBox "You did not select anything in the Name field." _
    , vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria4 = Right(strCriteria4, Len(strCriteria4) - 1)

For Each varItem In Me!MOWeapon.ItemsSelected
strCriteria5 = strCriteria5 & ",'" & Me!MOWeapon.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria5) = 0 Then
MsgBox "You did not select anything in the Contract field." _
    , vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria5 = Right(strCriteria5, Len(strCriteria5) - 1)

strSQL = "SELECT * FROM tblDataEntry " & _
     "WHERE tblDataEntry.District IN(" & strCriteria & ") AND 
tblDataEntry.MOMethodofEntry IN(" & strCriteria1 & ") AND 
tblDataEntry.MOLocation IN(" & strCriteria2 & ") AND 
tblDataEntry.MOPointofEntry IN (" & strCriteria3 & ") AND 
tblDataEntry.CircumstanceCode IN (" & strCriteria4 & ") AND 
tblDataEntry.MOWeapon IN(" & strCriteria5 & ");"

qdf.SQL = strSQL

DoCmd.OpenQuery "qryMultiselect"
Set db = Nothing
Set qdf = Nothing

End Sub

Also please let me know if I am doing anything wrong. Still a little new to this.

So the code you are showing us is OK, but you just want to know where to put the rest of the code (that you are not sharing)? If the code belongs to the form, put it in the code behind the form! If it belongs to some other form, put it there! If the code needs to be shared, put it in a module! — kismert
This is the code that I have at the moment. This is all for the same form on access. All referencing one table with all the data in it. I have yet to construct the code of the text box, and combo box. One of my concerns is where the "null" function should go for each of the "criteria" items. Should it be put after a certain part of the code and so on. As for the text box and combo box placement, I'm assuming that anywhere would do really so long as it is within the code? Not sure what you meant by "behind the form". Also not sure how to form it. — NMats
This is what I currently have for a text box If Me.txtCaseNumber > "" Then varWhere = varWhere & "[CaseNumber] LIKE """ & Me.txtCaseNumber & "*"" AND " End If not sure if this is right or not and also how the to put in the null code in so that if left blank it won't freak out on me (for lack of a better phrase). — NMats

Vlado Vlado · Accepted Answer · 2019-01-30T18:18:38

I am not sure what do you mean with "where the code for the text boxes and multi-valued combo box would go" but I can help you with writing better code. Put inside of your form this functions:

Private Function GetSelectedItems(combo As ListBox) As String
Dim result As String
Dim n As Integer
    With combo
        For n = .ListCount - 1 To 0 Step -1
            If .Selected(n) Then
                result = result & ",'" & .ItemData(n) & "'"
            End If
        Next n
    End With
GetSelectedItems = Mid(result, 2)
End Function

Public Function IsEmptyOrNull(strValue As Variant) As Boolean
    If Trim(strValue) = vbNullString Or IsNull(strValue) Then
        IsEmptyOrNull = True
    End If
End Function

and than you call the function like this:

strCriteria = GetSelectedItems(Me!District)
strCriteria1 = GetSelectedItems(Me!MOMethodofEntry)
…

After you fill in all your criteria create strFilter string:

If Not IsEmptyOrNull(strCriteria) Then
    strFilter = IIf(Not IsEmptyOrNull(strFilter), strFilter & " AND ", "") & " District IN (" & strCriteria & ")"
End If
If Not IsEmptyOrNull(strCriteria1) Then
    strFilter = IIf(Not IsEmptyOrNull(strFilter), strFilter & " AND ", "") & " MOMethodofEntry IN (" & strCriteria1 & ")"
End If
…

Something similar do with your text boxes:

If Not IsEmptyOrNull(Me.txtCaseNumber) Then
    strFilter = IIf(Not IsEmptyOrNull(strFilter), strFilter & " AND ", "") & " CaseNumber= '" & Me.txtCaseNumber & "'"
End If

and after you add all your fields create your strSQL string:

strSQL = "SELECT * FROM tblDataEntry WHERE " & strFilter

Microsoft Access: Adding Text box, Combo box and a way where if no entry is put in, it will still run

1 Answers