How to prevent identical values in pkey field?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Currently with the lines I have here, if I try to save identical value in
the primary key field, Access will force me to debug, but I'd really need a
long term solution to this frquently occuring problem...

Private Sub cmbSave_Click()
' First save the changes or new record
' this line will force the currently edited record to be written to disk,
' without moving to the first record in the form's recordsource
If Me.Dirty = True Then Me.Dirty = False
End Sub

What I have in mind is that if the value entered in the primary key field
already exists, then when the Save button is clicked, a message box (with a
descriptive message and only Yes button available to abort the saving action)
will pop up.

How can this be executed?

Regards,
Sam
 
Allen --- I do something similar, but I have to use "MsgBox CSTR(Err.Number)
& Err.Description" for it to work on my Access 2000 system. I was getting
an error in the error handler trying to concatenate different data types.
Bob.
 
Fair enough, Bob.

The CStr() is logical, though it should not be required.
This should work:
MsgBox "Error " & Err.Number & " - " & Err.Description
 
Hi Allen,

I've adopted your error handler and it works just like I needed it to, thanks.
But can I still write my own error message instead of the default message
(just so that I can inform the user exactly what to fix)?

Private Sub cbSave_Click()
On Error GoTo Err_cbSave_Click
' Code to do something here
Exit_cbSave_Click:
Exit Sub
Err_cbSave_Click:
MsgBox Err.Description
Resume Exit_cbSave_Click
End Sub
 
Sure. In whatever routine you want a cusom error message, trap the error
number:

Err_cbSave_Click:
If Err.Number = 9999 Then
MsgBox "This dumb error again?"
Else
'call the standard error handler
End If
Resume Exit_cbSave_Click
End Sub
 
Thanks Allen,
I've tried to make sense out of what I do here, but my custom error message
doesn't seem to work. Can you see why?

Private Sub cbSave_Click()
Err_cbSave_Click:
If Err.Number = 9999 Then
MsgBox "This dumb error again?"
Else

On Error GoTo Err_cbSave_Click
' Code to do something here
Exit_cbSave_Click:
Exit Sub
Err_cbSave_Click:
MsgBox Err.Description

End If
Resume Exit_cbSave_Click
End Sub
 
Sorry Allen, I didn't see that your additonal lines (catering for the custom
error message) actually starts from Err_cbSave_Click: , not from the very
beginning of Private Sub cbSave_Click()

But even so, it still skips my custom error message and jumps to the default
error meesage. I guess it's because my primary key data type is TEXT not
number, so the error is not considered as "Err.Number = 9999" ??

If this is the reason, what would be the correct notation for primary key
data type as text? Your kind help is much appreciated :)

Regards,
Sam
 
I'm not really following you, but if the error still occurs, is it an
engine-level error (about the field/record) rather than a VBA error? If so,
trap it with the Error event of the form.

9999 was just an example. Use whatever number you are encountering.
Err.Number is a Long.
 
Back
Top