You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table

[rocket2000]

Hi,
Could anyone explain why this happening with Access 2010? This code works with Access 2003 but since I've changed over to Access 2010 I am getting this error now even with the use of dbSeeChanges.

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

I have a command button on a form that runs this code for invoicing.

Private Sub cmdInvoice_Click()
Dim boolInvoiced As Boolean
Dim intResult As Integer

Select Case Me!optInvoiceOption

Case 1
CreateSalesInvoice
Case 2
CreateBICInvoice
Case 3
CreateCreditNote
End Select

DoCmd.Close

End Sub



The above code calls the following function below.


Public Function CreateNewInvoice(ByVal JobID As Long) As Boolean
On Error GoTo Err_CreateNewInvoice
' Author: Mark Casey
' Description: Creates a new invoice for each customer paying for the
' job.
' Arguments: JobID; The unique Job number.
' Amount.
' Created: 10/04/97
' Last Mod: 05/06/97

Dim dbFreight As Database, rstSalesInvoices As Recordset
Dim rstInvoiceDetails As Recordset
Dim qryJob As QueryDef, qryJobDetails As QueryDef
Dim qdCustomersForJob As QueryDef
Dim rstJob As Recordset, rstJobDetails As Recordset
Dim rstCustomersForJob As Recordset
Dim intInvoiceNo As Long, strCriteria As String



Set dbFreight = CurrentDb
Set rstSalesInvoices = dbFreight.OpenRecordset("Sales Invoices", dbOpenDynaset, dbSeeChanges)
Set rstInvoiceDetails = dbFreight.OpenRecordset("Sales Invoice Details", dbOpenDynaset, dbSeeChanges)

Set qryJob = dbFreight.QueryDefs("qryJobForInvoice")
Set qryJobDetails = dbFreight.QueryDefs("qryJobDetailsForInvoice")
Set qdCustomersForJob = dbFreight.QueryDefs("qryCustomersForJob")

qryJob.Parameters("SearchJobID") = JobID

qryJobDetails.Parameters("SearchJobID") = JobID

qdCustomersForJob.Parameters("SearchJobID") = JobID

Set rstJobDetails = qryJobDetails.OpenRecordset
Set rstCustomersForJob = qdCustomersForJob.OpenRecordset

' rstJob.MoveFirst

Do Until rstCustomersForJob.EOF
With rstSalesInvoices
.AddNew
!CustomerID = rstCustomersForJob!CustomerID
!SalesInvoiceDate = Forms![frmJobInvoiceOptions]![InvoiceDate]
!CompanyID = rstCustomersForJob!JobTypeID ' rstJob!CompanyID
'intInvoiceNo = !SalesInvoiceID
!CurrencyID = rstCustomersForJob!CurrencyID
!CurrencyRate = rstCustomersForJob!CurrencyRate
.Update
rstSalesInvoices.Move 0, rstSalesInvoices.LastModified
intInvoiceNo = !SalesInvoiceID

End With

strCriteria = "CustomerID = " & rstCustomersForJob!CustomerID
rstJobDetails.FindFirst strCriteria

Do Until rstJobDetails.NoMatch
With rstInvoiceDetails
.AddNew
!JobID = rstJobDetails!JobID
!SalesInvoiceID = intInvoiceNo
!AccountID = rstJobDetails!AccountID
!Description = rstJobDetails!Description
!Amount = rstJobDetails!Amount
!EuroAmount = rstJobDetails!EuroAmount
!VatRate = rstJobDetails!VatRate
.Update



End With
rstJobDetails.FindNext strCriteria
Loop
rstCustomersForJob.MoveNext
Loop

CreateNewInvoice = True

Exit_CreateNewInvoice:
Exit Function

Err_CreateNewInvoice:
MsgBox Err.Description
Resume Exit_CreateNewInvoice

End Function

Any assistance would be greatly appreciated,

 

[genomon]

Have you tried explicitly DIMming the database and recordset objects as "DAO.Database" and "DAO.Recordset"?

Beir bua agus beannacht!