Solved

MS Access  FindFirst method issue...

Posted on 2003-11-20
11
756 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
11 Comments
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
good analogy walt!
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Author Comment

by:captainkirk
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
this is why I love working with Walt...Attention to details...
0
 
LVL 17

Expert Comment

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

Expert Comment

by:walterecook
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now