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...

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.

>-----Original Message-----
>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...
>.
>

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

--
Steve Schapel, Microsoft Access MVP


V Ramos wrote:
> 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...

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?

"V Ramos" wrote:

> 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...

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 Dennis wrote:
> 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?
>
> "V Ramos" wrote:
>
>
>>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...

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" wrote:

> 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 Dennis wrote:
> > 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?
> >
> > "V Ramos" wrote:
> >
> >
> >>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...
>

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 Dennis wrote:
> 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" wrote:
>
>
>>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 Dennis wrote:
>>
>>>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?
>>>
>>>"V Ramos" wrote:
>>>
>>>
>>>
>>>>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...
>>

I AM NOT ABLE TO UNDERSTOOD THIS?PLEASE TELL ME IN DETAIL EXMAPLE.

"Steve Schapel" wrote:

> 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
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> V Ramos wrote:
> > 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...
>