?
Solved

3251: Operation is not supported for this type of object

Posted on 2010-11-23
14
Medium Priority
?
1,164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 - Microsoft MVP, Access and Data Platform) earned 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.
Suggested Courses

801 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