consecutive number problem (Access 2000 SP3)

Topic

New Reply

I have been trying to figure out how to do this and I just can’t get anything to work.

Basically, we need to provide a customer with a special request number, based on their store id. This request number needs to be consecutive, except if it’s the same store. So, for example:

Store number
MIN001 10001
MIN288 10002
MIN388 10003
MIN388 10003
MIK123 10004

Any suggestions?

Reply | Quote

Replies

I guess I’m a bit dense today – do you mean a customer would have two special requests with the same number? And are they entered at the same time or as much as several days apart, such that if there is a currently unsatisfied special request open for a customer, you attach a new request to the same number? I presume there is some sort of customer ID also associated with the special request, so it sounds like you will probably need some code to check and see what the situation is before you assign a special request number. In that case, I would assign a separate autonumber field as the primary key to satisfy uniqueness needs, though you could use Store, Number and CustomerID in an index to speed retrieval. I hope this helps, but I’m not sure it makes sense.

Reply | Quote

Wendell,

The customer I’m working with has several stores. Each store can order more than once during a time period, but the customer wants consecutive numbering on the special reference number except when the same store is ordering, let’s say, two times. Does this make more sense?

Reply | Quote

Wendell,

The customer I’m working with has several stores. Each store can order more than once during a time period, but the customer wants consecutive numbering on the special reference number except when the same store is ordering, let’s say, two times. Does this make more sense?

Reply | Quote

Actually, let me make my test data a bit more “user friendly”

Store Order requestID
MIN001 12930 10001
MIN288 40593 10002
MIN388 50405 10003
MIN388 50503 10003
MIK123 40384 10004

Reply | Quote

Well, now I’m pretty much totally confused. So more questions – what purpose does the requestID server? I think I under stand the Store bit, but is this a situation where customers are ordering things from stores, or is it where a customer orders something to be delivered to a store where it will presumably be picked up?

Reply | Quote

Wendell,

The requestID serves as a routing number. The situation is that we’re shipping to the stores and providing the customer with an electronic list which includes this routing number. Our only workaround at this point is to export the table to Excel, manually adjust the routing numbers, and then import it back into something we can use to transmit. I was hoping for an automated process so that there are fewer human hands (and tired brains like mine ) in the deal.

Reply | Quote

I can see why you want to solve the problem – that sort of thing drives me up the wall in the blink of an eye. I think Hans has suggested the best approach – looping through a recordset with either ADO or DAO is no big deal from a coding perspective. Well, if you’ve never done it, or do it very infrequently it can be intimidating, but isn’t much more involved than the code Hans suggested for a form doing the insert. Which brings up the next 20 questions. How does the initial customer request get generated – is it not data entered on a form somewhere?

Reply | Quote

Wendell,

Yes, I do looping very infrequently (except at home when I’m trying to teach the little one how to knit, but that’s another story ).

The data comes from another system. We just created some queries to massage it before it goes back out. The order itself comes in electronically.

Reply | Quote

Looks like Hans had given you sample code that should do the trick. Working with systems that are hard to change, so we do stuff in Access it appears. Not an uncommon situation! Good luck with the process.

Reply | Quote

Looks like Hans had given you sample code that should do the trick. Working with systems that are hard to change, so we do stuff in Access it appears. Not an uncommon situation! Good luck with the process.

Reply | Quote

Wendell,

Yes, I do looping very infrequently (except at home when I’m trying to teach the little one how to knit, but that’s another story ).

The data comes from another system. We just created some queries to massage it before it goes back out. The order itself comes in electronically.

Reply | Quote

I can see why you want to solve the problem – that sort of thing drives me up the wall in the blink of an eye. I think Hans has suggested the best approach – looping through a recordset with either ADO or DAO is no big deal from a coding perspective. Well, if you’ve never done it, or do it very infrequently it can be intimidating, but isn’t much more involved than the code Hans suggested for a form doing the insert. Which brings up the next 20 questions. How does the initial customer request get generated – is it not data entered on a form somewhere?

Reply | Quote

Wendell,

The requestID serves as a routing number. The situation is that we’re shipping to the stores and providing the customer with an electronic list which includes this routing number. Our only workaround at this point is to export the table to Excel, manually adjust the routing numbers, and then import it back into something we can use to transmit. I was hoping for an automated process so that there are fewer human hands (and tired brains like mine ) in the deal.

Reply | Quote

Well, now I’m pretty much totally confused. So more questions – what purpose does the requestID server? I think I under stand the Store bit, but is this a situation where customers are ordering things from stores, or is it where a customer orders something to be delivered to a store where it will presumably be picked up?

Reply | Quote

I think you will have to do this in a form based on the table. Put the following code in the Before Update event of the form (replace TableName by the name of the table):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varValue As Variant
‘ Test if we’re on a new record
If Me.NewRecord Then
‘ Check if there is already a RequestID for this store
varValue = DLookup(“RequestID”, “TableName”, “Store=” & Chr(34) & Me.Store & Chr(34))
If IsNull(varValue) Then
‘ If not, get a new RequestID
varValue = DMax(“RequestID”, “TableName”) + 1
End If
‘ Set the RequestID
Me.RequestID = varValue
End If
End Sub

Reply | Quote

Hans,

