Generating and AutoNumber that increases by 2

  • Thread starter Thread starter tracey
  • Start date Start date
T

tracey

I am a complete novice when it comes to VB but after
reading several similar problems to mine I feel that
creating some VB is my only answer.

I need to generate a unique number that is automatically
increased by 2 when data is entered into the database from
an online form. This unique number will then be displayed
to the user for reference purposes.

The numbers relate to computers and monitors owned by my
company and therefore can-not be reused. Also Monitors
require and even number and Computers an Odd. Monitor
details will be held in a different table from the
Computers.

My problem, I can't use AutoNumber as I can't make is
increase by 2 and apprently can't rely on in supping
sequencial number ever time. So need to write some code to
do this for me. As I no nothing about VB I was hoping
someone out there could help.

Now obviously some equipment is already cataloged and
therefore already number so I need my auto numbering to
start from a specific number. ie: 01419 and 3 letters to
preceed the number ie xyz.

So I get XYZ01419
XYZ01421
XYZ01423
For computers and
XYZ01420
XYZ01422
XYZ01424
For monitors

A bit of a long winded explanation I know but I hope I've
covered it all.

Oh could this AutoNumber also be my Primary Key?

Thanks
 
-----Original Message-----
I am a complete novice when it comes to VB but after
reading several similar problems to mine I feel that
creating some VB is my only answer.

I need to generate a unique number that is automatically
increased by 2 when data is entered into the database from
an online form. This unique number will then be displayed
to the user for reference purposes.

The numbers relate to computers and monitors owned by my
company and therefore can-not be reused. Also Monitors
require and even number and Computers an Odd. Monitor
details will be held in a different table from the
Computers.

My problem, I can't use AutoNumber as I can't make is
increase by 2 and apprently can't rely on in supping
sequencial number ever time. So need to write some code to
do this for me. As I no nothing about VB I was hoping
someone out there could help.

Now obviously some equipment is already cataloged and
therefore already number so I need my auto numbering to
start from a specific number. ie: 01419 and 3 letters to
preceed the number ie xyz.

So I get XYZ01419
XYZ01421
XYZ01423
For computers and
XYZ01420
XYZ01422
XYZ01424
For monitors

A bit of a long winded explanation I know but I hope I've
covered it all.

Oh could this AutoNumber also be my Primary Key?

Thanks
.
Click to expand...

Tracey:
What you need is a set of procedures to achive what you
need, and i guess you will need to learn vba to build
strong applications. Meanwhile, here is a code that you
may use to get the numbers from an table with just two
fields, the autonumber and a Yes/No Field.
So you must create this table. in my code the table is:
tblCreateNumber, and the fields are:
PKAuto = Field with the autonumber
Used = Yes/No field

I place this code in a sub procedure that i can call from
anywhere i want. You may put it in the On Enter event of
the field where the number must be generated:

Private sub yourcontrolname_Enter()

If IsNull(Me.yourcontrolname) Then
Set ds = New ADODB.Recordset
ds.ActiveConnection = CurrentProject.Connection
ds.Open "tblCreateNumber", , adOpenKeyset,
adLockOptimistic, adCmdTable

ds.AddNew
ds!Used = 1
ds.Update
mRecitCounter = ds!PKAuto
ds.Close
Set ds = Nothing

End If
 
-----Original Message-----
I am a complete novice when it comes to VB but after
reading several similar problems to mine I feel that
creating some VB is my only answer.

I need to generate a unique number that is automatically
increased by 2 when data is entered into the database from
an online form. This unique number will then be displayed
to the user for reference purposes.

The numbers relate to computers and monitors owned by my
company and therefore can-not be reused. Also Monitors
require and even number and Computers an Odd. Monitor
details will be held in a different table from the
Computers.

My problem, I can't use AutoNumber as I can't make is
increase by 2 and apprently can't rely on in supping
sequencial number ever time. So need to write some code to
do this for me. As I no nothing about VB I was hoping
someone out there could help.

Now obviously some equipment is already cataloged and
therefore already number so I need my auto numbering to
start from a specific number. ie: 01419 and 3 letters to
preceed the number ie xyz.

So I get XYZ01419
XYZ01421
XYZ01423
For computers and
XYZ01420
XYZ01422
XYZ01424
For monitors

A bit of a long winded explanation I know but I hope I've
covered it all.

Oh could this AutoNumber also be my Primary Key?

