By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Saving last TransactionID to another table

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.