When "FindFirst" does not work ?

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?
skybergAsked:
Who is Participating?
 
tcurtinConnect With a Mentor Commented:
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
 
brewdogCommented:
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
 
peroveCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
skybergAuthor Commented:
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
 
skybergAuthor Commented:
The reason why I can't use seek is that the argument side is not dynamic preventing me from using it in  general.
0
 
brewdogCommented:
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
 
skybergAuthor Commented:
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
 
skybergAuthor Commented:
When using FindFirst, does it only work along the Primary key ?
0
 
brewdogCommented:
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
 
skybergAuthor Commented:
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
 
brewdogCommented:
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
 
skybergAuthor Commented:
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
 
brewdogCommented:
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
 
MoondancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.