Look for two values in table and return message

[MikeN]

All my data entry options in my forms are working well..but now I'm to trying to limit duplicate entries in an access form though DLookup() but it doesn't seem like its recognizing "AND."

A little background: TripID is a foreign key in tblBeltSurvey. Within each trip (TripID) there can be up to four transects (one-to-many relationship) that are stored in tblBeltSurvey as TransectNum. I am running the code below on update of cmbTransectNum to check and make sure that specific transect number hasn't already been used for that specific tripID. I can't make TransectNum a pk because Transect #'s 1-4 can be repeated for separate trips. It works well to limit duplicate transects if there is only one TripID record, but when I go to add another 'trip' with transect 1 I get my MsgBox..even though there isn't a transect 1 for the new tripID record yet. If TransectNum X (or whatever) wasn't created for a previous trip then I don't get my message when creating transect #X under the newer trip. It seems like it isn't checking to see if the TransectNum AND TripID are records before it sends my message. Any thoughts to simplify this, or fix my code so I only get the message if a duplicate transect within a specific tripID is trying to be created?

If Me.TransectNum = DLookup("[TransectNum]", "tbl_BeltSurvey", "[TransectNum] = 1") And DLookup("TripID", "tbl_BeltSurvey", "TripID = " & Me.TripID.Value) Then
MsgBox "Transect 1 has already been created." & vbCr & "Please check transect number and try again.", vbInformation, "Duplicate Transect Number"
Me.Undo
.... repeated with TransectNum = 2...4
End if


-------------------
I also was thinking of creating a lookup function that checks to see if all four transects were already entered when the button is clicked to open the belt survey form so the user doesn't have to look through records to confirm they entered all the information for that trip. Something like...on form open...
If DLookup("[TransectNum]","tbl_BeltSurvey","[TransectNum] = "1" AND [TransectNum] = "2" AND [TransectNum] = "3" AND [TransectNum] = "4"") AND DLookup("TripID", _
"tbl_BeltSurvey", "TripID = " & Me.TripID.Value) Then
MsgBox "All belt survey transects have been entered for this trip." ......
End if

Is there an easier way to create a string criteria so I don't have TransectNum = 1 AND .....etc in my lookup? Or should I just keep my lookup criteria lengthy?

[orange]

Could you post a jpg of your tables and relationships?
Are TransectNums always assigned 1,2,3 etc. (sequentially)?

[MikeN]

Attached are images of the relationships and table (tblTrip with tblBeltSurvey). I have been doing it sequentially while I try to figure it out...but that might not always be the case in the future. There may be the occasion were TransectNum gets entered non-sequential order though.
Attachment 22740Attachment 22741

[orange]

You have a 1 to many relationship between Trip and BeltSurvey, so setting tripId before Transectnum seems appropriate.
What I'm saying is that TransectNum is only relevant when you know tripID.
One thing you could do is to make a unique composite index of TripID , TransectNum in tbl_BeltSurvey.
This will not allow duplicates. It will give an error message which you could test for and intercede.
I think an attempt to store a duplicate will result in 3022 error. So in your error handler you could check for 3022, and , if found, you could advise the user with your own message and then return to the appropriate area in your processing.

Other things (could be utility info) that may be useful is DCount() which will count the number of Transectnums for a given Tripid.

Or a query to identify trips by tripID with a count of transectnums for each trip.

Here is info on composite unique index.


Processing the potential 3022 duplicate record:

some sample code

ErrHandler:
100     If Err.number = 3022 Then
110      MsgBox "That record already exists. Proceed to another record"
120      rs.CancelUpdate
........