Issue editing a recordset

  • Thread starter Thread starter jonathansnyder via AccessMonster.com
  • Start date Start date
J

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
 
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
Click to expand...
 
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
Click to expand...
[quoted text clipped - 5 lines]
Click to expand...
 
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
..

jonathansnyder via AccessMonster.com said:
Bump.

I still cant figure this out!
Bump

Can anyone help me? Please
Hi folks,
Click to expand...
[quoted text clipped - 49 lines]
Thanks
Jonathan
Click to expand...
Click to expand...
Click to expand...
 
Thank You John,

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

Jonathan

John said:
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.
Click to expand...
[quoted text clipped - 9 lines]
Click to expand...
 
Back
Top