Solved

When "FindFirst" does not work ?

Posted on 1999-01-05
14
326 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:skyberg
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

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

Expert Comment

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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