Customized autonumber field

V

V Ramos

Is it possible in Access to create a customized autonumber field that will
increment like this;

ADJ-1001
ADJ-1002
ADJ-1003
and so on...

thanks in advance...
 
G

Guest

not too sure about that. it's not a number as access sees
it. it's text. that would make it difficult for access(VB)
to add 1 to it for the next number(?).
but you could split the number. in your next number table
(which you would have to create to do this), put the alpha
characters in one field and the numeric characters in
another field. that way you could add 1 to the numeric
part. then when you select the next number, you could
contatinate them.
look up combineing data in access help. i did. it explains
pretty good.
 
S

Steve Schapel

V Ramos,

Is "ADJ" the prefix for all of the records in the table? If so, you
don't need to store this in the table. Just use an ordinary AutoNumber
field, and set its Format property, either in the design of the table,
or on the relevant form and report controls, to...
"ADJ-"0000
 
P

Paul Dennis

I'm having the same problem, apart from The ADJ will change depending on the
last name and the number also, i.e.

ADJ-001
ADJ-002
DEN-001

any ideas?
 
S

strive4peace

yes, use the form BeforeUpdate event

'-------------------------
if isnull(controlname_text) then
msgbox "You must fill out WHATEVER",,"Cannot save record"
'don't update record yet
cancel = true
exit sub
end if

'don't do anything if field is already filled out
if not isnull(me.controlname) then exit sub

dim mNum as long
mNum = nz(dMax("mid([fieldname],5,3)", _
"tablename", _
"WHERE left([fieldname],3) = '" _
& me.controlname_text & "'")) + 1
me.controlname = _
me.controlname_text _
& "-" & format(mNum,"000")
'-------------------------

where
controlname_text is the control containing ADJ or DEN or...
controlname is the name of the control for your custom
number code

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
P

Paul Dennis

thanks for this, however I have 30,000 records to update (as a one off) hence
I need to run this as an update query?

sorry to be a pain.

strive4peace said:
yes, use the form BeforeUpdate event

'-------------------------
if isnull(controlname_text) then
msgbox "You must fill out WHATEVER",,"Cannot save record"
'don't update record yet
cancel = true
exit sub
end if

'don't do anything if field is already filled out
if not isnull(me.controlname) then exit sub

dim mNum as long
mNum = nz(dMax("mid([fieldname],5,3)", _
"tablename", _
"WHERE left([fieldname],3) = '" _
& me.controlname_text & "'")) + 1
me.controlname = _
me.controlname_text _
& "-" & format(mNum,"000")
'-------------------------

where
controlname_text is the control containing ADJ or DEN or...
controlname is the name of the control for your custom
number code

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



Paul said:
I'm having the same problem, apart from The ADJ will change depending on the
last name and the number also, i.e.

ADJ-001
ADJ-002
DEN-001

any ideas?

:
Click to expand...
Click to expand...
 
S

strive4peace

Hi Paul,

Not an update query, but you can loop through the recordset

'---------------------------
Sub LoopThroughRecordset()

'Set up error handler
On Error GoTo proc_error

'dimension variables
Dim mNum as long, mStr as string
Dim s as string, r As Recordset

'construct the SQL statement
s = "SELECT * FROM Tablename " _
& " WHERE ([field_with_code] Is Null) " _
& "AND (Len(Nz([field_with_text])) > 0) " _
& " ORDER BY [field_with_text];"

'open the recordset
Set r = CurrentDb.OpenRecordset(s,dbOpenSnapshot)

mStr = ""

'move to the first record
r.MoveFirst

'loop through the recordset until the end

Do While Not r.EOF
if r!Fieldname_with_text <> mStr then

mStr = r!field_with_text

mNum = nz(dMax("mid([field_with_code],5,3)", _
"Tablename", _
"WHERE left([field_with_code],3) = '" _
& r!field_with_text & "'"))
end if

mNum = mNum + 1

r.edit
r!field_with_code = _
r!field_with_text _
& "-" & format(mNum,"000")
r.update

r.MoveNext
Loop

'close the recordset
r.close
'release the recordset variable
Set r = Nothing

'got through ok -- exit sub
Exit Sub

'if there is an error, the following code will execute
proc_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
LoopThroughRecordset"
Stop
'press F8 to step through code and debug
Resume
End function
'---------------------------

I just typed this code in so you will have to check it for
errors


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



Paul said:
thanks for this, however I have 30,000 records to update (as a one off) hence
I need to run this as an update query?

sorry to be a pain.

:

yes, use the form BeforeUpdate event

'-------------------------
if isnull(controlname_text) then
msgbox "You must fill out WHATEVER",,"Cannot save record"
'don't update record yet
cancel = true
exit sub
end if

'don't do anything if field is already filled out
if not isnull(me.controlname) then exit sub

dim mNum as long
mNum = nz(dMax("mid([fieldname],5,3)", _
"tablename", _
"WHERE left([fieldname],3) = '" _
& me.controlname_text & "'")) + 1
me.controlname = _
me.controlname_text _
& "-" & format(mNum,"000")
'-------------------------

where
controlname_text is the control containing ADJ or DEN or...
controlname is the name of the control for your custom
number code

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



Paul said:
I'm having the same problem, apart from The ADJ will change depending on the
last name and the number also, i.e.

ADJ-001
ADJ-002
DEN-001

any ideas?

:



Is it possible in Access to create a customized autonumber field that will
increment like this;

ADJ-1001
ADJ-1002
ADJ-1003
and so on...

thanks in advance...
Click to expand...
Click to expand...
Click to expand...
 
Top