Issue editing a recordset

[jonathansnyder via AccessMonster.com]

Hi folks,

I really stuck here, hoping you all can help.
I'm using a combo box to locate and display a record on a form. Code is as
follows:

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Tagger_ID] = '" & Me![Combo48] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Eval (Tagger_ID)
rs.Close
DoCmd.FindRecord Tagger_ID, , acEntire, , acSearchAll, , acCurrent
End Sub

If the record is edited, I would like to populate two fields within the table
indicating the date the record was updated, and the user name of the person
who updated it.

Code is a s follows:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim X As String
X = fOSUserName()
'nr is a global variable declared in module nr - sets nr equal to true if its
a new record
If nr = True Then
Exit Sub
End If
result = MsgBox("You are about to update the current record! Do you really
want to?", vbYesNo + vbCritical + vbDefaultButton2, "WARNING")
If result = vbNo Then
Me.Undo
Exit Sub
End If
Me.Update_Date = Date
Me.Update_Name = X

End Sub

If I edit the record and use the combo box to move to another record and
confirm that I want to update, the above code hangs up on the "me.update_date
= Date" line with the following error: "Update or cancelupdate without addnew
or edit". If I use the record selectors to move to another record after
editing and confirming, the code works fine and the update_date and user_name
fields are populated. I know this has something to do with the recordset
opened in the Private Sub Combo48_AfterUpdate() code above, but I cant seem
to figure out what to do to fix this.

Any and all help would be appreciated.
Thanks
Jonathan

 

[jonathansnyder via AccessMonster.com]

Bump

Can anyone help me? Please

Hi folks,

I really stuck here, hoping you all can help.
I'm using a combo box to locate and display a record on a form. Code is as
follows:

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Tagger_ID] = '" & Me![Combo48] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Eval (Tagger_ID)
rs.Close
DoCmd.FindRecord Tagger_ID, , acEntire, , acSearchAll, , acCurrent
End Sub

If the record is edited, I would like to populate two fields within the table
indicating the date the record was updated, and the user name of the person
who updated it.

Code is a s follows:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim X As String
X = fOSUserName()
'nr is a global variable declared in module nr - sets nr equal to true if its
a new record
If nr = True Then
Exit Sub
End If
result = MsgBox("You are about to update the current record! Do you really
want to?", vbYesNo + vbCritical + vbDefaultButton2, "WARNING")
If result = vbNo Then
Me.Undo
Exit Sub
End If
Me.Update_Date = Date
Me.Update_Name = X

End Sub

If I edit the record and use the combo box to move to another record and
confirm that I want to update, the above code hangs up on the "me.update_date
= Date" line with the following error: "Update or cancelupdate without addnew
or edit". If I use the record selectors to move to another record after
editing and confirming, the code works fine and the update_date and user_name
fields are populated. I know this has something to do with the recordset
opened in the Private Sub Combo48_AfterUpdate() code above, but I cant seem
to figure out what to do to fix this.

Any and all help would be appreciated.
Thanks
Jonathan

 

[jonathansnyder via AccessMonster.com]

Bump.

I still cant figure this out!

Bump

Can anyone help me? Please

Hi folks,

[quoted text clipped - 49 lines]

Thanks
Jonathan

 

[J_Goddard via AccessMonster.com]

Hi -

I think the problem is due to the fact that you are trying to change the form
data in the before update event of the form, which means it will want to do
the before update event again when moving to another record.

Try putting

Me.Update_Date = Date
Me.Update_Name = X

In the On Dirty event of the form, instead of Before Update.

HTH

John

Bump.

I still cant figure this out!

Bump

[quoted text clipped - 5 lines]

 

[John Spencer]

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

If Me.Dirty then Me.Dirty = False 'Force the record to be saved
Set rs = Me.RecordsetClone '<<<<<<
rs.FindFirst "[Tagger_ID] = '" & Me![Combo48] & "'"

If rs.NoMatch = False then
Me.Bookmark = rs.Bookmark
Eval (Tagger_ID) ' <<<< What is this line supposed to do
Else
Msgbox TaggerID & " is not in the current record set"
End If

rs.Close
'DoCmd.FindRecord Tagger_ID, , acEntire, , acSearchAll, , acCurrent
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim X As String
X = fOSUserName()
'nr is a global variable declared in module nr - sets nr equal to true if
its
a new record

If nr = True Then
Exit Sub
End If

result = MsgBox("You are about to update the current record! Do you really
want to?", vbYesNo + vbCritical + vbDefaultButton2, "WARNING")
If result = vbNo Then
Me.Undo
Exit Sub
End If

Me.Update_Date = Date
Me.Update_Name = X

End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Bump.

I still cant figure this out!

Bump

Can anyone help me? Please

Hi folks,

[quoted text clipped - 49 lines]

Thanks
Jonathan

 

[jonathansnyder via AccessMonster.com]

Thank You John,

You code worked, much obliged. I knew the issue was with the record set, but
could not figure it out.

Jonathan

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

If Me.Dirty then Me.Dirty = False 'Force the record to be saved
Set rs = Me.RecordsetClone '<<<<<<
rs.FindFirst "[Tagger_ID] = '" & Me![Combo48] & "'"

If rs.NoMatch = False then
Me.Bookmark = rs.Bookmark
Eval (Tagger_ID) ' <<<< What is this line supposed to do
Else
Msgbox TaggerID & " is not in the current record set"
End If

rs.Close
'DoCmd.FindRecord Tagger_ID, , acEntire, , acSearchAll, , acCurrent
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim X As String
X = fOSUserName()
'nr is a global variable declared in module nr - sets nr equal to true if
its
a new record

If nr = True Then
Exit Sub
End If

result = MsgBox("You are about to update the current record! Do you really
want to?", vbYesNo + vbCritical + vbDefaultButton2, "WARNING")
If result = vbNo Then
Me.Undo
Exit Sub
End If

Me.Update_Date = Date
Me.Update_Name = X

End Sub

Bump.

[quoted text clipped - 9 lines]