Create Unique Auto Number 
Author Message
 Create Unique Auto Number

I need help developing code for a unique auto number.  

What am I trying to do?

Each salesman will use a quoting program designed in
access 2000.  Then at different points in time, the data
will be imported into a sql databse.  An example code is
as follows:
     1050621031
105- salesman number
062103- date
1- quote number of the day

The program will know what salesman number to use

I need code to find the current date in date format

I need code to find the last quote of the day and
increment it by one or know if it is the first one of the
day.

This Unique Identifier will be used as an auto number and
I don't want the salesman to have to input any of the
info.

Can anyone help me?

Brian



Fri, 09 Dec 2005 05:08:11 GMT  
 Create Unique Auto Number


Quote:
>  An example code is as follows:>

     1050621031

First of all, I'd really encourage you to store these three separate items
of data in three different fields -- you might care to read up on First
Normal Form. It's easy to create a Primary Key from all three fields and
the maintenance and query headaches of a single code as you have suggested
are not trivial. In a query or a report, you can make it look like what you
want with something like:

  SELECT Format(SalesManCode,"000") &
         Format(CreateDate,"ddmmyyyy") &
         Format(SerialNumber, "0") AS CombinedCode,
    OtherField, OtherFields
  FROM etc,

Is one digit really enough space for the SerialNumber: will you never go
beyond 9?

Quote:

> The program will know what salesman number to use

Okay, can we call its control txtSalesmanCode

Quote:
> I need code to find the current date in date format

It's so easy to format the date and you gain so much functionality by using
the native DateTime datatype, I'd leave it at that. The easy way to
populate this field is by putting =Date() in the DefaultValue of the
textbox control. You can format the textbox using "ddmmyy" but I have to
add that dropping the century information is a Really Bad Habit to get
into. Does it really take only three years to forget?

Quote:
> I need code to find the last quote of the day and
> increment it by one or know if it is the first one of the
> day.

Okay, put something like this in the BeforeUpdate event of the form:

  ' Don't overwrite an existing number!
  If IsNull(txtSerialNumber) Then
    ' make a search string from existing fields
    strCriterion = _
        "SalesmanCode = """ & txtSalesmanCode & """ AND " & _
        "CreateDate = " & Format$(txtCreateDate, "\#yyyy\-mm\-dd\#")

    ' look up the existing value
    varNextNum = DMax("SerialNumber","Sales",strCriterion)

    ' if there wasn't an existing one, start at 1
    If IsNullI(varNextNum) Then varNextNum = 1

    ' put it into the control so it will get saved
    txtSerialNumber = varNextNum

  End If

In a multiuser setup, if there is any chance of two people adding a record
for the same SalesmanCode, then you need to work round that too.

Hope that helps

Tim F



Fri, 09 Dec 2005 06:29:41 GMT  
 Create Unique Auto Number

Quote:

>  SELECT Format(SalesManCode,"000") &
>         Format(CreateDate,"ddmmyyyy") &
>         Format(SerialNumber, "0") AS CombinedCode,
>    OtherField, OtherFields
>  FROM etc,

Tim,

Thanks for the advice. What you said makes sense and that
is what I am going to implement.  I can see many
headaches occuring as far as maintenance.  

Brian



Fri, 09 Dec 2005 10:05:33 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Create unique ID from Employee Numbers

2. Creating an array of unique random numbers

3. Creating Unique Reference Numbers

4. Creating GUID Auto number via DAO

5. ------ creating a auto-numbered table with vb-script -------

6. Creating Custom Auto Number Fields

7. ------ creating a auto-numbered table with vb-script -------

8. ------ creating a auto-numbered table with vb-script -------

9. ------ creating a auto-numbered table with vb-script -------

10. creating database with a auto-numbered tabels

11. Creating Custom Auto Number Fields

12. How do I Create an Auto Number Field Using a DDL query

 

 
Powered by phpBB® Forum Software