MS Access FindFirst method issue...

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??
LVL 4
captainkirkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
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
wsteegmansCommented:
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
walterecookCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jadedataMS Access Systems CreatorCommented:
good analogy walt!
0
jadedataMS Access Systems CreatorCommented:
0
captainkirkAuthor Commented:
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
jadedataMS Access Systems CreatorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
walterecookCommented:
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
jadedataMS Access Systems CreatorCommented:
this is why I love working with Walt...Attention to details...
0
walterecookCommented:
Details and philsophy peddled here.  I leave actual answers up to guys like you Jack.  :)
0
walterecookCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.