Private Sub cmdSearchmbr_Click()
Dim rs As New ADODB.Recordset
Dim strSQL As String, strLName As String, strFName As String, strDOB As String
Dim strSBSB_Id As String
strLName = InputBox("Please Enter the Members Last Name." & vbCrLf & "If no last name is entered this operation is canceled.", "Last Name")
If strLName = "" Then Exit Sub
strFName = InputBox("Please Enter the Members First Name" & vbCrLf & "If No First name is entered this operation is canceled.", "First Name")
If strFName = "" Then Exit Sub
strDOB = InputBox("Please Enter the Members DOB", "DOB")
strSQL = "SELECT dbo_CMCV_SBSB_BASE.SBSB_ID " & _
"FROM (dbo_CMCV_MEME_BASE INNER JOIN dbo_CMCV_SBSB_BASE ON " & _
"dbo_CMCV_MEME_BASE.SBSB_CK = dbo_CMCV_SBSB_BASE.SBSB_CK) " & _
"INNER JOIN dbo_CMCV_MEPE_BASE ON " & _
"dbo_CMCV_MEME_BASE.MEME_CK = dbo_CMCV_MEPE_BASE.MEME_CK " & _
"WHERE (((dbo_CMCV_MEPE_BASE.MEPE_EFF_DT) <= Now()) And " & _
"((dbo_CMCV_MEPE_BASE.MEPE_TERM_DT) >= Now()) And " & _
"((dbo_CMCV_MEPE_BASE.MEPE_ELIG_IND) = ""Y"")) And " & _
"Trim(UCase([MEME_LAST_NAME])) Like """ & UCase(strLName) & "*""" & _
" AND Trim(UCase([MEME_FIRST_NAME]))Like """ & UCase(strFName) & "*"""
If IsDate(strDOB) Then
strSQL = strSQL & " AND dbo_CMCV_MEME_BASE.MEME_BIRTH_DT = #" & _
strDOB & "# "
End If
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
Do Until rs.EOF
strfilter = rs!sbsb_id & ","
rs.MoveNext
Loop
strfilter = Left(strfilter, Len(strfilter) - 1)
'Me.cboMemberID.
End If
rs.Close
Set rs = Nothing '
End Sub