Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    consecutive number problem (Access 2000 SP3)

    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? <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Carpy Diem, it&#39;s .

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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?
    Carpy Diem, it&#39;s .

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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
    Carpy Diem, it&#39;s .

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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?
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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?
    Carpy Diem, it&#39;s .

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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?

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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 <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15> ) in the deal.
    Carpy Diem, it&#39;s .

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive number problem (Access 2000 SP3)

    Hans,

    The example I have is sorted by store number. Thankfully, we have this built into the append query that creates the data.
    Carpy Diem, it&#39;s .

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: consecutive number problem (Access 2000 SP3)

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

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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?
    Wendell

  13. #13
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive number problem (Access 2000 SP3)

    Hans,

    No, there is no order after the store ID.
    Carpy Diem, it&#39;s .

  14. #14
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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 <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> ).

    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.
    Carpy Diem, it&#39;s .

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: consecutive number problem (Access 2000 SP3)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •