Solved

3251: Operation is not supported for this type of object

Posted on 2010-11-23
14
1,073 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 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

18 Experts available now in Live!

Get 1:1 Help Now