Unanswered: Executing queries on click of a button
I am trying to generate a reference number within an access form.
The form has about 7 combo boxes which have various categories, and these categories have two characters associated with them, for example "Call Handling" is CH.
There'll also need to be a unique number so when there are 7 values the same, it increments.. I.e. CH-CC-1, CH-CC-2, CH-CC-3 etc.
This is then stored as a unique reference number (URN) in the main table.
What I thought of doing, was to split it into two fields, a categorisation, and number field. The number field can be declared as an autonumber, and then the categorisation is filled with the CH-CC etc.
However, I'd really like it so the autonumber is only incremented for that category, so: CH-CC1, CH-CC2, CG-FF1, CG-PL1 etc.
I thought about running a query every time its about to be added, finding the highest number for that category (i.e. SELECT number FROM ... WHERE [Category] = 'CH-CC' etc.). Is this the best way of doing it?