Trying to find a specific Record using VBA

[FormerJarHead]

If the form I am using already has a record using a specific number, I want the user to be aware before they enter all the info. I found this code and it works except when it redirects me to the existing record, it doesn't. It redirects me to record #1. The field booking number is not a PK or AutoNumber field. It is a number field and as you can imagine, each is very specific to each person. Using that number as the PK field just won't work for this application. What I would like to accomplish is to be redirected to an existing booking number if it exists, otherwise the user will just fill in the form as needed. Here's what I have"

CODE:
[Private Sub Booking_Number_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.BookingNumber.Value
stLinkCriteria = [BookingNumber] = " & " '" & SID & "'"
'Check tblClient table for duplicate BookingNumber
If DCount("*", "tblClientInfo", "[BookingNumber]=" & Me.BookingNumber) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Booking Number " _
& SID & " has already been entered." _


& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Booking Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub]

Any help will be greatly appreciated

[orange]

How do you identify the record such that you could inform the user if such a record already exists?

[FormerJarHead]

The first control source on the form is Booking Number. After the Chaplain fills in this field, using the code in the Before Update of that control, the error message pops up which helps them quite a bit. Ideally, the Booking Number would be the best way to address this, however we have multiple jails in our county and not all inmates require, or want, services from our Chaplains so the next best identifier is the BK#

[orange]

So the basis of this is to prevent a duplicate Booking for this person at this time.
So what other fields identify the person and the time and Booking????
The reason I'm asking is the booking number is a great system identifier but means ziltch to the user. So if you had a unique index on say Person and Time, you could do a dcount and if not 0, then you have a duplicate, and can take necessary action.

[FormerJarHead]

The database form we use is for the Chaplains in our facilities. The inmates are already in custody and have been given a BK# at the time they are placed in custody through our Jail Information Management System (JIMS). When the inmates are assigned to a housing unit, the Chaplains can provide services such as Bibles, reading glasses, spiritual encouragement and so on. This information is tracked on a subform in the main form. The main form contains just the releavant info, BK#, Last name, First Name, etc.

The inmates are moved around frequently between facilities but their booking number will stay with them while incarcerated. When a Chaplain at another facility provides services to an inmate who is otherwise already in the Chaplains Database, they create a new record which creates a duplicate entry based on booking number. If we move this inmate to four of our jails and each Chaplain provides services for this one inmate, there could potentially be several entries using this individuals BK#. What I would like to happen is as soon as the Chaplain types in the inmates BK# and before he is able to tab to the next field, is for the error message to populate, the entry be erased, or undone, and then redirected to the previous record opened that contains the information of this inmate, based on his/her BK#.

[orange]

If everyone uses Booking Number, then why not do a search for Booking Number and show the last record?
Wouldn't that show you either
a)a Booking for whatever the chaplain is about to enter in duplicate, or
b) the last entry(service) that was relevant to this individual.

Do this check before you start adding a new or possibly duplicate record.

I'm still a little confused with the terminology.

The inmate has an assigned BookingNumber -- so it identifies the inmate.
The chaplain enters the system to record some service which sounds very much like
"meeting"/"servicing" this inmate at some date/time?

[FormerJarHead]

The date the service is given to the inmate is recorded in the subform. Doing a search is simple enough, but an extra step the not so computer savvy Chaplains would need to perform. It will much easier to have the function be automated at the time the BK# is entered. As it is now, it works. It just doesn't redirect them to the first entry made based on the inmates BK# in which case they will still need to do a search using the built-in Find Function. I'd rather they don't have to do this...

[orange]

I'm not understanding how there is a possible duplicate.
Maybe you could describe in plain English what exactly is the problem you're trying to solve.
You said it works now. And you said you don't want them to type a lot of info in only to find out there's a duplicate.

Duplicate what exactly?

And just for clarity, the data is stored in tables. It is displayed on forms and subforms, and yes some forms are for data entry.

[FormerJarHead]

