Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dcounting to increment (A2k3sp1)

    Hi All,
    I've been trying to increment 2 portions of a tracking number/receipt; formatted as SDSS-BK-1-00001. I have 2 comboboxes, the first which the user may choose the state code (SDSS) and the second the user selects a locality code (BK). In the afterupdate event of the 1st combobox I filter the records for the 2nd combobox. The singleton 1 indicates it is the 1st record for the state/locality of SDSS-BK (each state/locality therefore starts with a count of 1), while the 00001 indicates it is the 1st record period; i.e. the 1st record in the database. I am using the following code to try and accomplish this:

    <pre>If Not IsNull(Me!txtReceiptNumber) Then
    lngLocalityCount = DCount("ReceiptNumberPKID", "tblCaseData", "StateCode = '" & _
    Me!cboStateCode & "'" & " And " & "LocalityCode = '" & cboLocality & "'")

    If lngLocalityCount = 0 Then
    lngLocalityCount = 1
    Else
    'do nothing
    End If

    lngRecordCount = DCount("ReceiptNumberPKID", "tblCaseData")
    strFormatRecord = Format(lngRecordCount , "00000")
    strRecordSource = Me!cboStateCode & "-" & Me!cboLocality & "-" & _
    lngLocalityCount & "-" & strFormatRecord
    Me!txtReceiptNumber = strRecordSource
    Me.Refresh
    Me.txtRequestDate.SetFocus
    Else
    lngLocalityCount = DCount("ReceiptNumberPKID", "tblCaseData", "StateCode = '" & _
    Me!cboStateCode & "'" & " And " & "LocalityCode = '" & cboLocality & "'") + 1
    lngRecordCount = DCount("ReceiptNumberPKID", "tblCaseData") + 1
    strFormatRecord = Format(lngRecordCount , "00000")
    strRecordSource = Me!cboStateCode & "-" & Me!cboLocality & "-" & _
    lngLocalityCount & "-" & strFormatRecord
    Me!txtReceiptNumber = strRecordSource
    Me.Refresh
    Me.txtRequestDate.SetFocus
    End If
    </pre>

    The "If Not" portion of the code is used to catch users who choose a state/locality code, figure it is the wrong entry, then go back up and change their original entry. Hence there is no "+ 1" following the DCounts.

    Now the problem.......
    There are times when either count is incorrect (I have not seen where *both* counts are wrong, but I'm sure that could happen), both in a stand alone situation and when in a network environment (the db is split into fe and be) where up to 5 users may be using the db. I'll end up with something like SDSS-BK-1-00001 and later on have SDSS-BK-1-00012 or SDSS-BK-1-00001 and then next have SDSS-SF-1-00001. This db *needs* to be able to have both counts with no errors.

    I'm sure there must be a better way to do this, so would someone please point me in that direction.
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

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

    Re: Dcounting to increment (A2k3sp1)

    Both number parts run the risk that the same number will be issued twice. This will happen when a record is deleted, and also if there can be time between assigning the number and saving the record. So I don't think a DCount is the tool to use here.

    One solution would be to create two tables:
    tblStateLocNum with three fields: StateCode (Text), LocalityCode (Text) and Num (Number, Long Integer). Create a record in this table for each State / Locality combination, and initialize Num to 0.
    tblGlobalNum with one field, Num (Number, Long Integer). The table will hold only one record; initialize Num to 0.

    When you need a new tracking number, do it like this. The code uses DAO, so you need to have a reference to the Microsoft DAO 3.6 Object Library.
    <pre>Dim dbs As DAO.Database
    Dim rst As Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblStateLocNum WHERE StateCode = '" & _
    Me!cboStateCode & "' And LocalityCode = '" & cboLocality & "'", dbOpenDynaset)
    rst.Edit
    lngLocalityCount = rst!Num + 1
    rst!Num = lngLocalityCount
    rst.Update
    rst.Close
    Set rst = dbs.OpenRecordset("tblNum", dbOpenDynaset)
    rst.Edit
    lngRecordCount = rst!Num + 1
    rst!Num = lngRecordCount
    rst.Update
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    </pre>

    Since the tables are opened, updated and immediately closed again, the probability of conflicts is low.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dcounting to increment (A2k3sp1)

    Hi Hans,
    Very nice idea, <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> .
    This will also allow me to begin a state/locality with any given number. This is important as I am in the process of integrating and updating more of the "old" database into this "new" format.
    Thanks so much. I got 2 solutions for the 1 asking.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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