Solved

3251: Operation is not supported for this type of object

Posted on 2010-11-23
14
1,086 Views
Last Modified: 2012-05-10
I have a module that is going to track modifications but am getting the above error even before I add an insert statement.

what is wrong  here?
Sub AuditTrail(frm As Form, recordid As Control)

  'Track changes to data.

  'recordid identifies the pk field's corresponding

  'control in frm, in order to id record.

  Dim ctl As Control

  Dim varBefore As Variant

  Dim varAfter As Variant

  Dim strControlName As String

  Dim strSQL As String

  On Error GoTo ErrHandler

  'Get changed values.

  For Each ctl In frm.Controls

    With ctl

    'Avoid labels and other controls with Value property.

    If .ControlType = acTextBox Then

      If .Value <> .OldValue Then

        varBefore = .OldValue

        varAfter = .Value

        strControlName = .Name

        'Build INSERT INTO statement for sp.



  

      End If

    End If

    End With

  Next

  Set ctl = Nothing

  Exit Sub



ErrHandler:

  MsgBox Err.Description & vbNewLine _

   & Err.Number, vbOKOnly, "Error"

End Sub

Open in new window

0
Comment
Question by:Shawn
  • 8
  • 6
14 Comments
 
LVL 75
ID: 34202001
What line of code exactly?

mx
0
 
LVL 1

Author Comment

by:Shawn
ID: 34202010
it doesn't show where. a message box simply appears with the ok option.

It is definately after the stored procedure as that works.

I'm thinking around here:
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub

0
 
LVL 75
ID: 34202083
To find the exact line of code, Try this:

VBA editor >>Tools>>Options>>General Tab
Set Error Trapping to 'Break On All Errors'

This will cause a break on ANY error.  It can be a bit of a pain ... getting to the exact point you want ... depending if you have an On Error Resume Next statements, etc.

Be SURE to set this back after troubleshooting.

mx
0
 
LVL 1

Author Comment

by:Shawn
ID: 34202233
ok, it breaks here:
If .Value <> .OldValue Then
0
 
LVL 75
ID: 34202248
Kind of what I figured .... maybe this .. you need to Test for a Non Null OldValue first.

    If .ControlType = acTextBox Then
       If Not IsNull (.OldValue) Then      
         
         If .Value <> .OldValue Then
           varBefore = .OldValue
           varAfter = .Value
           strControlName = .Name
           'Build INSERT INTO statement for sp.
          End If
      Else
           ' Old Value IsNull  case - not sure what you would do here
      End If       ' Not Null OldValue Test
   
End If   ' Control Type
0
 
LVL 1

Author Comment

by:Shawn
ID: 34202272
i don't get it...the Oldvalue is not null in this case. In this eg it shows an email address.

the line before it however
If .ControlType = acTextBox Then

the control type doesn't seem to show a value
0
 
LVL 1

Author Comment

by:Shawn
ID: 34202292
triedit... If Not IsNull (.OldValue) Then
and still getting the same error on this line now
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75
ID: 34202492
Can you upload this db ?  And steps to reproduce the problem ?

mx
0
 
LVL 1

Author Comment

by:Shawn
ID: 34208912
uploading a db might take a while. in the meantime....

I think I found the "cause" http://support.microsoft.com/kb/207836

now to test a workaround
0
 
LVL 1

Author Comment

by:Shawn
ID: 34208983
it seems oldvalue won't work on a form based on a query with tables in a one to many relationship...which is my case.

microsoft's example seems straightforward though a  little simple

any idea how to modify my code?
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 34209018
wow. good find.

Hate to say it, but I would rethink the one to many scenario.  There should be no reason to need that.  There is always another way.  Consider that.  Then you can use the code you have.

mx
0
 
LVL 1

Author Comment

by:Shawn
ID: 34209108
i think you're right about the one to many....I don't actually need it. It was used to display address info for a contact but there are better ways to do that.

for the mo I have added a couple lines to ignore that error as the procedure still works.
ErrHandler:
If Err.Number = 3251 Then
Resume Next
Else
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End If

I have on my to do list now to get rid of the one to many.

thanks mx
0
 
LVL 75
ID: 34209116
Good move!!  
mx
0
 
LVL 1

Author Comment

by:Shawn
ID: 34209128
thx :)

and thanks again. it really helps to bounce off ideas.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now