?
Solved

dbSeeChanges error while using OpenrecordSet

Posted on 2004-08-28
3
Medium Priority
?
419 Views
Last Modified: 2012-08-13
Okay.  After I added the code from http://support.microsoft.com/?kbid=197526 and ommiting what I didn't need.  I now receive the following error:

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an INDENTITY column.

Here is what the code looks like.  I commented out what I didn't need instead of just deleting it.  Also the Table Building is linked from an SQL 2000 backend.

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_Building_NotInList

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a customer, set the Response
        ' argument to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again."
    Else
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("Building", dbOpenDynaset)

        ' Ask the user to input a new Customer ID.
        ' Msg = "Please enter a unique 5-character" & vbCr & "Customer ID."
        ' NewID = InputBox(Msg)
        ' Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
        ' If the NewID already exists, ask for another new unique
        ' CustomerID
        ' Do Until Rs.NoMatch
        '   NewID = InputBox("Customer ID " & NewID & " already exists." & _
        '            vbCr & vbCr & Msg, NewID & " Already Exists")
        '   Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
        ' Loop
        ' Create a new record.
        Rs.AddNew
        ' Assign the NewID to the CustomerID field.
        ' Rs![CustomerID] = NewID
        ' Assign the NewData argument to the CompanyName field.
        Rs![Building] = NewData
        ' Save the record.
        Rs.Update

        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded

    End If

Exit_Building_NotInList:
       Exit Sub
Err_Building_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue

 End Sub
0
Comment
Question by:John Sheehy
[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
  • 2
3 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 11923061
Just change this line:
Set Rs = Db.OpenRecordset("Building", dbOpenDynaset)

to read:
Set Rs = Db.OpenRecordset("Building", dbOpenDynaset, dbSeeChanges)

That should resolve your problem (you need to include the dbSeeChanges when you are opening a recordset from an SQL Server linked table).
0
 

Author Comment

by:John Sheehy
ID: 11923070
That is awsome.  Thanks.

John
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11923106
No problem, glad I could help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

752 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