Textbox search 1

[instructorTek]

Hi all I have a database created in Access 2002 with a table called tblStudent with the fields, FirstName, LastName , Address and Class. I'm creating a search form so that the user can enter the student's LastName and the database will pull up the student's info.
I tried to do this but although there may be several students with the same last name only the first matching record in the table is displayed. The form comes up with only one record as the result. Can you help with this?
Code:

Option Compare Database
Option Explicit
Dim dbCurr As ADODB.Connection
Dim rstUser As ADODB.Recordset
Dim strConnect As String

Private Sub cmdFind_Click()
'Set and open the recordsets for users and user groups
Set rstUser = New ADODB.Recordset
Set dbCurr = New ADODB.Connection

'Connection to the database
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name & ";Persist Security Info=False"
'Open the DB connection
dbCurr.Open strConnect

'Open the recordset
rstUser.Open "SELECT * FROM tblStudent WHERE LastName = '" & txtSearch.Value & "' ", dbCurr, adOpenDynamic, , -1

'assign values to textboxes as a result of textbox choice
If Not rstUser.EOF Then
If txtSearch.Value = rstUser.Fields("LastName").Value Then
txtFirstName.Value = rstUser.Fields("FirstName").Value
txtLastName.Value = rstUser.Fields("LastName").Value
txtClass.Value = rstUser.Fields("Class").Value
txtAddress.Value = rstUser.Fields("Address").Value
End If

End If
If rstUser.State = adStateOpen Then rstUser.Close
End Sub

 

[Remou]

You may wish to look at:
How to bind Microsoft Access forms to ADO recordsets

http://support.microsoft.com/kb/281998/EN-US/

 

[instructorTek]

Hi Remou I checked out the article but it was of no help. I have no problem connecting to the database, the only problem is that only one record shows up in my search although multiple records in the table may have the same last name for e.g.

 

[Remou]

What I am suggesting is that you use the recordset returned as the recordset for the form. The code as shown above will only show one record. To illustrate this, have you tried:

If Not rstUser.EOF Then
    Do While Not rsUser.EOF
        strMsg = strMsg rstUser.Fields("LastName").Value & ", " & rstUser.Fields("FirstName").Value & vbCrLf
    rsUser.MoveNext
   Loop
   msgbox strMsg
End If

The above is typed, not tested.

 

[JoeAtWork]

How are you determining there is only one record in the recordset? I don't see anything in the code where you are checking how many records there are. What I see:

1. Open the recordset
2. If not at EOF, fill textboxes with data from the current record (which would be the first one)
3. Then you close the recordset

You never call a rsUser.MoveNext to try to access any other records. Nor do you check rsUser.RecordCount (which may not be accurate until after calling rsUser.MoveLast).

By the way, your connection string suggests the tables are in the same database as the code. If that is true, you don't need to make another connection, you could just use CurrentProject.Connection.

But if your table is local, your easiest option might be just to bind your form to the table and use filters. For example, put a button labelled "Filter" next to your search textbox, and add code similar to the following:

    If Me.txtFilter.Value = "" Then
        Me.FilterOn = False
    Else
        Me.Filter = "Code = """ & Me.txtFilter.Value & """"
        Me.FilterOn = True
    End If

 

[instructorTek]

Hi Remou I tried the code you gave but it gives a syntax error on the string assignment line. I think this code will still only show one record because each time it loops the string is over-written with the next record without being displayed

Hi JoeAtWork I subsequently tried to move to the next record with rsUser.MoveNext but was still unable to bind other records to my result textboxes. Why is this so? I'm willing to try the filter but could not understand the coding.

 

[Remou]

It is just missing an &:

If Not rstUser.EOF Then
    Do While Not rsUser.EOF
        strMsg = strMsg [red]&[/red] rstUser.Fields("LastName").Value & ", " & rstUser.Fields("FirstName").Value & vbCrLf
    rsUser.MoveNext
   Loop
   msgbox strMsg
End If

 

[instructorTek]

Thanks Remou your help was greatly appreciated.