Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access  FindFirst method issue...

Posted on 2003-11-20
11
Medium Priority
?
776 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Technology Partners: 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!

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 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