Reference Number

[kruger101]

Hi guys/gals

My primary key, RefNumber, is a sequentual Autonumber field. I want to creat my own Custommade autonumber field, but the catch is:
My autonumber field should have any of the following (selectable by the user from a combo box from a Form) attached in front of the number: DPR,DPZ,DFE,DGL,DMI,DGJ or DPE.
ie: the autonumber is at 14. The user selects DFE, but this is only the seconde choice made of DFE.

So the end product should be: RefNumber = 14
.........................................DistrictRefNumber = DFE2 or DFE002

Help please?
Thanks in advance

 

[FoFa]

You can always use the DCOUNT("MyRefNo","MyTable","[MyRefNo] like 'DFE*')
to return the current number and add 1 to it

 

[The_Doc_Man]

1. Prime keys can be compounded from multiple fields.

2. Formatting will allow you to build the leading zeros to tack on the prefix you assign. Don't try to store a single-field concatenated key if any PART of the field has meaning. (And I think your prefix has meaning.)

 

[kruger101]

FoFa
Thanks for the help. It works perfectly. But now I'm having trouble adding the 1. The DCOUNT counts the current amount of choices, but how do I add the 1 more? Am I able to re-run the query? Thanks again.

 

[kruger101]

OK here is what I have to do:
The user chooses from a combo box one of the following: DPR, DPZ, DFE, DGL, DMI, DGJ or DPE. RefNumber is a Autonumber field counting the amount of records. So if we are at record number 1, the user chooses DFE, I want a field saying DFE001. Say the second record's selection is DPR, it should read DPR001. If the third record is DFE again, it should be DFE002. Fourth selection: DGJ. Fifth selection is DFE. So here is how it should look:
Refnumber.................GeneratedRefnumber
.......1..................................DFE001
.......2..................................DPR001
.......3..................................DFE002
.......4..................................DGJ001
.......5..................................DFE003

Thanks a lot guys, I really need this one bad. I have to finish it before tomorrow.

 

[FoFa]

Select DFE
Something = "DFE" & format(DCOUNT("MyRefNo","MyTable","[MyRefNo] like 'DFE*') + 1,"000")

As long as they don't go over 999 that is
Also rather than hardcoding the DFE like I show, use the combobox (or whatever) to supply it.
If there are none, DCOUNT will return 0, so 0 + 1 = 1
The format will zerofill the leading zeros for up 3 places (the "000" partof the format). I hope I have the FORMAT right, you may have to use help to get the proper syntax.