add primairy key into txt field

[crujazz]

I got a table with 3 columns

idGasID (Autonumber)
txtGas (Default Value: "A-"
txtLocation



what i would like to see is:

idGasID : 1.......999
txtGas : A-001 ....... A-999

the txt field gas is filled with the primairy key when i enter a new Gas type
in the table.
I don't know how to complete this in design view, can someone help me out
with it?

Thnx ion advance

 

[BruceM]

What happens after 999? Is the txtGas value "A" until 999, then goes to
"B", or what exactly.

 

[crujazz via AccessMonster.com]

What happens after 999? Is the txtGas value "A" until 999, then goes to
"B", or what exactly.

I got a table with 3 columns

[quoted text clipped - 14 lines]

Thnx ion advance

Indeed after 999, for example last day of decmeber, the letter A should be
raised to B

 

[BruceM]

What happens if you get to 999 in November? Is this a situation where you
will never get to 999 in one year? Is the letter derived from the year?

I'm trying to figure out the logic behind the numbering scheme. It can be
done, but if I interpret the requirement incorrectly the time spent devising
a method will be wasted.

What happens after 999? Is the txtGas value "A" until 999, then goes to
"B", or what exactly.

I got a table with 3 columns

[quoted text clipped - 14 lines]

Thnx ion advance

Indeed after 999, for example last day of decmeber, the letter A should be
raised to B

 

[Klatuu]

Do not use an Autonumber field for this process. Autonumber fields should
never be used for any meaninful data. They are designed for use as surrogate
primary key fields and are not fit for human consumption.

Do not store the same value twice in the same record.
Here is a method for finding the current highest value in a field and adding
1 to it.

tblGas
GasID Autonumber Primary Key
Gas Text
GasNumber Long Integer

Dim lngHighNumber As Long

lngHighNumber = IsNuLL(DMax("[GasNumber]", "tblGas", "[Gas] = """ &
Me.txtGas & """"), 0) + 1
If lngHighNumber > 999 Then
If Me.txtGas = "z" Then
MsgBox "You Are Out Of Numbers"
Else
Me.txtGas = Chr(Asc(Me.txtGas) + 1)
lngHighNumber = 1
End If
End If