Solved

Auto Increment number

Posted on 2014-04-06
9
964 Views
Last Modified: 2014-04-07
Hi,

I'm in the process of redesigning my database app and can't quite get an aspect to work.  Put simply, when I add a batch reference into my database for a customer the batch number must run concurrently according to the customer.

For example.  I add 5 batches for Customer A (1,2,3,4,5).  I then add 3 new batches for Customer B (1,2,3).  If I use an auto increment field, Customer B would get (6,7,8).  How would I allow each Customer to maintain their own batch sequence?

Can anyone advise on how I would achieve this?

Regards,
Anthony
0
Comment
Question by:anthonytr
9 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39981327
One option would be to relax the requirement for batch number for each company to start at 1. Then, an auto-increment number would ensure batch number uniqueness.

Otherwise, an autonumber field is not appropriate here. Declare the field as type Numeric Long Integer and set its default value to be one greater than the maximum value of batch numbers for the current company. ex.:

Form Control property: DefaultValue
=1+DMax("BatchNumber","MyTable","MyCompanyID = " & Forms!MyCurrentForm!MyCompanyIDControlName)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39981353
Another method is to store the next number in a other table.  You may need to do this for a number of tables , so i usually store table ame, uniqueCd, and NextVal.

When i do this, i create a function to call just before i insert records to ensure there is no possibility that someone else can get the sAme value by using the dmax method.  I'm ony phone or i would provide the function, but instead of dmax i open a recordset and get the  nextVal and the reset it in two steps.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39981707
Or you could dispense with storing the customer sequence number altogether.  Your AutoNumber already provides a unique identifier in a specific order.  You can always calculate the customer-specific sequence numbers on demand, in your reports.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:anthonytr
ID: 39981797
I have implemented the code, but keep getting and error when it runs:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tbl_batches", dbOpenTable)
rst.AddNew
rst!customer_id = Me.customer_id
rst!batch_number = 1 + DMax("batch_number", "tbl_batches", "customer_id = " & Forms!frm_batches!customer_id)

rst.Update
rst.Close

Set rst = Nothing
End Sub

Open in new window


It debugs to:

rst!batch_number = 1 + DMax("batch_number", "tbl_batches", "customer_id = " & Forms!frm_batches!customer_id)

with "Runtime Error '2471'
The Expression you entered as a query parameter produced this error: 'TR12043'

customer_id is a text field

Thanks
0
 

Author Comment

by:anthonytr
ID: 39981808
My bad - I got the syntax wrong.  I now have:

rst!batch_number = 1 + DMax("batch_number", "tbl_batches", "customer_id = '" & Me.customer_id & "'")

all working fine.
0
 

Author Comment

by:anthonytr
ID: 39981813
One thing I have realised is this.

If I'm adding a new customer and they currently do not have any Batches recorded in the database the DMax function will error out.  How would I add the first Batch ID to the database for new customers?

Also, how would I return this Batch ID to the original from so that the user can see what batch has been created?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39981982
I use something like the following.  I use a string as the UniqueCD to allow for either string or numeric values, and convert the Code that is passed into the function to a string for that reason.

Public Function fnNextNumber(TableName as string, Code as Variant) as Long

   Dim rst As DAO.Recordset
   Dim strSQL as string

   strSQL = "SELECT * FROM tbl_NextNumber " _
               & "WHERE [TableName] = '" & TableName & "'"
   Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenTable, dbfailonerror)

   rst.FindFirst "[UniqueCD] = '" & cstr(Code) & "'"
   if rst.NoMatch then
        rst.AddNew
        rst!TableName = TableName
        rst!UniqueCD = cstr(Code)
        fnNextNumber = 1
        rst!NextVal = 2
   Else
        rst.Edit
       fnNextNumber = rst!NextVal
       rstNextVal = rstNextVal + 1
   endif
   rst.Update
   rst.close
   set rst = nothing

End Function
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39982149
you need two different fields.  One field for the autoincrement values and another for the batch sequence values.  I implemented a sequencing in one of my applications in two passes.  The first pass added the rows.  I then queried the database to get the lowest autoincrement value for that client.  Using that value in an Update query, I was able to let the database to all of those sequence calculations.

From your description, it isn't clear to me how you want multiple batch rows numbered.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39982274
> How would I add the first Batch ID to the database for new customers?

You can use:

rst!batch_number = 1 + Nz(DMax("batch_number", "tbl_batches", "customer_id = '" & Me.customer_id & "'"), 0)

/gustav
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 33
A Function to parse a text string 4 34
VB Script: Correction backup files and folders v3 11 24
DCount Type Mismatch 2 21
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question