Solved

3251: Operation is not supported for this type of object

Posted on 2010-11-23
14
1,089 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - finding skipped numbers 11 22
Modal form 11 29
Access Open Report with SQL Parameter 11 28
aggregate query? 3 27
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 a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

816 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

7 Experts available now in Live!

Get 1:1 Help Now