Saving last TransactionID to another table

[PMB]

Thank you in advance for any and all assistance.

I'm trying to keep my numbers for transactions sequential. I clear my
monthly transactions each month and store them in a general transactions
table. My problem is, everytime I clear the table, it wants to start back at
Zero, which conflicts with records that are already created.

Is there a way to create a second table and store the value of the last
transaction and pull it into the transactions for the new month?

I've tried psuedo code of; (neither field is AutoNumber)

If IsNull(TransactionID) Then
TransactionID = DMax("tblNewNumber", "TransactionID")+1
Else
TransactionID = DMax("tblTransactionsNew", "TransactionID")+1
End If

But, it doesn't work all the time. HELP

Also, how can I save the last transaction into the new table so that the
transactionID's are sequential?
Michael

[Larry Linson]

When you Compact, it reinitializes AutoNumber to one more than the highest
value in the AutoNumber field (if you are using numeric, not GUID) -- thus,
if your clear the table, the highest used AutoNumber in that table is 0, so
it reinitializes to 1.

When you use DMax, it returns the highest number you have used in that
field -- thus, if all your records are gone, it returns 0, so you will start
with 1.

If you don't want that behavior, then you'll have to use a different
approach... perhaps have your own TableInfo table with table names and
highest-used-ident... don't clear that along with the transaction table, and
it should pick up where you left off. On the other hand, you could simply
leave it the way you have it and use Month/Year to form a multi-field key
with the AutoNumber.

Larry Linson
Microsoft Access MVP
"PMB" <pm*****@megavision.com> wrote in message
news:GN*****************@news.uswest.net...

Thank you in advance for any and all assistance.

I'm trying to keep my numbers for transactions sequential. I clear my
monthly transactions each month and store them in a general transactions
table. My problem is, everytime I clear the table, it wants to start back at Zero, which conflicts with records that are already created.

Is there a way to create a second table and store the value of the last
transaction and pull it into the transactions for the new month?

I've tried psuedo code of; (neither field is AutoNumber)

If IsNull(TransactionID) Then
TransactionID = DMax("tblNewNumber", "TransactionID")+1
Else
TransactionID = DMax("tblTransactionsNew", "TransactionID")+1
End If

But, it doesn't work all the time. HELP

Also, how can I save the last transaction into the new table so that the
transactionID's are sequential?
Michael

[Kinetic - Paul G]

hi
you could look for the dmax record in the trransaction file . . if it is >
0 then add 1 and use that number . .
if it is zero ( ie doesnt exist ) then get the highest number from the
history table and add 1.

see below

"PMB" <pm*****@megavision.com> wrote in message
news:GN*****************@news.uswest.net...

Thank you in advance for any and all assistance.

I'm trying to keep my numbers for transactions sequential. I clear my
monthly transactions each month and store them in a general transactions
table. My problem is, everytime I clear the table, it wants to start back at Zero, which conflicts with records that are already created.

Is there a way to create a second table and store the value of the last
transaction and pull it into the transactions for the new month?

I've tried psuedo code of; (neither field is AutoNumber) * * * * * *
on error resume next . . . will ignore the error when no transaction records
exist
transactionID = 0
TransactionID = DMax("tblTransactionsNew", "TransactionID")+1 . . will not
execute if table is empty
If transactionID = 0 then . . . . table was empty
TransactionID = DMax("tblTransactionsOld", "TransactionID")+1
else
TransactionID = TransactionID + 1
endif
cheers
paul


But, it doesn't work all the time. HELP

Also, how can I save the last transaction into the new table so that the
transactionID's are sequential?
Michael