Forms

[FiscalMike]

I am using a form for data entry to a table. Key is social security number.
The majority of the time the data entered will be a new record and will be
added to the table - no problem. However, occasionally the data to be
entered is actually an update to an existing record (data entry person cannot
distinguish between new or update). I would like to use the SSN field
(primary key) as soon as it is entered, to determine if the record already
exists, and , if so, populate the record's fields on the form. If the key
doesn't already exist, then allow data entry to continue normally.

I can successfully use DLOOKUP to interrogate the table, but have been
unsuccessful finding a way to tell Access to not attempt to add the record.
Access will not allow the update because it will create a duplicate key. I
have tried executing the code on the "before update" and the "on exit"
events but with no success. My code follows.

Suggestions appreciated!

Private Sub SearchSSN_Exit(Cancel As Integer)
Dim x As Variant

x = DLookup("[Sender SSN]", "SAF_Sender", "[Sender SSN]= '" _
& Forms!SAF_Sender_T!searchSSN & "'")

On Error GoTo CustID_Err

If Not IsNull(x) Then
Beep

MsgBox "The Sender has already been added. "
vbOKCancel , "Sender Already Entered"

DoCmd.CancelEvent

DoCmd.FindRecord x

End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit
End Sub

 

[Dirk Goldgar]

I am using a form for data entry to a table. Key is social security
number.
The majority of the time the data entered will be a new record and will be
added to the table - no problem. However, occasionally the data to be
entered is actually an update to an existing record (data entry person
cannot
distinguish between new or update). I would like to use the SSN field
(primary key) as soon as it is entered, to determine if the record already
exists, and , if so, populate the record's fields on the form. If the key
doesn't already exist, then allow data entry to continue normally.

I can successfully use DLOOKUP to interrogate the table, but have been
unsuccessful finding a way to tell Access to not attempt to add the
record.
Access will not allow the update because it will create a duplicate key.
I
have tried executing the code on the "before update" and the "on exit"
events but with no success. My code follows.

Suggestions appreciated!

Private Sub SearchSSN_Exit(Cancel As Integer)
Dim x As Variant

x = DLookup("[Sender SSN]", "SAF_Sender", "[Sender SSN]= '" _
& Forms!SAF_Sender_T!searchSSN & "'")

On Error GoTo CustID_Err

If Not IsNull(x) Then
Beep

MsgBox "The Sender has already been added. "
vbOKCancel , "Sender Already Entered"

DoCmd.CancelEvent

DoCmd.FindRecord x

End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit
End Sub

I'd suggest you use the BeforeUpdate event instead of Exit, because Exit
fires even if you tab through the field on your way to another. Here's an
example of *untested* code you might use:

'----- start of "air code" -----
Private Sub SearchSSN_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Handler

If Not IsNull(Me.SearchSSN) Then

With Me.RecordsetClone

.FindFirst "[Sender SSN]= '" & Me.SearchSSN & "'"

If Not .NoMatch Then

Cancel = True

If MsgBox( _
"This Sender SSN has already been entered. " & _
"Do you want to go to that record?", _
vbExclamation+vbYesNo, _
"Already On File") _
= vbYes _
Then
Me.SearchSSN.Undo
Me.Undo
Me.Bookmark = .Bookmark
End If

End If

End If

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code -----

If that doesn't work -- after fixing any minor bugs -- you may have to use
the control's AfterUpdate event instead.

 

[FiscalMike]

Dirk,
Works great, thanks! I guess I'm more of a rookie than I realized. I'm not
familiar with "me.". Where can I learn more about that?

Mike

I am using a form for data entry to a table. Key is social security
number.
The majority of the time the data entered will be a new record and will be
added to the table - no problem. However, occasionally the data to be
entered is actually an update to an existing record (data entry person
cannot
distinguish between new or update). I would like to use the SSN field
(primary key) as soon as it is entered, to determine if the record already
exists, and , if so, populate the record's fields on the form. If the key
doesn't already exist, then allow data entry to continue normally.

I can successfully use DLOOKUP to interrogate the table, but have been
unsuccessful finding a way to tell Access to not attempt to add the
record.
Access will not allow the update because it will create a duplicate key.
I
have tried executing the code on the "before update" and the "on exit"
events but with no success. My code follows.

Suggestions appreciated!

Private Sub SearchSSN_Exit(Cancel As Integer)
Dim x As Variant

x = DLookup("[Sender SSN]", "SAF_Sender", "[Sender SSN]= '" _
& Forms!SAF_Sender_T!searchSSN & "'")

On Error GoTo CustID_Err

If Not IsNull(x) Then
Beep

MsgBox "The Sender has already been added. "
vbOKCancel , "Sender Already Entered"

DoCmd.CancelEvent

DoCmd.FindRecord x

End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit
End Sub

I'd suggest you use the BeforeUpdate event instead of Exit, because Exit
fires even if you tab through the field on your way to another. Here's an
example of *untested* code you might use:

'----- start of "air code" -----
Private Sub SearchSSN_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Handler

If Not IsNull(Me.SearchSSN) Then

With Me.RecordsetClone

.FindFirst "[Sender SSN]= '" & Me.SearchSSN & "'"

If Not .NoMatch Then

Cancel = True

If MsgBox( _
"This Sender SSN has already been entered. " & _
"Do you want to go to that record?", _
vbExclamation+vbYesNo, _
"Already On File") _
= vbYes _
Then
Me.SearchSSN.Undo
Me.Undo
Me.Bookmark = .Bookmark
End If

End If

End If

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code -----

If that doesn't work -- after fixing any minor bugs -- you may have to use
the control's AfterUpdate event instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Works great, thanks! I guess I'm more of a rookie than I realized. I'm
not
familiar with "me.". Where can I learn more about that?

The keyword "Me" is a reference to the class object that contains the
running code. So for code running on a form, Me refers to that form; for
code running on a report, Me refers to that report, and for code running in
a user-defined class module, Me refers to the current instance of that
class.

In the code I posted, I used Me as a handle to refer to the properties and
methods of the Form object. I also habitually use Me to qualify the names
of controls on the form when I refer to them, though it's not generally
required. I do it to make sure the references can't be confused with any
other object or variable of the same name.