I'm sorry...Let me try to rephrase.
Inmate Smith is booked into Jail A. Booking Number 123456789. After 1 week in custody he has seen the Chaplain and received 1 Bible and 1 pair of glasses.
2 days later Inmate Smith is transferred to Jail B. While there he gets another Bible and anothe pair of glasses from the Chaplain at Jail B. There are now 2 entries for Inmate Smith BK# 123456789. The first is record #17 (Autonumber) and the second is record #123.
5 days later Inmate Smith is again transferred to Jail C. He makes the same request from the Chaplain at that facility and another entry is made for the same items.

If this was working as I had hoped, the Chaplain at Jail B would have been able to tell that the Chaplain at Jail A had already made an entry for this inmate because the Booking Number is a duplicate. If this was working as I had hoped, once this notification was made to the Chaplain at Jail B, the record that was created by the Chaplain at Jail A would have opened (redirected) and the Chaplain at Jail B could deny the request because he can now see the entries made by Chaplain at Jail A.

I hope this is explanation makes more sense.

[orange]

I hate to be a pain, but what are the Tables and relationships you are working with? I can see a set up such as the following, if you have a standard set of services.

tblInmates
InmateId -----------------
BookingNumber------------ 1 of these would be the PK
Firstname
Lastname
..


tblServices

ServiceId (PK)
ServiceName

tblInmateServices This is a junction table to identify who exactly received what service exactly and when
InmateServideId (PK autonumber)
BookingNumber (identifies the inmate)
ServiceId (identifies the service)
ServiceDate (Date the service was performed/enacted..)
ServiceLocation (Jail Identification ...)
ServiceComment (any special comments quantity/rationale/conditions..)
ServiceCompletedBy (identifies Chaplain.others...)
......
I would recommend a compound unique index of
BookingNumber +ServiceId + Servicedate (and ServiceCompletedBy ) if there is a possibility of the same service for the same inmate on the sameday [by the same person])
This compound unique index will prevent duplicates. Access will raise a trappable error if a duplicate is entered)

[FormerJarHead]

Here is what I have:

tblClinetInfo
Client ID PK & AutoNumber
BookingNumber Number
LastName Text
FirstName Text
Gender Text
Facility Text
Housing Unit Text
..

tblReportCodeDetails
ReportCodeID PK/AutoNumber
ClientID Number
Description Text
Date Issued Date/Time
Facility Text
Chaplain Text
..

There are also other tables but they have no direct impact on what I'm trying to accomplish. Is the problem due to the fact the BK# is not the primary Key field in the table? If so, is there a work around?

[orange]

So where would the information about the Service be recorded
-- a Bible and pair of Glasses to theClient with BookingNumber 123456789

In your tables you have ClientId as PK, and you have ClientId as field (could be used as FK in tblReportCodeDetails)
You can go from BookingNumber to ClientId and find in other Table using ClientId.

Did you follow the tables I mentioned?

[ssanfu]

PMFJI,

What about this.... Since the BookingNumber must be unique (right?), couldn't the field be set to indexed - no duplicates? That would eliminate entering duplicate BookingNumbers without any code. Would need error handling though.

If you need the ClientID (PK), either
a) write a function to return the ClientID based on the BookingNumber (using a recordset) or
b) use the DLOOKUP() function (or ELOOKUP() - http://allenbrowne.com/ser-42.html) to get the ClientID.

My $0.02

[orange]

Sounds reasonable Steve.
That's why I suggested

You can go from BookingNumber to ClientId and find in other Table using ClientId.

[FormerJarHead]

Thanks Steve...all are Indexed No Dups. That was my first step. I have code written to handle the error(3022). This needs to go into the After Update event so it doesn't give the msgbox until after all the data had been entered. This creates a look of extra work. Was trying to get something that basically said...That number is already used. Let me show you where it is...I just don't know what other code to write. This has been an all day sucker. I know Orange is probably done with me...I don't see the reply I posted showing all of the data in the tables. Maybe this only allows for so many kb's to be uploaded...