Thanks
.
Click to expand...
Tracey:
What you need is a full set of procedures, that i think
you won't find together. you will have to look for the
different functions to be needed in your db. as well as
think in learn vba to do it but yourself. when answers
can't be given in forum like this. Even when you get the
answers if you do not know where to put the code, the
code is useless.
Well back to your question.
First:
You must create your table and set the primary as well as
the table where the numbers will be generated
key.
Second:
The code to generate the number can be achived in several
ways, here is just one of them:
The table is use in the code is: tblCreateNumber
The two fields in the table are:

PKAuto = Autonumber
Used = Yes/No
mNumber = The variavel that will store the number

You may put this code in its own procedure, to call it
from anywhere you need.

Option Compare Database
Option Explicit

Private mNumber as string

Private sub GetNumber()
Dim strRecit As String
Dim t As integer
Dim sTemp As String
Dim ds As ADODB.Recordset

Set ds = New ADODB.Recordset
ds.ActiveConnection = CurrentProject.Connection
ds.Open "tblCreateNumber", , adOpenKeyset,
adLockOptimistic, adCmdTable
'To Get the autonumber from the table tblcreateNumber
ds.AddNew
ds!Used = 1
ds.Update
i = ds!PKAuto
ds.Close
Set ds = Nothing
'To create the key for the serial number
i = 1420 + 1 <-- to increment the number by two
mNumber = "XYZ0" + i
End sub
'what it does is: 1 = 1420 + 1 you get 1422 then
' mNumber = "XYZ0" + 1422
'Result = XYZ01422

You can call this procedure in the Enter event of the
field where the number is stored ex.

Private sub yourtxboxname_Enter()
call getnumber
yourtxtboxname = mNumber
mNumber = vbNullstring
End sub

Notes:
Number you had 1419 is not a pair number, so it can't
exist in your table. So you gave a wrong number.
You may replace the 1420 for any number you need.
The number 1 i had is to make multiplos of two.
good luck

Estuardo
 
Tracey,

I would not use Autonumber for this purpose. Two reasons:

1. Autonumber is intended to provide a 'meaningless' unique ID. In
other words, the user is not meant to see it. Your key has two
meanings overlayed: reference for user, and even/odd relates to
different equipment.

2. Autonumber is long integer only. It can't do a XYZ12345 format.

As for solutions, you have two paths to choose from:

Path 1: Build the User Key Yourself.
* Make a table that holds just one record containing the last key
values for each equipment type.
* Make a VB function that returns the next proper key for the
equipment type, and stores that new key value in the table.
* Use this function whenever you add a new record to your catalog
table.
* This key field will be your primary key

Path 2: Use a Autonumber Primary Key.
* Use Autonumber to make a Primary key field, which you will leave
unmodified.
* Add your user key field, defined in Path 1, as a Business or
Surrogate key.
* Your table would have two unique indexes, one for the primary key,
and one for your user key field.
* Internally, all foriegn key references (relationships) between
tables would be by the primary key.
* Externally, the user would see and use your user key.

Most db people would agree that Path 2 is the more 'correct' way.
However, you can build an acceptable solution by using only Path 1.

-Ken
 
Now obviously some equipment is already cataloged and
therefore already number so I need my auto numbering to
start from a specific number. ie: 01419 and 3 letters to
preceed the number ie xyz.
Click to expand...

You are quite right that an autonumber will not do for you. But you do need
to tie down quite what you do need:

Is the XYZ constant or does it mean something? If it's constant, then don't
store it in a field at all, but simply put it in the Format or add it in
the query. If it does mean something, then put it in its own field -- this
is first normal form.

About the plus-two numbering: do you have to have all even numbers or all
odds? I notice that in your posts you have used 3,5,7 as well as 2,4,6.
What is going to happen to the missing numbers, and would it not be simpler
to store the n/2 numbers (i.e. in 1-increments) and multiply by two for the
user to see?

And what about the consecutiveness? At what point will you allow a user to
cancel the creation of a record and still re-use the number afterwards?
What happens if a case is closed and deleted, and still happens to be the
most recent -- how to avoid two valid cases with the same number?

None of these are hard questions, and none of them have right or wrong
answers, except in the sense that getting them wrong will mean that the
database won't work. The advantage of using ANs is that you know exactly
what you will get; with custom counters you are in much more mirky country.


HTH
All the best


Tim F
 
