.Update fails

Posted on 2006-03-21
Last Modified: 2008-03-06
Dear Expert,

Please  find the code below.

Function Acclev(prmMain)
Dim Accset As Recordset
Dim ctl As Control

Dim AccMatch As Boolean
AccMatch = False

Set Accset = Datadb.OpenRecordset("TBL-MENUS", dbOpenDynaset)
' Accset.Index = "PrimaryKey"      '******OMWB********
For Each ctl In Me.subOptions.Form
  If ctl.ControlType = acCommandButton Then
    With Accset
   '   .Seek "=", prmMain, ctl.Name  '******OMWB********
      Do While Not .EOF  '******OMWB********
      .MoveNext  '******OMWB********
      If Not .EOF Then
        If .Fields("mainopt") = prmMain And .Fields("subopt") = ctl.Name Then  '******OMWB********
            AccMatch = True '******OMWB********
            Exit Do
            'Exit Do '******OMWB********
        End If '******OMWB********
      End If
      Loop '******OMWB********
      If Not AccMatch Then '******OMWB********
        !mAINOPT = prmMain
        !SubOpt = ctl.Name
        .Bookmark = .LastModified
      End If
      If !Access > SECLEV Then
        ctl.Enabled = False
        ctl.ForeColor = MyGrey
        ctl.Enabled = True
        ctl.ForeColor = MyBlack
      End If
    End With
  End If
Set Accset = Nothing 'ge
If SECLEV < 99 Then
  Me.subOptions.Form.ShortcutMenuBar = ""
End If
End Function


TBL-MENUS is a access query that pulls data from ODBC link table(from oracle ). The update procedure fails  with the error code '3146' "ODBC -- call failed".

Could you please help . This is a really urgent one.

Thanks & regards,
Question by:saichak
    LVL 54

    Accepted Solution

    Make sure that the mandatory fields in the table are filled and also check the foreign keys to exist.


    Author Comment

    Dear Expert,

    I have checked it. Neither mandatory field nor the foreign key constraint.

    ANy other Hint please!!


    Author Comment

    Thanks Nic.

    The problem was actually very simple. On investigation it was found that duplicate records were going to be inserted i.e. primary key violation has been in place.

    Many thanks for you help again.

    LVL 54

    Expert Comment

    Glad it worked out, they are often "nasty" to trace in a .update setting.
    Thus for debugging I prefer the SQL query as that has the possibility to use the query editor to simulate the query.

    Success !


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    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…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    754 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

    20 Experts available now in Live!

    Get 1:1 Help Now