Filter on a Form

[RayMilhon]

I have a form in MS Access 2010 That displays contract information. There is a subform that displays the customer information from that contract. I have a command button on the form so that if the user doesn't know the contract number they can find all contracts related to a specific person. The command button displays 2 prompts 1 for the last name and 1 for the first name with a minimum of 3 characters. It then builds a query and gets all of the contractID's related to the matching people. Code Below:

Private Sub cmd_find_customer_Click()
Dim lastname as string, firstname as string, tmpstr as string
Dim strsql as string, rs as new adodb.recordset

lastname = Inputbox("Please Enter the last name (Minimum of 3 characters)","Last Name"
firstname = Inputbox("Please enter the first name ","First Name")    'There is no min on the first name just in case they want all with the same lastname

tmpstr = ""   'Clear the tmpstr
strsql = "Select contractID from tbl_customer_contract_link inner join tbl_customer on tbl_customer_contract_link.customerid = tbl_customer.customerid " & _
    "Where tbl_customer.lastname Like ""*" & lastname & "*"" and tbl_customer.firstname like ""*" & firstname & "*"""


rs.open strsql,currentproject.connection,adopenforwardonly,adlockreadonly

do until rs.eof

    tmpstr = tmpstr & rs!contractID & ","
    rs.movenext
loop

   If tmpstr > 1 Then
       tmpstr = left(tmpstr,len(tmpstr)-1)
       me.filter = "ContractID In (" & tmpstr & ")"
       me.filteron = true
   End if
rs.close
set rs = nothing

End sub

When I click on the button I get the 2 prompts put in the data for the prompts but when the code executes I get 0 records. If I put in a breakpoint at the rs.open command pull the strsql string out and put it into a query I get the expected records. Executing in the code returns 0 records????

Anybody have an idea what I'm missing?

[pbaldy]

Have you stepped through the code after setting the breakpoint, to see if the string is being populated? I would wonder if this:

If tmpstr > 1 Then

should be


If Len(tmpstr) > 0 Then

[RayMilhon]

No the tmpstr is not being populated because the Recordset is not returning any data. Even though the query works when I run it in VBA it doesn't

[pbaldy]

Ah, just noticed, when using an ADO recordset, you need to use % instead of * as the wildcard.

[RayMilhon]

Thanks that worked also realized I forgot the quotes in the tmpstr 99% of the time there will be only 1 contract for a person but just in case need to account for the possibility of more need to change the following lines



When building the tmpstr should be as follows:

tmpstr = tmpstr & rs!contractID & ""","""

Also the
tmpstr = LEFT(tmpstr,Len(tmpstr)-1) should be
tmpstr = LEFT(tmpstr,Len(tmpstr)-2) To account for the extra ,"

the ME.Filter line should be

me.filter = "ContractID In (""" & tmpstr & ")"


Need to change the

[pbaldy]

No problem. I don't use ADO much, and didn't notice the wildcard right off.

[RayMilhon]

I use it all the time but this is the first time I needed a wild card.