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]

Admin

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