I think I must become a farmer!!! ---- ADO!!

Hi all,

I'm REALY battling... can someone PLEASE help!!

I think I dont know what I'm doing!!??

I have a form where the user can either add a new record or search for an existing one.

general declarations
Public cnConnect As Connection
Public rsRecord As Recordset
Public adoErr As Error

'in the formload I open my connection and create a default recordset

Private Sub Form_Load()

Set cnConnect = New Connection
Set rsRecord = New Recordset

'On Error GoTo ConnectError
With cnConnect
.Provider = "microsoft.jet.OLEDB.4.0"
.ConnectionString = "user id=admin;password=; " & _
"data source=c:\settadminsystem\Sett.mdb; "
.Open
End With

' I think my problem lies here - I read somewhere that you MUST have a default recordset

rsRecord.Open "select * from client", cnConnect, adOpenDynamic, adLockOptimistic

end sub


'All I want to do here is search for all record which match the surname which the user typed in and display it on a listbox where the user can dbl-click to select the right one
What happens here is that ALL records in the client table is displayed!! Because of that first "default" select statement I did in form_load!! How can I do this.
Do I bind my fields in the right place or do you do this ONLY ONCE!!! Do you have to open and close a recordset each time you want to do something different with it??

Private Sub cmdQteMainBrowse_Click()
Dim strFind As String
Dim number As Integer


strFind = "surname like '" & txtClientSurname & "*'"
rsRecord.Find strFind

'bind all txtboxes
bind_fields

While Not rsRecord.EOF
Form2.lstBrowseResults.AddItem rsRecord.Fields("surname")
rsRecord.MoveNext
Wend

'this is the listbox which displays ALL records and NOT ONLY the ones I searched for in the rsRecord.find

Form2.Show

End Sub



PLEASE HELP - I'm going nuts!!

You could always close the recordset and open it again in the Sub cmdQteMainBrowse_click.

strFind = "select * from client where surname like '" & trim(txtClientSurname) & "*'"

rsRecord.Open strFind, cnConnect, adOpenDynamic, adLockOptimistic

Thanks for the reply -

I used the code, but it still does not work!!

When I hardcode a surname into strFind I do however get results.

Something else funny is happening....

I have 3 records in my client table - 2 with the surname "Jones" and 1 with the surname "White" - when I hardcode "JONES" into strfind, I find BOTH records (which is correct), but when I try the same with "WHITE", I get none!!

This is my code - when it comes to the movenext in the
while loop - I get an error EITHER BOF or EOF ....... and my program terminates!!


Private Sub cmdQteMainBrowse_Click()
Dim strFind As String
Dim number As Integer


strFind = "select * from client where surname = 'WHITE'"

Set rsRecord = New Recordset

rsRecord.Open strFind, cnConnect, adOpenDynamic, adLockOptimistic



bind_fields

While Not rsRecord.EOF
Form2.lstBrowseResults.AddItem rsRecord.Fields("surname")
rsRecord.MoveNext
Wend
'This is the listbox!!
Form2.Show

End Sub

put a breakpoint on the line:

strFind = "select * from client where surname like '" & trim(txtClientSurname) & "*'"

When your code stops at the breakpoint, press f8 then type the following in the immediate window:

?strFind

Copy the results and post them here.

JHausmann,

?STRFIND
select * from client where surname like 'WHITE*'

Looks fine to me!!??

I tested this AGAIN...
when I change "LIKE" to "=" then it works 100%!!

I'm accessing a MS ACCESS database.

Try changing the "*" to "%" in your like command and see if it doesn't work better...

"%" works like a charm!!

Thanks JHausmann!!