I can see that this would work in a form if we were manually adding data; however, what we want to do is create a table that can be electronically submitted and we have the table all ready to go except for the the request ID, which needs this consecutive number at the end.

Maybe if I use the example you provided in a function and call the function to loop through the data, this would work?

Reply | Quote

It is possible to use VBA code with DAO or ADO to loop through the records and assign requestID’s. If there are multiple records for the same store, how do you determine which record gets the first requestID? Or is that irrelevant?

Reply | Quote

Hans,

The example I have is sorted by store number. Thankfully, we have this built into the append query that creates the data.

Reply | Quote

OK, but is there a prescribed sort order within the orders for an individual store (for example by order date or something like that)?

Reply | Quote

Hans,

No, there is no order after the store ID.

Reply | Quote

Here is DAO code that will do what you want. Copy the procedure into a standard module and replace the table name (tblImport) and if necessary, the field names (Store and requestID) by the names you are using. Before running it, make sure that you have a reference to the Microsoft DAO 3.6 Object Library (in Tools | References…)

Sub FillID()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngID As Long
Dim strCurrStore As String
Dim strPrevStore As String
Dim strSQL As String

On Error GoTo ErrHandler

lngID = 10000
strPrevStore = “”
strSQL = “SELECT * FROM [tblImport] ORDER BY [Store]”
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Do While Not rst.EOF
rst.Edit
strCurrStore = rst![Store]
If Not (strCurrStore = strPrevStore) Then
strPrevStore = strCurrStore
lngID = lngID + 1
End If
rst![requestID] = lngID
rst.Update
rst.MoveNext
Loop

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

You can run this code by placing the insertion point somewhere within the procedure and pressing F5.

Reply | Quote

Hans and Wendell,

Once again, you guys are (and let me get this right this time) life savers! Or is it lifesavers? Anyway, I’ll try out the code and let you know how it goes.

Reply | Quote

Hans and Wendell,

Once again, you guys are (and let me get this right this time) life savers! Or is it lifesavers? Anyway, I’ll try out the code and let you know how it goes.

Reply | Quote

It worked! Now all I have to do is finalize the export and we’re back in business!

Reply | Quote

It worked! Now all I have to do is finalize the export and we’re back in business!

Reply | Quote

Here is DAO code that will do what you want. Copy the procedure into a standard module and replace the table name (tblImport) and if necessary, the field names (Store and requestID) by the names you are using. Before running it, make sure that you have a reference to the Microsoft DAO 3.6 Object Library (in Tools | References…)

Sub FillID()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngID As Long
Dim strCurrStore As String
Dim strPrevStore As String
Dim strSQL As String

On Error GoTo ErrHandler

lngID = 10000
strPrevStore = “”
strSQL = “SELECT * FROM [tblImport] ORDER BY [Store]”
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Do While Not rst.EOF
rst.Edit
strCurrStore = rst![Store]
If Not (strCurrStore = strPrevStore) Then
strPrevStore = strCurrStore
lngID = lngID + 1
End If
rst![requestID] = lngID
rst.Update
rst.MoveNext
Loop

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

You can run this code by placing the insertion point somewhere within the procedure and pressing F5.

Reply | Quote

Hans,

No, there is no order after the store ID.

Reply | Quote

OK, but is there a prescribed sort order within the orders for an individual store (for example by order date or something like that)?

Reply | Quote

Hans,

The example I have is sorted by store number. Thankfully, we have this built into the append query that creates the data.

Reply | Quote

It is possible to use VBA code with DAO or ADO to loop through the records and assign requestID’s. If there are multiple records for the same store, how do you determine which record gets the first requestID? Or is that irrelevant?

Reply | Quote

Hans,

I can see that this would work in a form if we were manually adding data; however, what we want to do is create a table that can be electronically submitted and we have the table all ready to go except for the the request ID, which needs this consecutive number at the end.

Maybe if I use the example you provided in a function and call the function to loop through the data, this would work?

Reply | Quote

I think you will have to do this in a form based on the table. Put the following code in the Before Update event of the form (replace TableName by the name of the table):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varValue As Variant
‘ Test if we’re on a new record
If Me.NewRecord Then
‘ Check if there is already a RequestID for this store
varValue = DLookup(“RequestID”, “TableName”, “Store=” & Chr(34) & Me.Store & Chr(34))
If IsNull(varValue) Then
‘ If not, get a new RequestID
varValue = DMax(“RequestID”, “TableName”) + 1
End If
‘ Set the RequestID
Me.RequestID = varValue
End If
End Sub

Reply | Quote

Actually, let me make my test data a bit more “user friendly”

Store Order requestID
MIN001 12930 10001
MIN288 40593 10002
MIN388 50405 10003
MIN388 50503 10003
MIK123 40384 10004

Reply | Quote

I guess I’m a bit dense today – do you mean a customer would have two special requests with the same number? And are they entered at the same time or as much as several days apart, such that if there is a currently unsatisfied special request open for a customer, you attach a new request to the same number? I presume there is some sort of customer ID also associated with the special request, so it sounds like you will probably need some code to check and see what the situation is before you assign a special request number. In that case, I would assign a separate autonumber field as the primary key to satisfy uniqueness needs, though you could use Store, Number and CustomerID in an index to speed retrieval. I hope this helps, but I’m not sure it makes sense.

Reply | Quote