Solved

MS Access  FindFirst method issue...

Posted on 2003-11-20
11
768 Views
Last Modified: 2012-05-04
Given the followinfg code:

        rsChurches.FindFirst "[ChurchID] = ChurchID"
       
        If rsChurches.NoMatch Then
            MsgBox ("Error in updating Churches record for < " & ChurchID & " >")
        Else
            rsChurches.Edit
            rsChurches!SeniorPastorID = SeniorPastorID
            rsChurches!YouthLeaderID = YouthLeaderID
            rsChurches!YALeaderID = YALeaderID
            rsChurches!MusicLeaderID = MusicLeaderID
            rsChurches!MinisterialAllianceID = MinisterialAllianceID
            rsChurches!AffiliationID = AffiliationID
            rsChurches!ChurchDenominationID = ChurchDenomID
            rsChurches.Update
        End If

The Churches record has been previously created, and the FindFirst method supposedly finds the right record, but the Update seems to ALWAYS update the first record in the recordset. The documentation says that FindFirst makes the record found the current record, so what gives??
0
Comment
Question by:captainkirk
[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
11 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9791287
Hey captainkirk!

  Findfirst "FINDS" the "FIRST" record meeting the conditions you specify in the WhereCondition parameter
 
  What I don't see is the code that stages the recordset up.  This can have an effect on the FindFirst operation.

regards
Jack
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9795299
I think it's already going wrong in your first line:
       rsChurches.FindFirst "[ChurchID] = ChurchID"

I suppose your second ChurchID is a variable, so you have to concatinate those two string like this:
       rsChurches.FindFirst "[ChurchID] = " & CStr(ChurchID)

I think he will now find the right record!

Regards.
0
 
LVL 17

Expert Comment

by:walterecook
ID: 9797076
I'll take a different spin on this and suggest that FindFirst isn't what you want at all.  Using this method is akin to scanning down a list of data in excel and stopping at the first data you find.  However in a rdbms this idea is completely irrelevant.  What would happen if, for instance in the excel example, someone had re-sorted those records without your knowledge?  This is sort of what it is like with records in a db table, order means nothing and shouldn't be counted on.

UNLESS you have already taken care of that fact when you defined and opened your recordset.  (in which case I've just wasted your and everyone else's time for which I apologize.)

Rather I would pose that you should query the records for the lowest(or highest) autonumber or some other key; lowest or highest entry date or something based on your chosen ChurchID.  
Whether or not you handled the sorting order, this is still a safer way to go.

Just some thoughts
Walt

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:jadedata
ID: 9797743
good analogy walt!
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9797998
0
 
LVL 4

Author Comment

by:captainkirk
ID: 9820667
Walt -

ChurchID is a variable (long) set earlier in the script and [ChurchID] is a field of type long in the Churches table.

 rsChurches.FindFirst "[ChurchID] = ChurchID" should locate the first rec in the recordset where the ChurchID field matches the variable. I wouldn't think sorting would have anything to do with it, 'cuz we are looking at a field which is the primary key of the table. The CStr() approach doesn't seem to make sense to me 'cuz neither the variable nor the field are strings - they are longs.

What is the use of specifying search criteria in the FindFirst method if it is not functional?
0
 
LVL 32

Accepted Solution

by:
jadedata earned 125 total points
ID: 9820729
Try this syntax
  rsChurches.FindFirst "[ChurchID] = " & ChurchID

where ChurchID must be a valid ID in context

If the church ID is coming from a form for comparison
    rsChurches.FindFirst "[ChurchID] = " & me("txtChurchID")

If it is a variable pass to the code it should have a different name than the field so Access doesn't get confused:
    rsChurches.FindFirst "[ChurchID] = " & lngChurchID  '< where lngChurchID is a long integer datatype.
0
 
LVL 17

Expert Comment

by:walterecook
ID: 9820793
All I was saying is be careful, sort order can be (and often is) an illusion.  In this rs:
a      1
b      1
a      2
b      2
a      3
b      3
a      4
b      4
If you search for first occurrence of 'b' you end up with 1.
Same rs different order:
a      4
b      4
a      3
b      3
a      2
b      2
a      1
b      1
the FindFirst would return 4.
See?
Just be careful how things are sorted, particularly if you are pulling directly out of a table.  What we think is "in order" is often very different than what Access thinks is "in order"

Walt

0
 
LVL 32

Expert Comment

by:jadedata
ID: 9821251
this is why I love working with Walt...Attention to details...
0
 
LVL 17

Expert Comment

by:walterecook
ID: 9821375
Details and philsophy peddled here.  I leave actual answers up to guys like you Jack.  :)
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10392194
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: jadedata {http:#9820729}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

696 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