Code won't update

[TeeSee]

The following code runs and the variables contain the desired data but
the underlying table doesn't get updated. Can anyone see the error of
my ways? There are no error messages with this code. It just does
nothing!
This has been adapted from Graham Mandenos' post of some years ago
which was run under the Form_BeforeUpdate as you can see. Can this
type of code also be run from the text box control BeforeUpdate as
well since I am only capturing a single field?
Thanks as always

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim CtlSource As Variant, Ctl As Control, intCtl As Integer
Dim db As DAO.Database, rsMMhist As DAO.Recordset
On Error GoTo Form_BeforeUpdate_Err
Set db = CurrentDb()
Set rsMMhist = db.OpenRecordset("tblMaterialMasterHistory")

CtlSource = Ctl.ControlSource

If ListPrice = ListPrice.OldValue Then
'do nothing (Still working on it)
Else
'field has been changed - add record to history
Debug.Print "TestPosition"
rsMMhist.AddNew
rsMMhist!Originator = CurrentUser()
rsMMhist!ChngeDate = Now()
rsMMhist!oldlistprice = ListPrice.OldValue
rsMMhist!newListPrice = ListPrice.Value
rsMMhist!ControlSource = CtlSource
Debug.Print ListPrice.OldValue ' ********* correct value
here ********

rsMMhist.Update

End If

Form_BeforeUpdate_Exit:
On Error Resume Next
rsMMhist.Close
Exit Sub

Form_BeforeUpdate_Err:
If Err = 2455 Then
MsgBox Error$
Cancel = True
Resume Form_BeforeUpdate_Exit
End If

End Sub

 

[Tom van Stiphout]

On Wed, 7 Jan 2009 17:05:18 -0800 (PST), TeeSee

I'm not sure how you are debugging this. Did you set a breakpoint at
the top and are stepping through it a line at a time?

The only odd code I see is the error handler. For now take out the IF
statement and always display the error messagebox.

-Tom.
Microsoft Access MVP

 

[J]

It's late so I may not be seeing it but you have the following

ctlSource = ctl.ControlSource

but you never assigned an actual control on your form to ctl (i.e ctl
= [ListPrice]). I personally had some trouble assigning a control to
ctl so I bypassed the that variable and just did this

ctlSource = Me!List3.ControlSource (in your case it looks like you'd
use ListPrice instead of List3)

Here is the code I used to test it out:
----------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Command2_Click()
Dim CtlSource As Variant, Ctl, intCtl As Integer
Dim db As DAO.Database, rsMMhist As DAO.Recordset
On Error GoTo Form_BeforeUpdate_Err
' Set db = CurrentDb()
' Set rsMMhist = db.OpenRecordset("tblMaterialMasterHistory")
Ctl = Form.Controls("List3")
' Debug.Print Form.Controls.Item
CtlSource = List3.ControlSource
' If ListPrice = ListPrice.OldValue Then
'do nothing (Still working on it)
' Else
'field has been changed - add record to history
Debug.Print "TestPosition"
Debug.Print "addnew"
Debug.Print CurrentUser()
Debug.Print Now()
Debug.Print "ListPrice.OldValue: " & 1
Debug.Print "ListPrice.Value: " & 2
Debug.Print CtlSource
'Debug.Print ListPrice.OldValue ' ********* correct
valuehere ********
Debug.Print "Update"
' End If
Form_BeforeUpdate_Exit:
On Error Resume Next
' rsMMhist.Close
Exit Sub
Form_BeforeUpdate_Err:
' If Err = 2455 Then
MsgBox Error$
Cancel = True
Resume Form_BeforeUpdate_Exit
' End If
End Sub
----------------------------------------------------------------------------------------------------------------------------------------------

I commented out all of the recordset code and replaced it with
Debug.Prints since I don't have your table or form design. Also like
Tom said take out the IF statement in your error handler because with
it you ignore all errors that are not '2455' and quietly exit your
subroutine.

Let me know if that helps out,
J

 

[TeeSee]

It's late so I may not be seeing it but you have the following

ctlSource = ctl.ControlSource

but you never assigned an actual control on your form to ctl (i.e ctl
= [ListPrice]). I personally had some trouble assigning a control to
ctl so I bypassed the that variable and just did this

ctlSource = Me!List3.ControlSource (in your case it looks like you'd
use ListPrice instead of List3)

Here is the code I used to test it out:
---------------------------------------------------------------------------­-------------------------------------------------------------------
Private Sub Command2_Click()
    Dim CtlSource As Variant, Ctl, intCtl As Integer
    Dim db As DAO.Database, rsMMhist As DAO.Recordset
    On Error GoTo Form_BeforeUpdate_Err
'    Set db = CurrentDb()
'    Set rsMMhist = db.OpenRecordset("tblMaterialMasterHistory")
    Ctl = Form.Controls("List3")
