MoveFirst Not First? (1 Viewer)

[grahamvb]

Hello Access-Programmers,

I have a table (tblContact) with an auto number key field that is numbered sequentially (1-8) there are no deletions, each new record is appended.

I wrote a simple FindFirst line to locate a record that is the first record in the table. The FindFirst failed to find the record.

So, I wrote a Do Until Loop that cycles through each record looking for the record that I want to find (the first record with key field 1).

    rstContact.MoveFirst
    Debug.Print "ContactID: " & rstContact.Fields("ContactID")
    Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf
 
    Do Until rstContact.EOF
            Debug.Print "ContactID: " & rstContact.Fields("ContactID")
            Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf

This works to find the first record... eventually, because it does not begin its search at the first record. The results in the immediate window are below.

ContactID: 4
CEmployerID: 2
 
ContactID: 4
CEmployerID: 2
 
ContactID: 5
CEmployerID: 4
 
ContactID: 6
CEmployerID: 3
 
ContactID: 7
CEmployerID: 1
 
ContactID: 8
CEmployerID: 5
 
ContactID: 1
CEmployerID: 1

I believe the field CEmployerID is unrelated to the issue. I am also attaching screen shots of the table "tblContact" and code with immediate window. I have tried indexing and not indexing the CEmployerID field in the Contact table to no avail. Even though the Do Until Loop eventually finds my record, it bothers me that the more simple Findfirst command is ineffective and I would like to know why and how to fix this. Any ideas where I have gone amiss?

 

[CJ_London]

MoveFirst moves to the first record of the recordset - which is not necessarily the first record you expect - you need to order your data in the order you wish to see it if you want it to present in a particular order.

e.g.

rst=currentdb.openrecordset(SELECT * FROM myTable ORDER BY ID")

FindFirst needs a parameter which is like a filter e.g.

FindFirst("ID=1")

Not sure why you want to effectively do a sequential search when you can set your recordset to provide exactly what you want

 

[pbaldy]

What's the recordset opened on? If SQL, what is it?

 

[Steve R.]

Did you have an "ORDER BY" clause for your recordset? If not add one. The "first" record is not necessarily the first physical record stored in the table. The "first" record depends on how the data is sorted.

Set BuildComboBoxRS = CurrentDb.OpenRecordset("SELECT CountyIDnum,CountyName FROM " & csCountyList & " ORDER by CountyName;", dbOpenDynaset, dbSeeChanges)

 

[grahamvb]

Here is the whole subroutine with the info I think all of you are looking for. It works in a long winded way.

Public Sub FindUnkCon()
On Error GoTo FindUnkCon_Err
 
Dim rstContact As DAO.Recordset
    varConFlag = 0
 
Set db = CurrentDb
Set rstContact = db.OpenRecordset("tblContact", dbOpenSnapshot)
 
    rstContact.MoveFirst
    Do Until rstContact.EOF
        If rstContact.Fields("CEmployerID") = Val(Me.cboFindEmployer.Column(0)) And _
        rstContact.Fields("CCalcName") = " Contact, Unknown" Then
            Me.cboContact = rstContact.Fields("ContactID")
            varConFlag = rstContact.Fields("ContactID")
            rstContact.MoveLast
        End If
    rstContact.MoveNext
    Loop
 
    rstContact.Close
Set rstContact = Nothing
 
    If varConFlag = 0 Then
        Call AddUnkCon
    End If
 
    Me.Refresh
 
FindUnkCon_Exit:
    Exit Sub
 
FindUnkCon_Err:
    Call Eror("Public Sub", "FindUnkCon", Screen.ActiveForm.Name, Err.Number, Err.Description)
    Resume FindUnkCon_Exit
 
End Sub

What did not work was the FindFirst method. I think because the FindFirst started in the middle of the record set like the Do Until Loop does.

Set rstContact = db.OpenRecordset("tblContact", dbOpenSnapshot)
    rstContact.FindFirst "[CEmployerID] = " & Str(cboFindEmployer.Column(0))
    Me.cboContact = rstContact.Fields("ContactID")
    rstContact.Close
Set rstContact = Nothing

 

[grahamvb]

CJ London,

Is this what you had in mind?

Set rstContact = db.OpenRecordset("SELECT * FROM 'tblContact' ORDER BY ContactID")
    rstContact.FindFirst "[CEmployerID] = " & Str(cboFindEmployer.Column(0))
    Me.cboContact = rstContact.Fields("ContactID")
    rstContact.Close
Set rstContact = Nothing

 

[pbaldy]

CJ has probably gone to a pub, but presumably meant more like:

Set rstContact = db.OpenRecordset("SELECT * FROM tblContact WHERE [CEmployerID] = " & Me.cboFindEmployer.Column(0))

Which is also what I would do. Why open the recordset on an entire table to find one record? Open the recordset on the one record.

 

[grahamvb]

pbaldy, you are the man! Problem solved with about 1/10th the code my solution took. Thank you!

Set rstContact = db.OpenRecordset("SELECT * FROM tblContact WHERE [CEmployerID] = " & Me.cboFindEmployer.Column(0))
    Me.cboContact = rstContact.Fields("ContactID")
    rstContact.Close
Set rstContact = Nothing

 

[pbaldy]

No problemo! Note that the second line will throw an error if the recordset doesn't find the person. I would typically test the recordset for EOF first:

If Not rstContact.EOF Then

 

[CJ_London]

Not the pub - visitors!

I was going to propose the solution Paul suggested once I better understood what Graham was looking for

 

[gemma-the-husky]

Graham. I suspect the problem was just one of understanding the precise syntax you needed. Ditto with the observation that an ordered query always uses the order specified, whereas using the table directly does not force any particular search order to be used, which may make a difference in some cases. As a matter of interest, note the difference between these two

rstContact.FindFirst "[CEmployerID] = " & Str(cboFindEmployer.Column(0))
and
rstContact.FindFirst "[CEmployerID] = " & cboFindEmployer.Column(0)

the first tests the employerid for a string equal to the value of the comob box, the second for a numeric value. depending on the data type, one of these may throw a run time error (type mismatch) although access/vba can often resolve some of these.

note also that column(0) is generally the bound column, and may be superfluous - so this probably works fine.

rstContact.FindFirst "[CEmployerID] = " & cboFindEmployer