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
Solved

When "FindFirst" does not work ?

Posted on 1999-01-05
14
337 Views
Last Modified: 2008-02-26
I've got a database that is beeing aotomatically updated from DOS-files. In the database there are relations (one-many). On inserting a new record on the many-side I check wether the corresponding one-side-record exists.
In too many cases, the "FindFirst" cant find the record.
The one-side-table is opened as dynaset, the primary (unique) key is defined composed of more fields which match with the argument in the "FindFirst". The one-side record is, in many cases inserted a previous day, so there could not be a refresh-problem. Is there something to do about it?
0
Comment
Question by:skyberg
14 Comments
 
LVL 10

Expert Comment

by:brewdog
ID: 1972599
I suppose my question would be . . . if there is a relationship set up (with integrity turned on), why are you checking for the existence? Are you checking before trying to add the record on the many side? Access will give you an error if there isn't a record on the one side, so I would simply trap the save routine (or Form_Error, if it's a bound form) for that error (it's number 3201, I believe) and then tell the user what I wanted to.

Does that make sense?

brewdog
0
 
LVL 9

Expert Comment

by:perove
ID: 1972600
I remember that I had problems with a unsable findfirst statement, doin' a .MoveLast BEFORE I do the findfirst did solve it for me.
I general I prefer usin' seek it is (a LOT) faster

perove
                  
0
 
LVL 3

Accepted Solution

by:
tcurtin earned 50 total points
ID: 1972601
If you have a defined relationship with Referential Integrity enforced, Access will notify you if the 'Key' does not exist. The other thing you could do is make a combobox that reads from the parent table. Set the RowSource = to a query that reads from the parent table and set the control source = to a field on the child table.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:skyberg
ID: 1972602
The reason whyI try to access the record on the one-side is that relationship between the one- and the many-side is based on a unique field (counter). It is in order to update this field on the many-side I have to do this before I do the insertion.
0
 

Author Comment

by:skyberg
ID: 1972603
The reason why I can't use seek is that the argument side is not dynamic preventing me from using it in  general.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 1972604
Then how does your FindFirst work? What are you trying to find, if you don't know which counter number the record was given in the one-side table?
0
 

Author Comment

by:skyberg
ID: 1972605
I do of cause know each recno whwn I am on the one-side.
However I don't know it when I am on the many-side.
To find that out, I have to Look it up via a second index that is far more complex than the mere recno which is the PrimaryKey.
The many-side has all the fields available to do the job.

0
 

Author Comment

by:skyberg
ID: 1972606
When using FindFirst, does it only work along the Primary key ?
0
 
LVL 10

Expert Comment

by:brewdog
ID: 1972607
No, it will work on any field -- and the fact that you have an index set up on the combination of fields in both tables helps.

Does your FindFirst *ever* work? It sounds like it does sometimes, which means it wouldn't be a syntax issue. Hmmm . . . would it be possible to get a copy of the database at dbrewer@uhc.com? I'd be interested in looking at it.

brewdog
0
 

Author Comment

by:skyberg
ID: 1972608
About 1/3 of the FindFirsts do not work.
The argument  is composed of 4 fields, 3 numeric and one date (#...#). In manually looking up the one-side the record is there.
The problem, I think is that the "pointer" to the table on the one-side must be reset to the first record. If not, it will continue searching from where it was before the Find. So how do I reset the pointer, MoveFirst ? no, that does not help, nor does MoveLast. I must move, not to the first record, but to the record with the lowest Key-value.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 1972609
One of the FindFirst arguments, though (I don't remember which one; Help could tell you) says whether to start at a current record row or at the top. I thought the default was to start at the top . . . so that shouldn't be a problem.

If you really want to pursue that line, you could do something like this:

Dim strSQL as string
Dim rs as recordset

strSQL = "select * from [One-sideTable] where [Field1] = " & txt1Value & " and [Field2] = " & txt2Value & " and [Field3] = " & txt3Value & " and [DateField] = #" & txt4Value & "#;"

set rs = currentdb().openrecordset(strSQL)
rs.movelast                               'if there is no matching record, this will prompt error 3021
       'do whatever you want here; if the code gets here, that means there is a match

exit sub

ErrorRoutine:
   If err.number = 3021 then
          'whatever you want to do with a non-matching entry

Would that work, I wonder . . . ? It sounds like you don't really need to *do* anything with the one-side value, just verify that it's there. if so, the above code could check.

Let me know if this is a promising line of thinking for you . . .

brewdog
0
 

Author Comment

by:skyberg
ID: 1972610
I've found the reason why some FinfFirst are not operating properly. If there is a Date as a part of the key, the system antisipate an american format if mm < 13. If mm > 12 the system antisipate an european format. If I swap mm and dd, everything works ok. This problem has nothing to do with date-format-setting in Windows, but must be stored somewhere deep inside Access.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 1972611
Are you withdrawing the question, then? It sounds like you've solved your own problem . . . and given us all something to remember for our reference. We should give you points. (smile)
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6875601
GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.  Also in the Community Support link is a question on how experts can help, if they wish, on the cleaning of old and abandoned questions.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

856 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