How to prevent identical values in pkey field?

S

Sam Kuo

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
 
B

Bob Howard

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.
 
A

Allen Browne

Fair enough, Bob.

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

Sam Kuo

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
 
A

Allen Browne

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
 
S

Sam Kuo

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
 
S

Sam Kuo

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
 
A

Allen Browne

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.
 
Top