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

[PrimoSteve]

Hi,

Could anyone explain why this happening with Access 2010? This code works with Access 2003 but since I've changed over to Access 2012 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,

 

[Andrzejek]

>The above code calls the following function below.
No, it does not.
You do not show the code for your procedures:
[ul][li]CreateSalesInvoice[/li]
[li]CreateBICInvoice[/li]
[li]CreateCreditNote[/li]
[/ul]
Which line of code creates the error: use dbSeeChanges ?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

>SQL Server table that has an IDENTITY column

Let's say, if [tt]Sales Invoices[/tt] table's [tt]CustomerID[/tt] field is declared as an IDENTITY column, ...

Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the server. A user generally cannot insert a value into an identity column. Identity column can be used to uniquely identify the rows in the table.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[PrimoSteve]

Hi Andy,

Sorry, see below

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



Public Sub CreateSalesInvoice()
Dim intResult As Integer


If Forms![Jobs]![Invoiced] = True Then
intResult = MsgBox("An invoice already exists for this job", vbYesNo, CYBCAPT)
Exit Sub
End If




intResult = MsgBox("Are you sure you want to create a Sales Invoice?", vbYesNo, CYBCAPT)

If intResult = vbYes Then
' boolInvoiced = CreateNewInvoice(Me!JobID)
If CreateNewInvoice(Forms![Jobs]![JobID]) Then
Forms![Jobs]!Invoiced = True
DoCmd.openForm "Sales Invoices"
End If
Else
MsgBox "Sales invoice was not created", , CYBCAPT
End If
End Sub

This is working on my Access 2003 and I have tried running it on another desktop using Access 2012 but it's throwing out the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column error

Thanks

 

[PrimoSteve]

The error happens after I click the Yes command button to create a new invoice

Public Sub CreateSalesInvoice()
Dim intResult As Integer


If Forms![Jobs]![Invoiced] = True Then
intResult = MsgBox("An invoice already exists for this job", vbYesNo, CYBCAPT)
Exit Sub
End If




intResult = MsgBox("Are you sure you want to create a Sales Invoice?", vbYesNo, CYBCAPT)

 

[Andrzejek]

Like I said, if - for example - CustomerID is an IDENTITY field, and you try to write to it:

With rstSalesInvoices
    .AddNew[red]
    !CustomerID = rstCustomersForJob!CustomerID[/red]
    !SalesInvoiceDate = Forms![frmJobInvoiceOptions]![InvoiceDate]
    !CompanyID = rstCustomersForJob!JobTypeID ' rstJob!CompanyID
    'intInvoiceNo = !SalesInvoiceID
    !CurrencyID = rstCustomersForJob!CurrencyID
    !CurrencyRate = rstCustomersForJob!CurrencyRate
    .Update
    ...
End With

You canNOT :-(

BTW -
intResult = MsgBox("An invoice already exists for this job", [red]vbYesNo[/red], CYBCAPT)

Wouldn't be easier to just:
Call MsgBox("An invoice already exists for this job", [blue]vbOKOnly[/blue], CYBCAPT)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[PrimoSteve]

Hi Andy,

I understand what your saying but why does this work in Access 2003 and not in 2012? It's still the same FE and still inked to the same BE SQL serve. It must have something to do with the Access version?

Thanks

 

[Andrzejek]

Who knows...?
Maybe Access 2003 used a brute force, and Access 2012 cannot do that
You just have to play nice and modify your code.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[PrimoSteve]

Hi Andy,

Thanks for the feedback I'm only a newbie to Access and coding and didn't write this code. I thought I could easily add a line here so learning how to use Recordset.

 

[Andrzejek]

I would first find out which table has the IDENTITY field and focus on this field in your code.
It may be as easy as commenting out one line of code...


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson