Unbound form + duplicate values error message

[Opal]

Could someone please help me create a unique
message to replace the system message for duplicate
values? My form is unbound and will remain unbound.
I am running Access 2003. I have looked in the help
and found the following:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "Each employee record must have a unique " _
& "employee ID number. Please recheck your data."
MsgBox strMsg
End If
End Sub

How would I handle this for an unbound form?

 

[Allen Browne]

If the form is unbound yet you are writing values to a table, you must be
executing action queries to perform the INSERT, DELETE, or UPDATE.

The DELETE won't cause a duplicate, but the INSERT and UPDATE might.
Therefore you will need to use Execute with dbFailOnError so you are
notified if the action failed. Use error handling. Trap the error number,
and in your error handler, give whatever message you deem appropriate.

If Execute is new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

If error handling in VBA code is new, see:
http://allenbrowne.com/ser-23a.html

The form's Error event won't be any use in to you.

 

[Opal]

Hi Allen,

Helpful as always! This is what I pulled together:

Err_MgmtAdd_Click:
Select Case Err.Number
Case Is = 3022
Select Case MsgBox("This name already exists in the database. You
may not duplicate these records in the database. Do you need to update
this record?", vbYesNo, "Confirm Action")
Case vbNo
'Do Nothing.Default Behaviour
Case vbYes
DoCmd.OpenForm "frmMgmtUpt"
End Select
Case Else
Resume Exit_MgmtAdd_Click
End Select

But the update form will not open, so I have some more troubleshooting
to do.

 

[Opal]

Still cannot get the form to open....any suggestions?

 

[Allen Browne]

I don't understand what you are doing here using Form_Error.

Try the Click event of a command button to perform your update/insert. See
if the form opens then.

 

[Opal]

Sorry Allen,

Basically, if the user attempts to enter a new record
where the value already exists in the table and I don't
want duplicate records in my table, I want to to give the
user the option to open an update form and modify the
record rather than create a duplicate record.

The table contains the various users' names, their home
shop, their Supervisor,...etc. However, people move
around too and may report to other supervisors. I want
them to be able to update this information rather than
put their name into the table a second time and create
duplicate records. So if they answer "Yes" to my question
as to whether they want to update the record, I want to
be able to open the update form, but in my error handler
above, the form will not open.

 

[Mike Painter]

I've missed all of this but do you require the user to search for an
existing record first to make sure that a change is made to the proper
person?
Having once worked in a store with two people named Donald Leeroy Smith
I've always been aware of this problem.

 

[Allen Browne]

What I don't understand is that there is no concept of a 'new record' or a
'duplicate record' in the events of the *form* if this is an *unbound* form.

If it is a bound form, we need to start again in talking about how to
address this issue.

If it is an unbound form, give up on the form's events, and use your own
events (such as the click of a command button.)

 

[Opal]

I am adding some additional functionality to an
existing DB. I do not wish to re-write the DB. The
form is unbound in the existing DB, but writes to to a
table where the employee number will not accept duplicate
values.

For example: Employee # 1234 already exists in the database, but
the records show him working in Shop A. He has since moved to
Shop B. User (not necessarily employee #1234) wants to put in a
record of activity for this employee. Filters on the activity input
form
do not bring up this employee in Shop B because, as I said, he is
still in shop A in the database but the user does not know this. He
attempts to add the employee to the DB via the unbound form and gets
the message that the employee already exists. I want him to have
the option of opening another form, this one bound, so that he can
update the record for employee #1234 and move him from shop A to
shop B. When I use the error handling for the duplicate record:

Err_MgmtAdd_Click:
Select Case Err.Number
Case Is = 3022
Select Case MsgBox("This name already exists in the database. You
may not duplicate these records in the database. Do you need to
update
this record?", vbYesNo, "Confirm Action")
Case vbNo
'Do Nothing.Default Behaviour
Case vbYes
DoCmd.OpenForm "frmMgmtUpt"
End Select
Case Else
Resume Exit_MgmtAdd_Click
End Select


The bound update form will not open. But I want it to..... this is my
goal.

Does this help clear up what I am trying to achieve? If so, is it
possible?

 

[Allen Browne]

No; I can't follow that.
Use a different event.

I don't think there's anything I can add.

 

[Opal]

Thank you for your support. I have a handle on how to do what I want.