Combo Box Filtering 2

[Ray1127]

I have a combo box in a Form in Access 2000. The combo box Record source is a query that grabs data from 3 Linked SQL Server tables. There are 4 data elements in the combo box: ID, lastname,firstname,DOB. The ID is saved to the Table the Form is based on. This works well as long as the user knows the ID Number. I also have a command button that the user can click on to search for a specific member if the ID is unknown. User is prompted for the lastname, first name, and DOB. The Code for the Subroutine Follows:

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
Me.MemberID = rs!sbsb_id
End If
rs.Close
Set rs = Nothing

End Sub

I have 2 issues First is that the SQL Works perfectly if I open a new query click on SQL and copy the strsql. However when it runs in the sub procedure it returns 0 records. Secondly a recent change in the Where clause specifically last name and first name so that if partial name is known (rare but possible) The SQL Could return more than 1 record. I would like the combo box to filter to only those records that match. Not sure how to do that I know the Form can be filtered but I only want to filter the combo box also if the record is changed the filter should go away. Any ideas will be appreciated.

 

[lameid]

Matching is easy... Use an equal sign (=) instead of the Like Operator and a wildcard *

Not sure why at runtime you have an issue...

directly above or below the RS.OPEN line, add...

debug.print strSQL

After the code runs, press CTRL + G to see if the select statement looks good... Like you said runs...

Just to double check... You are using an MDB with linked tables correct?

 

[PHV]

As you use ADO the wildcard is % (not *).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ray1127]

Thanks PHV that was the issue however requirements were changed and the like is no longer needed.

 

[lameid]

I always use DAO to connect to Jet so I have not had that come up.
I use ADO for direct SQL connections though.

ADO Connection to Jet means a shift in Wild Cards... Interesting.... And no complaints about the date delimeters being #? - That is plain weird to me.

Take another star for that one PHV.