Tracey,
It is certainly possible to do what you want but many here would advise
against it.
I would urge you to use the autonumber as-is and have three columns in your
database
PartNumber - Autonumber,
Prefix - text (3) which would hold the XYZ, and
Category - text (10) which would say Computer, Monitor, or Printer if you
decide to add a new category

If you must have your solution, then post back here and we'll come up with
an answer.

HS
 
Thanks to everyone who replied to this question.

Let me answer some of HTH's questions and see if I make
more sense.

Q1 Is the XYZ constant or does it mean something?

A1 Yes this is constant all Computer and Monitor cat. nos.
will begin GHA.

Q2 About the plus-two numbering: do you have to have all
even numbers or all
odds?

A2 All Monitors are indexed with even numbers and Computer
with odd ones. And entries are made into different tables
in the database. ie a Monitor tables where numbering
would be all even and a Computer table where numbering
would be all odd.

Also I see no reason why the Primary Key and Index number
need to be one and the same I just thought it seemed
logical. However what ever is fine by me.

q3 And what about the consecutiveness?

A3 I don't understand. No number should ever be used
twice. eg. If in 2002 we had a computer GHA00103 that was
binned in 2003 because of water damage that dosn't mean
that the no GHA00103 can be used again.

Any Clearer? I hope so

Tracey
 
Thanks for your interest. Since I have inherited
previously gather data that I must add too then I do have
to do things in a certain way.

eg. All Computer and Monitor cataloged/indexed No. will
begin GHA. follow by 5 numbers. All Monitors are indexed
with an even 5 digit number and Computer with and odd 5
digit numner. eg. GHA00012 is the monitor and GHA00013 is
the Computer. Entries are made into different tables in
the database. ie a Monitor tables where numbering would
be all even and a Computer table where numbering would be
all odd.

Also I see no reason why the Primary Key and Index number
need to be one and the same I just thought it seemed
logical. However what ever is fine by me.

No number should ever be used twice. eg. If in 2002 we
had a computer GHA00103 that was binned in 2003 because of
water damage that dosn't mean that the no GHA00103 can be
used again.

This is how I hope it will work

We receive some new equipment. B4 it is given to a user it
must be cataloged/indexed. The IT guys do this via an
online form, completing the details and posting to the
database table. Once posted a unique Index number is
returned and the Computer/Monitor labled with that unique
number and sent out.

I can do everyting else except create and unique Index
number in the way explained above.

So I'm falling on your mercy to help a girl out.


Tracey
 
....falling on your mercy eh?
This is a simple routine. If you are in a multi-user setup, then we will
have to add more code to avoid those problems.
First, Add a table : tblAutoNums
Fields: Domain text (10)
NextNum LongInteger

Primary Key: Domain
Add Two entries to this table - showing then NEXT (i.e. as yet unused) value
"Monitors" 12
"Computers" 13


now add this function below to a code Module, and you are ready to use it.
for example this code could be part of a command button that generates the
label
txtSerialNumber would be bound to your table.

I would urge you to not let the user access this field set it as invisible
if need be.


Dim nextnum As Integer
nextnum = GetNextNumber("Computers")
If nextnum > 0 Then
Me.TxtSerialNumber.Value = "XYZ" & Format(nextnum, "000000")
Else
MsgBox "wrongNumber"
End If


Regards
HS


'----------------code start--------------------
Function GetNextNumber(ByVal InDomain As String) As Long
On Error GoTo GetNextNumber_Err:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngNextNum As Long
Dim ssql As String

ssql = "Select NextNum from tblAutoNums where Domain = '" & InDomain & "'"

Set db = CurrentDb
Set rst = db.OpenRecordset(ssql)

' capture nextnum, increment it for the next time
rst.MoveFirst
rst.Edit
lngNextNum = rst("NextNum")
rst!NextNum = lngNextNum + 2
rst.Update

GetNextNumber = lngNextNum

GetNextNumber_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

GetNextNumber_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
"Failed to get nextNumber"
GetNextNumber = -1
Resume GetNextNumber_Exit:

End Function


'----------------code end---------------------
 
Let me answer some of HTH's questions and see if I make
more sense
Click to expand...

