Transaction problem

[Rotsey]

Hi,

I am writing some code that uses DAO code to write to access table
that is linked to a SQL table using transaction control.

So I am writing a invoice record to a invoice table in a loop

In the loop I have to check if a invoice already exists with the invoice
number
I supply before writing the record.

The problem is that when I write the first record all is ok but the second
write is the same number as the first because it happens to be for the same
customer etc
and this causes a runtime error which says "execution cancelled"

You see it seems to fail when I do my check (which is a lookup on the
same table) on the record I have already written in the transaction before.

Any ideas why??

rotsey

 

[Bill Mosca, MS Access MVP]

I'd guess that all you need to do is do the checking before the loop instead
of doing it on each iteration of the loop, but it would be much more helpful
if you posted the code you are using.

 

[Rotsey]

Well the code is quite long. But the crux of it is like this.


Public Sub ProcessInvoiceData(CustNoDetails)

objws.Begintrans

For cntr = 1 to NmberOfInvoiceToCreate

invno = CreateInvoiceNumber(CustNoDetails(cntr))

SQL = "SELECT * FROM Invoices WHERE invNumber = " & invno
Set rs2 = gobjDBodbc.OpenRecordset(sql, dbOpenSnapshot)
IT BOMBS HERE THE 2ND ITERATION AS THE invno IS THE SAME AS THE FIRST
INTERATION

if rs2.bof then
CreateInvoice(invno)
Endif

Next

objws.CommiTrans


If you need more info let me know.

rotsey