Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why won't findfirst find the required record

Posted on 2013-10-24
6
Medium Priority
?
503 Views
Last Modified: 2013-10-25
I am running the following code and the findfirst step always finds the first record even though the value of lDealer does change from 1 to 2. It never finds rs!ID = 2 even though it does exist. What am I doing wrong? Id is an autonumber field and therefore should be long. I have tried it in both MS Access 2010 and 2007

sub mySub
    Static lDealer As Long
    Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset("Select Contacts.ID from Contacts order by id", dbOpenDynaset)
   
    If lDealer = 0 Then
        rs.MoveFirst
    Else
        rs.MoveFirst
       
        rs.FindFirst rs!ID = lDealer

        rs.MoveNext
        If rs.EOF Then
            rs.MoveFirst
        End If
    End If
   
    lDealer = rs!ID
    rs.Close
    Set rs = Nothing

end sub
0
Comment
Question by:Rob4077
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39596902
How are you calling this sub?

If it is only called once, the code will execute one time, find the record corresponding to the current lDealer and exit.

There is no looping in the sub itself to find another record, so unless you are somehow making multiple calls to the sub, it will stop at the first record.

What are you trying to do with the code?
0
 

Author Comment

by:Rob4077
ID: 39597198
The code is called by a timer loop.

When I test it the value of lDealer does increase but for some reason it never finds anything but the first record.

The reason I am using it is to display a rolling ToDo list. It is meant to find the first ID record, display a screen with all the records for that person and scroll through the list. When it gets to the end of the list it finds the next person and repeats the process. When it gets to the last person it starts again
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39597230
I guess it should read:

Sub mySub
    Static lDealer As Long
    Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset("Select Contacts.ID from Contacts order by id", dbOpenDynaset)
   
    If lDealer = 0 Then
        rs.MoveFirst
    Else
        rs.FindFirst "ID = " & lDealer & ""
        rs.MoveNext
        If rs.EOF Then
            rs.MoveFirst
        End If
    End If
    lDealer = rs!ID
    rs.Close
    Set rs = Nothing

End Sub

/gustav
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39597447
Is ID a numeric or text value?  It sounds like it is numeric, but I'm not sure.  It is also not clear whether this is actually supposed move your focus in your form to the record that corresponds to the lDealer value on your form.  You also never actually set the value of lDealer in this subroutine.  If your intent is to enter a DealerID in a textbox, and cause the form to move to that dealers records, I would not normally use a timer event to perform this operation; I would use the txt_DealerIDFilter_AfterUpdate (usually in the form header) or a command button located next to that control.  Lets assume this is in a command button cmd_GotoRecord

Try this:

Sub cmd_GotoRecord_Click

    With me.Recordsetclone
   
        .FindFirst "ID = " & NZ(me.txt_DealerIDFilter, 0)

        if .NoMatch then
            msgbox "ID value not found"
            .MoveFirst
        Endif
        me.bookmark = .Bookmark

    End With

End Sub
0
 

Author Comment

by:Rob4077
ID: 39600095
Hi fyed, thanks for your suggestion but it's not quite what I am trying to do. Actually I have put together what I want to do, it's just the findfirst command that I couldn't get to work.

gustav your solution solved the problem. I didn't realise that the syntax of the command required the field and value being tested had to be inside double quotes. Thanks.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39600104
You are welcome!

/gustav
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question