?
Solved

MS Access  FindFirst method issue...

Posted on 2003-11-20
11
Medium Priority
?
771 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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 …
Suggested Courses

800 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