Tricky autonumber one.

[Jimmythemadman]

im creating an error reporting database.
for this the hardware/system items need entering
they will have a unique id
to assist identification i would like the unique id to start with a 3 letter code refering to the type of hardware/system/periferal followed by the number
e.g a printer will ahve the id "ptr 00000" and a computer will have the id "cpu 00000" with the 3 letters automaticaly comeing from a combobox field in the same table (form to the user)
i have allready been able to get autonumbers with 3 letters before when i created the tecnitian id they are "TEC-00000"

any help thanked. im an A2 student so i know all the code stuff.

thanks in advance

Jimmythemadman.

 

[ColinEssex]

any help thanked. im an A2 student so i know all the code stuff.

Then you should know that you never use an AutoNumber for anything than linking tables internally.

One way is to use code to find the Max() number and concatonate with the text

Search for "Invoice numbers" this question is a regular and many examples are posted

Col

 

[Jimmythemadman]

im useing the autonumber for the hardware id this will be on the side of whe computer/ printer etc im using an autonumber sothat the sticker can be produced and printed automaticaly without the user haveing to make up a number fot it this wil save a lot of time as the system is to be implimented in a building where the is a ubstantial infastructure (400+ pieces of hardware)

i have considered a way rpund this of just using two fields on te record positioned close enough to look like one field to the user.

 

[boblarson]

im useing the autonumber for the hardware id this will be on the side of whe computer/ printer etc im using an autonumber sothat the sticker can be produced and printed automaticaly without the user haveing to make up a number fot it this wil save a lot of time as the system is to be implimented in a building where the is a ubstantial infastructure (400+ pieces of hardware)

Colin is correct - and you are incorrect when you say that you must use an autonumber to produce a sticker so the user doesn't have to do anything. You can do that without using an autonumber and still not have to have user interaction. As he mentioned, you would use DMAX to find the last number added and then increment by one.

There's a couple of things that you should know about autonumbers:

1. They ONLY guarantee you a UNIQUE number and do NOT guarantee you incremental numbers. This means that, while it appears that they always increment by one, it is not necessarily true that will always occur.

2. If a record is started, and then not completed, that number is gone forever and you will have gaps in the numbering sequence.

Here is some more good info on Autonumbers and Primary Keys:
http://www.access-programmers.co.uk/forums/showthread.php?t=128935

 

[Jimmythemadman]

ok i now see the error of my ways.
however im am unsure of ow to use dmax in the design of the table how would i impliment this and would i still be able to use the 3 letter abreviation (taken from a field in the same table) at the begining of the number?

 

[boblarson]

Store the number and the prefix separately

 

[ColinEssex]

Thanks Bob, I recall a post from last year from the great Pat Hartman that explained it all and gave examples - which is why I suggested Jimmy did a search

Col

 

[boblarson]

Thanks Bob, I recall a post from last year from the great Pat Hartman that explained it all and gave examples - which is why I suggested Jimmy did a search

Col

If I might expand upon that idea - I would say searching for ANY posts by Pat Hartman would be a great learning experience for ANYONE who wants to get some good info.

 

[ColinEssex]

Store the number and the prefix separately

and concatonate them when you need them

As you are familiar with all the code "stuff" I assumed you knew how to use the DMax() + 1 procedure

Col

 

[Jimmythemadman]

ok then maby not all of the code stuff, more like bits which i have found usefull and taught myself/ asked others about.
im vaiguly familliar with the DMax() +1 from some work with vb but am unsure how to use this in the specific situation.

 

[ColinEssex]

Have you searched yet?

Col

 

[boblarson]

You can have a bound form that has the fields you need and as the last item to do as you create the record you would use DMAX to find the last number and increment it. You want the latest point in the process as it will keep you with the fewest possible gaps due to more than one user starting a record but not finishing it. So, if you set the dmax field in the form's before update event you should wind up with very few, if any, gaps.

Have you searched yet?

Col

And good point - please do what Colin suggests.

 

[Jimmythemadman]

right i think i got it now i was trying to do everything in the tale but doing it in the form will be easier. i may dissapear for a while to contemplate and attempt. (I havent got much of the database created yet its still in the ideas stage)

 

[boblarson]

You would want to use forms anyway as you definitely do not want users directly using tables/queries for adding or editing data. You do not have as much control as you do with forms.

 

[Jimmythemadman]

ive given it a try but it wont work ive got
Option Compare Database

Private Sub hardwareid_BeforeUpdate(Cancel As Integer)
hardwareid = DMax("[hardwareid]", "tblhardware", 0) + 1
End Sub

in the form for the field of hardware id (which is where i want the number to appear

help please

 

[Jimmythemadman]

i am not bothered with using continus numbers in the id would it not be easier to use a random autonumber to go after the 3letter code. i do not see how using an autonumber to create a wholey random number can be a problem.
i would be interested in any responce

 

[boblarson]

Set the textbox name as getting it, also your DMAX syntax is off:

Me.YourTextBoxNameHere = DMax("[hardwareid]", "tblhardware") + 1

 

[boblarson]

i am not bothered with using continus numbers in the id would it not be easier to use a random autonumber to go after the 3letter code. i do not see how using an autonumber to create a wholey random number can be a problem.
i would be interested in any responce

If you just want a unique number (and aren't concerned about consecutive numbers) then you can, of course, use an autonumber to generate the number if that is easier for you. Just don't set it to random and don't use replication as it will also generate negative numbers.

 

[Jimmythemadman]

aha by entering it in the form_load area ive got it to add 1 to the value however its adding 1 to the first value of the table thus messin up the records is it possiable o got th form to go straight to a new record when its loaded

 

[Jimmythemadman]

i now have got it to do it right e.g when a new entry is made however if you edit a preexisting record it add to the number if there a way to stop this (im thinkng putting an if funcion which only runs the dmax code if the value is null)