• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1237
  • Last Modified:

3251: Operation is not supported for this type of object

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
Shawn
Asked:
Shawn
  • 8
  • 6
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What line of code exactly?

mx
0
 
ShawnAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
ShawnAuthor Commented:
ok, it breaks here:
If .Value <> .OldValue Then
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
ShawnAuthor Commented:
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
 
ShawnAuthor Commented:
triedit... If Not IsNull (.OldValue) Then
and still getting the same error on this line now
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Can you upload this db ?  And steps to reproduce the problem ?

mx
0
 
ShawnAuthor Commented:
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
 
ShawnAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
ShawnAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Good move!!  
mx
0
 
ShawnAuthor Commented:
thx :)

and thanks again. it really helps to bounce off ideas.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now