vbcode in access

[Guest_imported]

hello!
i have a student table and this is the vb code i put in the code builder behind the add record and close buttons. I have 2 problems.

1) It is not displaying the message i put in it. i.e if i try to put a duplicate record (it says "you cant go to the specified record".) i think it has to say "The student ID has to be unique.

2) Whenever i close the form and comeback, it won't refresh.
I have even got #delete before.

As, I am new to all this, Could anybody please try to figure out what's wrong with the code? Thanks a lot. The code is:


Private Sub Add_Click()
On Error GoTo Err_Add_Click
Dim i

DoCmd.GoToRecord , , acNewRec

Exit_Add_Click:
Exit Sub

Err_Add_Click:

If Err.Number = 3022 Then
i = MsgBox("The Student ID has to be unique", vbOKOnly, "Student Database &quot
Else
MsgBox Err.Description
Resume Exit_Add_Click
End If

End Sub

Private Sub Close_Click()
On Error GoTo Err_Close_Click


DoCmd.Close

Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub

 

[fmbpcsup]

This message could be caused if your form properties has Allow Additions set to No instead of Yes.

 

[LarryDeLaruelle]

First, if the message box is not displaying it is probably because you have assigned it to a variable "i"

Change your code to:

If Err.Number = 3022 Then
MsgBox("The Student ID has to be unique",
vbOKOnly, "Student Database &quot
Else
MsgBox Err.Description
Resume Exit_Add_Click
End If

Second, I would offer the user the option to cancel or continue with a vbOkCancel.

If the user selects Cancel then issue an undo command to flush the contents of the form. If the user selects Ok then just do an Exit Sub.

I would suggest this:


If Err.Number = 3022 Then
If MsgBox("The Student ID has to be unique. Click on OK to change or Click on Cancel to erase.",
vbOKOnly, "Student Database &quot = vbCancel then
DoCmd.Undo
Else
Exit Sub
End If
Else
MsgBox Err.Description
Resume Exit_Add_Click
End If

Hope this helps.
Larry De Laruelle
larry1de@yahoo.com

 

[Guest_imported]

thanks larry for your help!

but it says "method or datamember not found at DoCmd.Undo
It is pointing to Undo. Hope you can figure it out for me..
THANKS AGAIN

 

[LarryDeLaruelle]

Guest:

Saw your last post, you're welcome. I have receive a lot of assistance from this site and I'm glad when I can contribute.

I should have given you the full syntax of the undo. Here is what I frequently use:

If Me.Dirty Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

Later.
Larry De Laruelle
larry1de@yahoo.com