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!!