Ordering in Record Set

[UAlbanyMBA]

How does a Record Set order the when there is redundancy?
The example I have is if you search a DB for a customer, but there are two or more customers with the same name, i.e John Smith. I want to show the contact info for each John Smith, but currently I can only get the data to show up for the first John Smith.

Currently I have it setup up like this:
Address1 = rs(3)
Address2 = rs(4)
City = rs(5)
State = rs(6)

You can imagine the rest. With other code I can receive the names for all the names, because I get that many records, but the contact data is the same. How does the rs work so I can code this?

[MMcCarthy]

You will have to post the actual code you are using. I'm afraid I can't follow what's happening from what you've posted so far. I know that it probably makes sense to you but there is not enough information there for anyone to understand what you are doing or what is going wrong.

Mary

[NeoPa]

Like Mary, I can't really follow your explanation.
However, I'll guess that you want to return all records in your recordset where they match your selected name.
In this case (as you haven't supplied any information for this) I'm assuming you have the selection in a TextBox called txtName on your form (we'll call frmCust for clarity). I've also assumed your data is found in the table tblCust.

strSQL = "SELECT * " & _
         "FROM tblCust " & _
         "WHERE CustName='" & Me!txtName & "'"

If this doesn't help, consider checking POSTING GUIDELINES: Please read carefully before posting to a forum for help on how to post a question that's more likely to be understood and get responses.
Good luck.

[UAlbanyMBA]

I very sorry for not posting all the code. So once again, my question is, if I have two John Smiths, how does the Record Set order the data. Right now it only changes the DistID, and nothing else. How do I fix this. Here you go.

Private Sub FindCustomer_Click()
Dim FirstName As String
    txtFirstName.SetFocus
    FirstName = txtFirstName.Text
 
    Dim LastName As String
    txtLastName.SetFocus
    LastName = txtLastName.Text
 
    Dim CallID As String
    Call_ID.SetFocus
    CallID = Call_ID.Text
 
    Dim Phone As String
    Telephone.SetFocus
    Phone = Telephone.Text
 
    Dim Email1 As String
    Email.SetFocus
    Email1 = Email.Text
 
    Dim DistID As String
    Dist_ID.SetFocus
    DistID = Dist_ID.Text
 
    Dim dbs As DAO.Database
    Dim rs As Recordset
    Dim rs2 As Recordset
 
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
 
    Set dbs = CurrentDb
 
    lblerror1.Visible = False
    lblError2.Vertical = False
 
    If ((FirstName <> "") And (LastName <> "")) Then
         Set rs = dbs.OpenRecordset("Select * FROM Caller WHERE C_F_Name like '" & FirstName & "*' and C_L_Name like '" & LastName & "*'")
    End If
 
    If (((FirstName = "") And (LastName = "")) Or ((FirstName = "") And (LastName <> "")) Or ((FirstName <> "") And (LastName = ""))) Then
        lblerror1.Visible = True
        lblError2.Visible = False
    Else
        If (rs.EOF) Then
            lblerror1.Visible = False
            lblError2.Visible = True
            Call_ID = ""
            Telephone = ""
            Email = ""
            Dist_ID = ""
            lstSearchResults.RemoveItem (1)
 
        Else
            lblerror1.Visible = False
            lblError2.Visible = False
            Call_ID = rs(0)
            Telephone = rs(3)
            Email = rs(4)
            Dist_ID = rs(5)
 
            Set rs2 = dbs.OpenRecordset("SELECT Date_filed, Customer_Order_Number, Purchase_Order_Number FROM Request WHERE Request.Call_ID = Call_ID")
 
            lstSearchResults.RowSource = "Value List"
            lstSearchResults.ColumnCount = 3
            lstSearchResults.RowSource = "Date Filed; Customer Order Number; Purchase Order Number"
            While (Not rs2.EOF)
               Dim row As String
                row = rs2(0) & ";" & rs2(1) & ";" & rs2(2)
                lstSearchResults.AddItem (row)
                rs2.MoveNext
            Wend
        End If
    End If
 
End Sub
 

Private Sub lstSearchResults_Click()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
 
    Set dbs = CurrentDb
 
    Dim strSQL As String
    strSQL = "Select * FROM Request WHERE Customer_Order_Number = " & lstSearchResults.ItemData(1)
    Set qdf = dbs.CreateQueryDef("GetRequest", strSQL)
 
    DoCmd.OpenForm ("Request3")
End Sub
 

[MMcCarthy]

You never actually search the recordset using a loop. It's not that it's not returning more than one record you're just not looping through them. Have a look at this example in the tutorials section.

Mary

[UAlbanyMBA]

That tutorial really helps. I am just not sure what "Query2" is supposed to do? That may sound really stupid, but that is tripping me up.

[MMcCarthy]

That tutorial really helps. I am just not sure what "Query2" is supposed to do? That may sound really stupid, but that is tripping me up.

In the example the code loops through query1 and for each record it loops through query2 to see if it can find a matching value. If a matching value is found it preforms the update if not it continues to loop. When the code finished looping through query2 it moves on to the next record in query1 and starts again.

Mary

[MMcCarthy]

Implement as much as you can using the example and then we can address any outstanding problems.

Mary

[UAlbanyMBA]

That wasn't quite what I meant by what is query2.

My Query1 =

"Select * " & _
"FROM Caller " & _
"WHERE C_F_Name like '" & FirstName & "*' " & _
  "AND C_L_Name like '" & LastName & "*'"

So that gives me my rs of all the "John Doe's" in my system.

Now what is Query2, and how is that gonna help me differentiate between John Doe #3 and John Doe #7?

I have no other search parameters. All I am trying to get out of this is for my form to show the appropriate information for each John Doe. I think that is what these nested loops are doing, but like I said, I don't have a Query2.

I hope this helps clarify my problem, if not I am sorry.

[NeoPa]

That wasn't quite what I meant by what is query2.

I assume you meant - How does Query2 fit into my situation?
You probably don't have a need for a 'Query2' in your situation.
The tutorial illustrates how multiple queries can be processed together within VBA code. It includes examples of a number of the functions you need to use when doing something similar. The tutorial probably has more than you need, you just need to ignore the bits that you don't need.
Does that help to clarify?

[UAlbanyMBA]

Kind of. But if I may not need a second query, why would I need to do this loop? Also, if I don't need this second query or the loop, that puts me back to my original question. How does a rs organize its data?

If I have a table with 5 columns, and 3 like names (i.e. John Doe)
Is John Doe #1 represented by rs(0) - rs(4)
John Doe #2 rs(5) - rs(9)
John Doe #3 rs(10) - rs(14)?

Thanks

[NeoPa]

You're losing me a bit here.
However, that's certainly not how it works. The tutorial (ignoring Query2) should illustrate how this works, but briefly :
Each separate record (or row if you prefer) is a different line of data. A recordset object can refer to only one record at a time. Normally (although there are often multiple ways of doing things), a field in the current record of a recordset (rs) is referred to as rs!FieldName.
Is that an easier way to think of it?

[UAlbanyMBA]

So using this example:

FName LName ID
John Doe 12
John Doe 35
John Doe 98

I have a record set with three records. So if I were to assign values to the rs, i would only only have rs(0), rs(1), & rs(2)? Or would those numbers only represent the first record?

Here is where I am also confused. When I code rs!FieldName, is FieldName as reserved word in this case or do I insert my specific FieldName in this case?

Now to address my original question, which may help clarify the problem for both of us. In my form I have three textboxes. One for FName, LName, and ID. I have a find button for the record. I type in FName and LName, and click find. The boxes will fill with John Doe 12, but when I click the next record on the bottom of the form, nothing changes. My code from a previous post shows this.

I hope this helps some more.

Thank you.

[UAlbanyMBA]

Just noticed something in the tutorial, that doesn't work for my case.

The if statement

If rs![FieldName] = rs2!FieldName Then
'rs2.Edit
'rs2![FieldName] = 'Your Value'
'rs2.Update
End If

Since I don't have 2 query, which would make 2 rs, what do I compare?

[MMcCarthy]

The problem is what you are trying to do with this code. The initial point of how to get more than one result from a recordset has been answered. The bigger question here is why are you using recordsets and what exactly are you trying to do with the results.

Mary

[UAlbanyMBA]

Ok, I hope this explains what I am doing better.

I have a database filled with clients. When a customer calls the user first must see if the customer exists in the system. This requires the first and last name each in its own respective textbox. The find button first makes sure both fields have data in them, and then creates a record set of all the the fields for any name matching the search criteria. In this case John Doe, where fictionally have three of them. After the find button is clicked the empty textboxes on the form are filled with the information from that particular record. In my past examples this would be John Doe's ID number. What I want is for every time I click the next record button on the bottom, I want the ID number to change, showing it changes the record set.

My real world example of course has more than just ID, but several other fields.

Thank you.

[MMcCarthy]

Ok, I hope this explains what I am doing better.

I have a database filled with clients. When a customer calls the user first must see if the customer exists in the system. This requires the first and last name each in its own respective textbox. The find button first makes sure both fields have data in them, and then creates a record set of all the the fields for any name matching the search criteria. In this case John Doe, where fictionally have three of them. After the find button is clicked the empty textboxes on the form are filled with the information from that particular record. In my past examples this would be John Doe's ID number. What I want is for every time I click the next record button on the bottom, I want the ID number to change, showing it changes the record set.

My real world example of course has more than just ID, but several other fields.

Thank you.

Ok, there are better approaches to searching for records in a form. Have a look at NeoPa's tutorial on this and then come back with any questions.

Example Filtering on a Form

Mary

[NeoPa]

Sorry I couldn't get back to this earlier.
This is to answer your specific questions and doesn't negate or contradict any other comments posted since.

I have a record set with three records. So if I were to assign values to the rs, i would only only have rs(0), rs(1), & rs(2)? Or would those numbers only represent the first record?

Absolutely, when the first record is the current record.
Whenever the current record pointer is moved, these references move with it.
It is not possible to access multiple records of a recordset at the same time in code.

Here is where I am also confused. When I code rs!FieldName, is FieldName as reserved word in this case or do I insert my specific FieldName in this case?

You need to replace the word (FieldName) with the actual name of your own field.

[NeoPa]

Just noticed something in the tutorial, that doesn't work for my case.

The if statement

If rs![FieldName] = rs2!FieldName Then
'rs2.Edit
'rs2![FieldName] = 'Your Value'
'rs2.Update
End If

Since I don't have 2 query, which would make 2 rs, what do I compare?

This indicates to me that you are trying to duplicate the tutorial as an answer to your problem. This is not the right idea at all. You need to learn the concepts from the tutorial and fit them into your own situation. In the tutorial are examples in code of,

• Moving the current record.
• Accessing data within the current record.
• Looping around your code to process all records in a recordset.
• Etc.

[NeoPa]

Don't forget to check out the tutorial in Post #17 (That may have scrolled off the page now). You can even download the example database there and look at it in more detail. I feel most of what you need is in there. Come back and let us know how you get on. We'll still be here when you've done that.