Auto Incrementing

[Tim]

Hello all!

Newbie to Access 97 here. I'm trying to figure out how to
automatically increment a specific alphanumeric so I can
have Access 'assign' the alphanumeric each time I enter a
new part number.

For example, I have been given the alphanumerics ACE00400
through ACE00499 & I wish for it to start at ACE00400 and
automatically increment by one (next one would be ACE00401)
each time I enter new data.

Anyone have any ideas here? I am stumped.

Thanks in advance.

 

[Steven]

Try This:

Dim HighestItem As String
'**Comment: Find the highest item:....DMax( , , ) ***
vHighestItem = DMax( , , ) 'Use the DMax Function
'**Comment: There are some faster ways to do a DMax if it
is a large database but if there are not that many records
then DMax() will me ok. ***

'**Comment: After you have the highest item.
Dim MakeNewNumberString as String
Dim MakeNewNumber as Double
MakeNewNumberString = Right(vHighestItem,5) '**This is
because I know the first 3 char's are ACE **
MakeNewNumber = Val(MakeNewNumberString) + 1
MakeNewNumberString = "ACE" + Right("00000" + Trim(Str
(MakeNewNumber)), 5)

'**Comment: And then you can also test to make sure you
are not over the highest number you can use. **
'**Comment: You can combine some of these lines but I
thought I would break it out to make it clearer. *

 

[Tim Ferguson]

For example, I have been given the alphanumerics ACE00400
through ACE00499 & I wish for it to start at ACE00400 and
automatically increment by one (next one would be ACE00401)
each time I enter new data.

First of all, if all the keys begin with ACE004, then it's best just not to
store the prefix. You can display it on forms and reports by using the
Format property.

If the ACE004 is likely to change, then you would be well advised to store
it in a separate column; particularly if you come back and want the BHE091
to start again at 00 and work up to 99. Come to think of it, if in the
original prefix the 04 is a year counter you should be looking at a three-
field key.

There are a number of ways you can allocate the serial number 00 to 99. You
can certainly use an autonumber, as long as you understand their
limitations. You can use some VBA to allocate them, and this is the only
way to maintain separate queues for ACE004, BHE091 and so on; but you have
to be able to restrict data entry to forms that you program to allocate the
correct number.

Hope that helps


Tim F

 

[Guest]

Both of these replies are greatly appreciated, but I was
hoping for something a little less in-depth. I am quite
new to Access & have no experience with any of the VB or
coding. After reading through the forums a bit more, I
see that customizing Autonumber isn't an easy task.

However, I will try the methods given for examples & see
what sort of outcome I have. Thanks for the help.

 

[Tim Ferguson]

However, I will try the methods given for examples & see
what sort of outcome I have. Thanks for the help.

You can accomplish so much with just the UI, but you will find that any
real work with Access (or any database) does require some programming
effort. At least to people experienced with it, Access is not an end-user
tool in the way that Word and Powerpoint are -- it is much better described
as a development platform. It is not the sort of software that you can just
pick up and start typing. In fact, with any moderate or large application,
the project is probably more than two-thirds complete before Access is even
fired up: that is how important the preparation and analysis stages are.

Best of luck

Tim F