(Actually I'm Tim -- the HTH means Hope That Helped... it obviously didn't
Q1 Is the XYZ constant or does it mean something?
A1 Yes this is constant all Computer and Monitor cat. nos.
will begin GHA.
Click to expand...

So don't store it at all: simply stick it on the form/ report/ query as a
text literal.
Q2 About the plus-two numbering: do you have to have all
even numbers or all odds?

A2 All Monitors are indexed with even numbers and Computer
with odd ones. And entries are made into different tables
in the database. ie a Monitor tables where numbering
would be all even and a Computer table where numbering
would be all odd.
Click to expand...
<puzzled> okay... are you suggesting that they always come in pairs, or is
the odd/ even distinction entirely conincidental? For example, if someone
orders a laptop without a screen, then is the next monitor number left
blank; or if someone has a pc with dual screens... etc. Is there any good
reason, in any case, for having numbers that are unique across different
types of equipment? Why shouldn't there be a monitor 23 somewhere and a PC
23 somewhere else? And I am dying to ask about printers and scanners and CD
arrays and docking stations etc etc. but I mustn't get carried away.

This is starting to look like a job for subclassing, where you have a table
of EquipmentItems, containing perhaps the Location, the Supplier, the
DateDelivered and so on, keyed on an EquipmentNumber. Separate tables hold
details like PC.CPUType, PC.RAMSize, PC.NumberOfUSBPorts; or
Monitor.MaxResolution, Monitor.HasBuiltInSpeakers etc.; each keyed on the
same EquipmentNumber. If you see what I mean.
Also I see no reason why the Primary Key and Index number
need to be one and the same I just thought it seemed
logical. However what ever is fine by me.
Click to expand...

I'll agree with everything in that statement.
q3 And what about the consecutiveness?

A3 I don't understand.
Click to expand...

You said in your OP that autonumbers were unsuitable because they cannot be
relied upon to be consecutive -- which they can't. I assumed from this that
you wanted the numbers to go relentlessly 23,25,27,29 with no missing ones
-- but you have to know what happens when machine 29 is removed (for
example because of water damage). If you want to go on to 31, you have to
have some way of remembering that 29 has gone, because most common methods
simply look up the highest current value (27) and add the increment and
will likely present 29 again. Therefore you need a method that avoids the
error.

I see you are getting some detailed code from Salim, so you don't have to
go on with this. My instinct is, though, that you have a way to go in
analysing your system needs.

Best of luck


Tim F
 
A couple of comments on the paragraph below - mostly rambling thoughts on
the subject.
I'd strongly disagree with any mechanism that wanted to re-use numbers as in
the 29 below.
Once a number had been used, it should stay used. trying to reuse numbers
is asking for untold headaches, particularly when foreign keys are involved.

Fortunately, Tracey will not be affected as she does not want to reuse the
29 (below)

As for the sequential generation of autonumber and the gaps there - My
procedure contains a similar flaw that autonumber has.
Let me explain.
Autonumbers are indeed serially incremented. Access maintains an internal
counter of the serial number, although that is not exposed to us.
(Evidence of that comes from the fact that the counter is reset when the
database is compacted.)
Gaps arise because uses retrieve an autonumber and then fail to use it.
In a typical form design, the autonumber gets populated (and the internal
counter incremented ) when the form or row
becomes dirty. When a user begins data entry and then chooses to cancel the
record, that number is lost.
Hence the claim that autonumbers are not guaranteed to be sequential.

How do you ensure that the numbers increase sequentially? With Autonumbers,
one way to do it is to use unbound forms and write form values to a database
as the form moves to the next record. In this case, you can use a bound
form, but populate the value of the NextNumber just before the record is
saved.
I think tracey just wants to ensure that Computers have odd numbers and
monitors even numbers, and is not particularly concerned about gaps in the
sequence if any. If that is a concern, then the application shoud be
designed to retrieve the nextNumber and commit it to the table as quickly as
possible.

Some people are uncomfortable with the fact that autonumbers are not
sequential -
DBase programmers tend to look for a rowid - as in the row's relative
position.
Once they get a better understanding of how autonumbers are used, the
discomfort goes away.
Autonumber columns can indeed be used to uniquely identify a row as Access
does guarantee uniqueness which can be fortified with a unique index.

HS
 
I think tracey just wants to ensure that Computers have odd numbers
and monitors even numbers, and is not particularly concerned about
gaps in the sequence if any.
Click to expand...

You might think that, and I might even agree, but it's not what she (he?)
said in the OP.

Apart from that, I don't think we have anything to disagree about.

All the best


Tim F
 
Back
Top