'    Debug.Print Form.Controls.Item
    CtlSource = List3.ControlSource
'        If ListPrice = ListPrice.OldValue Then
            'do nothing (Still working on it)
'            Else
            'field has been changed - add record to history
           Debug.Print "TestPosition"
            Debug.Print "addnew"
            Debug.Print CurrentUser()
            Debug.Print Now()
            Debug.Print "ListPrice.OldValue: " & 1
            Debug.Print "ListPrice.Value: " & 2
            Debug.Print CtlSource
            'Debug.Print ListPrice.OldValue ' ********* correct
valuehere ********
            Debug.Print "Update"
'        End If
Form_BeforeUpdate_Exit:
    On Error Resume Next
'    rsMMhist.Close
    Exit Sub
Form_BeforeUpdate_Err:
'    If Err = 2455 Then
    MsgBox Error$
    Cancel = True
    Resume Form_BeforeUpdate_Exit
'    End If
End Sub
---------------------------------------------------------------------------­-------------------------------------------------------------------

I commented out all of the recordset code and replaced it with
Debug.Prints since I don't have your table or form design. Also like
Tom said take out the IF statement in your error handler because with
it you ignore all errors that are not '2455' and quietly exit your
subroutine.

Let me know if that helps out,
J

Thank you both for your responses. I have learned a great deal on this
one. The code now functions as expected except I now receive an error
as follows.
Error 3265 (Item not found in this collection)
Using some new debugging skills learned from both your responses The
value of CurrentUser() prints as Admin when debug.print handles it but
on the very next line when applying it to the history table field it
errors out.
How can it be recognized and have a vlue on one line and not be
recocognized on the next. How do I deal with this error.

Best regards

 

[J]

On Jan 7, 8:05 pm, TeeSee <[email protected]> wrote:

It's late so I may not be seeing it but you have the following

ctlSource = ctl.ControlSource

but you never assigned an actual control on your form to ctl (i.e ctl
= [ListPrice]). I personally had some trouble assigning a control to
ctl so I bypassed the that variable and just did this

ctlSource = Me!List3.ControlSource (in your case it looks like you'd
use ListPrice instead of List3)

Here is the code I used to test it out:
--------------------------------------------------------------------------- ­-------------------------------------------------------------------
Private Sub Command2_Click()
    Dim CtlSource As Variant, Ctl, intCtl As Integer
    Dim db As DAO.Database, rsMMhist As DAO.Recordset
    On Error GoTo Form_BeforeUpdate_Err
'    Set db = CurrentDb()
'    Set rsMMhist = db.OpenRecordset("tblMaterialMasterHistory")
    Ctl = Form.Controls("List3")
'    Debug.Print Form.Controls.Item
    CtlSource = List3.ControlSource
'        If ListPrice = ListPrice.OldValue Then
            'do nothing (Still working on it)
'            Else
            'field has been changed - add record to history
           Debug.Print "TestPosition"
            Debug.Print "addnew"
            Debug.Print CurrentUser()
            Debug.Print Now()
            Debug.Print "ListPrice.OldValue: " & 1
            Debug.Print "ListPrice.Value: " & 2
            Debug.Print CtlSource
            'Debug.Print ListPrice.OldValue ' ********* correct
valuehere ********
            Debug.Print "Update"
'        End If
Form_BeforeUpdate_Exit:
    On Error Resume Next
'    rsMMhist.Close
    Exit Sub
Form_BeforeUpdate_Err:
'    If Err = 2455 Then
    MsgBox Error$
    Cancel = True
    Resume Form_BeforeUpdate_Exit
'    End If
End Sub
--------------------------------------------------------------------------- ­-------------------------------------------------------------------

I commented out all of the recordset code and replaced it with
Debug.Prints since I don't have your table or form design. Also like
Tom said take out the IF statement in your error handler because with
it you ignore all errors that are not '2455' and quietly exit your
subroutine.

Let me know if that helps out,
J

Thank you both for your responses. I have learned a great deal on this
one. The code now functions as expected except I now receive an error
as follows.
Error 3265 (Item not found in this collection)
Using some new debugging skills learned from both your responses The
value of CurrentUser() prints as Admin when debug.print handles it but
on the very next line when applying it to the history table field it
errors out.
How can it be recognized and have a vlue on one line and not be
recocognized on the next. How do I deal with this error.

Best regards

From your response its my understanding that you have the following:

....
Debug.Print CurrentUser()
rsMMhist!Originator = CurrentUser()
....

Which writes "Admin" to the debug console but errors on the second
line. If that is the case my first guess would be to make sure that a
'Originator' field exists in tblMaterialMasterHistory and if it does
make sure you've spelled it correctly in the code. Also if it exists
and you've spelled it correctly then check each of the other fields
and spellings in your code. Of course if it doesn't exist then you'd
need to add that field to your table. Hope that helps.

J