In VB code, I build a SQL statment. I then set the RowSource of a Combo Box to that SQL statement. My code doesn't seem to be adding the final clause to the WHERE statment:
AND [App Info].[App Type] <> 'Canceled/Terminated'
You see, I thought I'd put in an unbound checkbox on the form and have the code look at it to see whether or not to filter out canceled people. Depending on whether or not the checkbox is checked, the MsgBox's that I put in are correctly telling me True or False, but the RowSource of the Combo Box (which the SQL statement resets) still shows people that have been 'Canceled/Terminated'. I've tried copy/pasting that term, just to make sure it's spelled correctly, still doesn't seem to work. The ShowCanTer checkbox seems to do nothing.
AND [App Info].[App Type] <> 'Canceled/Terminated'
You see, I thought I'd put in an unbound checkbox on the form and have the code look at it to see whether or not to filter out canceled people. Depending on whether or not the checkbox is checked, the MsgBox's that I put in are correctly telling me True or False, but the RowSource of the Combo Box (which the SQL statement resets) still shows people that have been 'Canceled/Terminated'. I've tried copy/pasting that term, just to make sure it's spelled correctly, still doesn't seem to work. The ShowCanTer checkbox seems to do nothing.
Code:
Private Sub GoGoNamesComboBox(WhichName As String) 'Nod to Inspector Gadget
'MsgBox ("GoGoNamesComboBox begins")
'Create the string to hold the SQL statment
Dim SqlString As String
'Add a SELECT to the SQL statment
SqlString = "SELECT [App Info].[Soc Sec #] as [Soc Sec], [Last Name] & ', ' & [First Name] & ' ' & [MA] AS Name FROM [App Info] "
'Selectively add a WHERE to the statement
Select Case WhichName
'Case "All" We just ignore this case, as it has no WHERE statement
Case "AB"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'A%' or [App Info].[Last Name] aLike 'B%'"
Case "CD"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'C%' or [App Info].[Last Name] aLike 'D%'"
Case "EFG"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'E%' or [App Info].[Last Name] aLike 'F%' or [App Info].[Last Name] aLike 'G%'"
Case "HIJ"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'H%' or [App Info].[Last Name] aLike 'I%' or [App Info].[Last Name] aLike 'J%'"
Case "KL"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'K%' or [App Info].[Last Name] aLike 'L%'"
Case "M"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'M%'"
Case "NOPQ"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'N%' or [App Info].[Last Name] aLike 'O%' or [App Info].[Last Name] aLike 'P%' or [App Info].[Last Name] aLike 'Q%'"
Case "RS"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'R%' or [App Info].[Last Name] aLike 'S%'"
Case "TUVWXYZ"
SqlString = SqlString & "WHERE [App Info].[Last Name] aLike 'T%' or [App Info].[Last Name] aLike 'U%' or [App Info].[Last Name] aLike 'V%' or [App Info].[Last Name] aLike 'W%' or [App Info].[Last Name] aLike 'X%' or [App Info].[Last Name] aLike 'Y%' or [App Info].[Last Name] aLike 'Z%'"
End Select
'MsgBox (SqlString)
'Again, selectively add a WHERE to the statement
'The MsgBox's are for error checking
If Me.ShowCanTer = False Then 'ShowCanTer is a checkbox on the form
'MsgBox ("ShowCanTer is false")
SqlString = SqlString & " AND [App Info].[App Type] <> 'Canceled/Terminated'"
'MsgBox (SqlString)
Else
'MsgBox ("ShowCanTer is true")
End If
'Ordo the statement
SqlString = SqlString & " ORDER BY REPLACE(Nz([App Info].[Last Name]),' ',''), REPLACE(Nz([App Info].[First Name]),' ',''), REPLACE(Nz([App Info].MA),' ','');"
'MsgBox (SqlString)
'Set it all up nicely and finish it off
Me.Names_Combo_Box.RowSource = SqlString
Me.Names_Combo_Box.SetFocus
Me.Names_Combo_Box.Requery
SetSlider 'Get the vertical Slider on the side of the Combo Box right
Me.Names_Combo_Box.Dropdown
'MsgBox ("GoGoNamesComboBox ends")
End Sub
Last edited: