×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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,

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

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

---- Andy

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

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

>SQL Server table that has an IDENTITY column

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

Quote (Google)

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

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

(OP)
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

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

(OP)
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)

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

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

CODE

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

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

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

---- Andy

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

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

(OP)
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

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

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

---- Andy

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

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

(OP)
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.

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

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...
pc2

---- Andy

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close