Replace Access error message with more descriptive message.

S

Sandy Burgess

Back again. This time I am having problems with an input form for new
patients. The form can not access existing patients. Similar problem as
previous post. Form should not allow duplicate SSN's

Table called Patient Data contains SSN (text field). There also is a field
call ID that is numeric. I have the following event procedure coded to the
before update .

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub


When a duplicate SSN is entered on the form I get the msg box with "SSN
exists. Verify SSN". When I hit ok. I get an Access Popup that states “The
value in the field or record violates the validation rule for the record or
field ………â€
How can I get rid this second popup? I got rid of the second popup on a
modify existing patient form with the above code but this code is not working
on the input form. Thanks for your help.
 
M

Marshall Barton

Sandy said:
Back again. This time I am having problems with an input form for new
patients. The form can not access existing patients. Similar problem as
previous post. Form should not allow duplicate SSN's

Table called Patient Data contains SSN (text field). There also is a field
call ID that is numeric. I have the following event procedure coded to the
before update .

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub


When a duplicate SSN is entered on the form I get the msg box with "SSN
exists. Verify SSN". When I hit ok. I get an Access Popup that states “The
value in the field or record violates the validation rule for the record or
field ………”
How can I get rid this second popup? I got rid of the second popup on a
modify existing patient form with the above code but this code is not working
on the input form.
Click to expand...


I suspect that the update is occuring because the focus is
moving but the bad ssn is still in the control. In any
case, I recommend that you change the Sel lines to
Me.textSSN.Undo
 
S

Sandy Burgess

Need more help. I don't write code so I need very specific instructions. I
changed the code to

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.Undo = 0
Me.textSSN.Undo = Len(Me.textSSN)
End If

End Sub


Now I'm getting a compile error. I'm sure I didn't change the code correctly.

I want to get this error after the ssn is entered and the tab key moves the
cursor(focus) to the next field.

What do I do now?
--
Sandy Burgess


Marshall Barton said:
Sandy said:
Back again. This time I am having problems with an input form for new
patients. The form can not access existing patients. Similar problem as
previous post. Form should not allow duplicate SSN's

Table called Patient Data contains SSN (text field). There also is a field
call ID that is numeric. I have the following event procedure coded to the
before update .

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub


When a duplicate SSN is entered on the form I get the msg box with "SSN
exists. Verify SSN". When I hit ok. I get an Access Popup that states “The
value in the field or record violates the validation rule for the record or
field ………â€
How can I get rid this second popup? I got rid of the second popup on a
modify existing patient form with the above code but this code is not working
on the input form.
Click to expand...


I suspect that the update is occuring because the focus is
moving but the bad ssn is still in the control. In any
case, I recommend that you change the Sel lines to
Me.textSSN.Undo
Click to expand...
 
S

Sandy Burgess

--
Sandy Burgess
Ignore previous post. I figured out how to implement your suggestion but
nothing has changed. After entering an existing SSN and tabing to the next
field, I get the msg box with "SSN exists. Verify SSN". When I hit ok. I
get an Access Popup that states “The value in the field or record violates
the validation rule for the record or
field ………â€

Back to my original question - How can I get rid this second popup?

Sandy


:

I suspect that the update is occuring because the focus is
moving but the bad ssn is still in the control. In any
case, I recommend that you change the Sel lines to
Me.textSSN.Undo
Click to expand...
Sandy said:
Back again. This time I am having problems with an input form for new
patients. The form can not access existing patients. Similar problem as
previous post. Form should not allow duplicate SSN's

Table called Patient Data contains SSN (text field). There also is a field
call ID that is numeric. I have the following event procedure coded to the
before update .

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub


When a duplicate SSN is entered on the form I get the msg box with "SSN
exists. Verify SSN". When I hit ok. I get an Access Popup that states “The
value in the field or record violates the validation rule for the record or
field ………â€
How can I get rid this second popup? I got rid of the second popup on a
modify existing patient form with the above code but this code is not working
on the input form.
Click to expand...
Click to expand...
 
M

Marshall Barton

Sandy said:
Need more help. I don't write code so I need very specific instructions. I
changed the code to

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.Undo = 0
Me.textSSN.Undo = Len(Me.textSSN)
End If

End Sub
Click to expand...


That code should be:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

If IsNull(DLookup("SSN", "Patient Data", strCriteria)) _
Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.Undo
End If

End Sub

If it doesn't work, post back with what did happen along
with a Copy/Paste of the code after any changes you might
have made.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check for duplicate before update 9
Error Message with Duplicate Values 10
Forms 3
IF statement help. 1
Can't Find Forms Error with Macros 2
Strange Error Message 2
Check for duplicates with multiple criteria 1
Need more help on preventing duplicates 1

Top