If record exists, edit it?

[Maarkr]

I have a form that I want users to select their name, and if the record
already exists, edit it. Form opens in data entry mode. I'm using a before
and after update event:
Before:
Dim strPick As String
strPick = Nz(DLookup("UserName", "UserInfo", "[UserName] =
Form.[UserName]"), 0)
If strPick = 0 Then
Exit Sub
Else
If MsgBox("A record already exists with that name." & vbCrLf _
& "If you need to edit the info, click OK, otherwise click Cancel to
return to entry screen and close the program.", vbOKCancel) = vbCancel Then
Cancel = True 'don't enter it in table
Me.UserName.Undo
Else
'Cancel = True if I leave this, I can't change data entry mode
but it adds a record that I don't want
Exit Sub
End If
End If

After:
Dim strPick As String
strPick = Nz(DLookup("UserName", "UserInfo", "[UserName] =
Form.[UserName]"), 0)
Debug.Print strPick
If strPick = 0 Then
Exit Sub
Else
Form.DataEntry = False
DoCmd.FindRecord strPick
End If

Am I messing it up by trying to use before and after update to do this? I
tried variations with all under before update but that didn't work. Doing it
this way works but adds a record to the table-- if I try to cancel the before
update I can't shift the data entry mode. I hate before update code.
Thx

 

[Dirk Goldgar]

I have a form that I want users to select their name, and if the record
already exists, edit it. Form opens in data entry mode. I'm using a
before
and after update event:
Before:
Dim strPick As String
strPick = Nz(DLookup("UserName", "UserInfo", "[UserName] =
Form.[UserName]"), 0)
If strPick = 0 Then
Exit Sub
Else
If MsgBox("A record already exists with that name." & vbCrLf _
& "If you need to edit the info, click OK, otherwise click Cancel
to
return to entry screen and close the program.", vbOKCancel) = vbCancel
Then
Cancel = True 'don't enter it in table
Me.UserName.Undo
Else
'Cancel = True if I leave this, I can't change data entry mode
but it adds a record that I don't want
Exit Sub
End If
End If

After:
Dim strPick As String
strPick = Nz(DLookup("UserName", "UserInfo", "[UserName] =
Form.[UserName]"), 0)
Debug.Print strPick
If strPick = 0 Then
Exit Sub
Else
Form.DataEntry = False
DoCmd.FindRecord strPick
End If

Am I messing it up by trying to use before and after update to do this? I
tried variations with all under before update but that didn't work. Doing
it
this way works but adds a record to the table-- if I try to cancel the
before
update I can't shift the data entry mode. I hate before update code.

I don't see that you're undoing the control or the form before moving to the
existing record.

I think I'd use the combo's AfterUpdate event for the whole thing.
Something like this:

'----- start of revised (air) code -----
Private Sub UserName_AfterUpdate()

Dim strCriteria As String

With Me.UserName

strCriteria = "UserName = " & Chr(34) & .Value & Chr(34)

If Not IsNull(DLookup("UserName", "UserInfo", strCriteria)) _
Then

If MsgBox( _
"A record already exists with that name." & vbCrLf & _
"If you need to edit the info, click OK, otherwise
click " & _
"Cancel to return to entry screen and close the
program.", _
vbOKCancel) _
= vbCancel _
Then
.Undo
Me.Undo
Me.DataEntry = False
Me.Recordset.FindFirst strCriteria
End If

End If

End With

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

However, I much prefer not to overload combo boxes by using them both for
navigation and as bound controls.

 

[Maarkr]

thanks... you gave me some ideas so I posted the code in case anyone cares.
I ended up copying the form and opening it in edit mode since it was giving
me fits. Your statement was the best clue:

However, I much prefer not to overload combo boxes by using them both for
navigation and as bound controls.

Private Sub UserName_AfterUpdate()
Dim strPick As String

With Me.UserName
strPick = Nz(DLookup("UserName", "UserInfo", "[UserName] =
Form.[UserName]"), 0)
If strPick = 0 Then
Exit Sub
Else
If MsgBox("A record already exists with that name." & vbCrLf _
& "If you need to edit the info, click OK, otherwise click Cancel to
return to entry screen and close the program.", vbOKCancel) = vbCancel Then
.Undo
Me.Undo
Exit Sub
Else
.Undo
Me.Undo

DoCmd.OpenForm "UserEntryFrm2", acNormal, , "[UserName]=" &
strPick, acFormEdit
DoCmd.Close acForm, "UserEntryFrm"
End If
End If
End With
End Sub

I have a form that I want users to select their name, and if the record
already exists, edit it. Form opens in data entry mode. I'm using a
before
and after update event:
Before:
Dim strPick As String
strPick = Nz(DLookup("UserName", "UserInfo", "[UserName] =
Form.[UserName]"), 0)
If strPick = 0 Then
Exit Sub
Else
If MsgBox("A record already exists with that name." & vbCrLf _
& "If you need to edit the info, click OK, otherwise click Cancel
to
return to entry screen and close the program.", vbOKCancel) = vbCancel
Then
Cancel = True 'don't enter it in table
Me.UserName.Undo
Else
'Cancel = True if I leave this, I can't change data entry mode
but it adds a record that I don't want
Exit Sub
End If
End If

After:
Dim strPick As String
strPick = Nz(DLookup("UserName", "UserInfo", "[UserName] =
Form.[UserName]"), 0)
Debug.Print strPick
If strPick = 0 Then
Exit Sub
Else
Form.DataEntry = False
DoCmd.FindRecord strPick
End If

Am I messing it up by trying to use before and after update to do this? I
tried variations with all under before update but that didn't work. Doing
it
this way works but adds a record to the table-- if I try to cancel the
before
update I can't shift the data entry mode. I hate before update code.

I don't see that you're undoing the control or the form before moving to the
existing record.

I think I'd use the combo's AfterUpdate event for the whole thing.
Something like this:

'----- start of revised (air) code -----
Private Sub UserName_AfterUpdate()

Dim strCriteria As String

With Me.UserName

strCriteria = "UserName = " & Chr(34) & .Value & Chr(34)

If Not IsNull(DLookup("UserName", "UserInfo", strCriteria)) _
Then

If MsgBox( _
"A record already exists with that name." & vbCrLf & _
"If you need to edit the info, click OK, otherwise
click " & _
"Cancel to return to entry screen and close the
program.", _
vbOKCancel) _
= vbCancel _
Then
.Undo
Me.Undo
Me.DataEntry = False
Me.Recordset.FindFirst strCriteria
End If

End If

End With

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

However, I much prefer not to overload combo boxes by using them both for
navigation and as bound controls.

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

(please reply to the newsgroup)