increment by 1 every button click

[shod90]

Dear All,
um working on cashier system and need to set the transaction ID to be incremented every time by one when the user click New Order .. And the text box that holds the transaction ID (which refers to TransactionID in tblOrders) to remember the last number was incremented and save it to tblOrders.
I don't want to use Autonumber , Sometimes makes troubles .



Hope you understood me .

Thanks in advance.

[ranman256]

Autonumber is the easiest,passive, problem free method.

[Micron]

The Access gurus say that autonumbers should never be used as meaningful data and never shown to the user. So what's good enough for them...
However, if you don't care whether or not the number is sequential and/or has no gaps, then maybe.
The main issues with sequential numbering arise in two situations, assuming the table field for this number is indexed, no dupes.

In a multi-user db, it is possible to run into conflicts if the same calculated number is being used by concurrent users. When the second goes to save, it fails and at worst, the record is lost. Second is that if a record is started and the sequential number is calculated and stored in some seed table, the user may cancel the record creation. If that happens, there is a gap in the sequential numbering.

The only ways I know that are somewhat reliable are as follows (in either case, the likelihood that concurrent users attempt to create a record at the same millisecond is virtually impossible):
- create and save the record at the very beginning if you need to display the number at the start, and don't allow the record to be canceled. If during the record creation it is decided the record is not required, provide for a 'cancellation' flag of some sort on that record. A query for these records can either be filtered to not show 'canceled' or can identify which ones were canceled to show why you might not be able to locate a printed copy of a PO, for instance. Usually this is not possible if there is a sub form whose records relate to the sequential number.

- create the number at the end of the process and display it then. It pretty much ensures that if one is going to effect a save after filling out the form, the record is, in fact, required, although providing a means of cancelling such a record may not be a bad idea, depending on the business case.

- use temp tables. If you don't have the unique sequential number, at the very least you need a temp unique identifier on the main form, such as the user id, in order to create the child records. Once the record is complete, write the parent/child records to their tables.

In either method, the number would be calculated using the DMax function on the sequential number field (easier than DLookup). That would be my preference over using some autonumber field to find the last record. The form probably needs a Save and Save/Close button in addition to whatever else seems suitable (such as Cancel) if the record creation starts with the number. Save to commit the number, the Save/Close to save any changes made to the form when user attempts to close it (since the record is incomplete when the number is generated).