Solved

When "FindFirst" does not work ?

Posted on 1999-01-05
14
340 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
[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
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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 …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

688 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