Contract Number Generator

[sz13]

Dear all,
I am quite new to Access and VBA.
I am working on a small database for my company, where the main purpose would be automatic contract number generation.
We are managing 4 accounts at the moment (expecting to increase) and we need to keep separate contract 'counters' for each account.
I have two tables, one which contains all transactions (tblTransactions), and one which contains account information (tblAccounts), a query (qryAccTran), which combines the two tables with a Lookup field AccountRef and has some calculated fields.
What I am trying to achieve is to make a contract number generator on btnGenerate_click() on my form frmNewTransaction.
The number must increment in sequence, but with reference to the account used.
eg. ACA00001, then ACA00002, ACA00003 and if I choose a different account it would start again from ACB00001 then ACB00002 and etc.

It would have been easy if I needed one general 'counter' which would be in a separate field and I would just combine the AccountRef and next available number using Dmax. However because my contracts are Alphanumeric the Dmax does not seem to work and gives Value mismatch error.
Can you please advice me on what function or code should I use to overcome this problem, as I have spent hours looking online and trialing different methods but none were successful.

I will be very grateful for your help.

 

[pbaldy]

I would store the contract number separately from the account. Use the DMax() method to find the highest number for the appropriate account, and add 1 to it. For the user, concatenate the values together to get your desired look.

 

[Galaxiom]

The Accounts table would presumably have a field for AccountCode.
Store the number part separately as a number type (Integer or Long) and increment that.

Concatenate the fields and pad the leading zero for display using this ControlSource:

= AccountCode & Format(SequenceNumber, "000000")

If you need to increment the letter sequence then use Asc() function to get the character code, increment that and then convert back to a character with Chr() function.

ASCII codes for the capital letters run alphabetically from 65 to 90.

Use the Mid() function to parse the letter code into separate characters for incrementing.

 

[sz13]

Thank you so much guys! Never thought of that!
I will try it tomorrow and let you know, but seems like my problem will be solved.

Cheers!

 

[sz13]

Hi again,
I have worked on the contract generator today.
So what I have done is I have made extra fields for each account, in the transaction table. I had to eliminate the table with accounts and create combo boxes in the transaction table, as I was trying to work through a query but the datasheet became unupdatable.
This is the only way I have managed to make the separate 'counters' for each account. So a person selects an account in the combo box and presses generate button, which triggers and IF command and concatenates the combobox with the dmax of the selected account contract field.
It is quite inefficient, as when we open more accounts I will have to add new columns to the table and add options to the combobox and make additions to the code.
Also I have noticed that because it is on Button click event, if a person selects one account and generates a contract and realises that he wants to use another account and generates another contract number it creates two entries, one for each account field in one record, thus taking up an 'unneeded' contract number, which then creates problems for sequence.

Can you please advise me on how the system can be improved?
I am sure there must be other ways, its just my limited knowledge of VBA that creates inefficencies and constraints.

Cheers.

 

[pbaldy]

You don't want a field for each account; you had it right to begin with. If you're form became read-only, you must have had a query as it's source to get both tables. You just want your form bound to the transaction table. You'd use a combo to select accounts from the accounts table, but the combo would be bound to the "account ID" field in the transaction table.

It's probably most common to have code generate the number in the after insert event of the form. That means it isn't created until the last moment, after the user has finished inputting. It also makes it harder for multiple users to get the same number by mistake.