anthonytr
asked on
Auto Increment number
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
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
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.
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.
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.
ASKER
I have implemented the code, but keep getting and error when it runs:
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
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
It debugs to:
rst!batch_number = 1 + DMax("batch_number", "tbl_batches", "customer_id = " & Forms!frm_batches!customer
with "Runtime Error '2471'
The Expression you entered as a query parameter produced this error: 'TR12043'
customer_id is a text field
Thanks
ASKER
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.
rst!batch_number = 1 + DMax("batch_number", "tbl_batches", "customer_id = '" & Me.customer_id & "'")
all working fine.
ASKER
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?
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?
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(st rSQL, 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
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(st
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
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.
From your description, it isn't clear to me how you want multiple batch rows numbered.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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","MyT