Results 1 to 7 of 7

Thread: Table Question

  1. #1
    Join Date
    Oct 2002

    Unanswered: Table Question

    I have inherited a Legacy Database. This db has one table that is used to track sales. It contains one field that is sort of a yearly serial number. the program basically queries the table for the last number used then inserts the next numerical number into the new record. Unfortunately, if a record is deleted, that yearly serial number is no re-used. I want to be abel to query this table in order to find these missing yearly serial numbers. In order to find these records I created a table that contains numbers from 1 to 5000 and I use an join this table to the sales table but there has to be a better way.


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    LATER: ooops, missing LOOP in the original

    querying a database for data that isn't there - cute!

    how about...
    private sub findGaps()
      dim dabs as dao.database
      dim reci as dao.recordset
      dim reco as dao.recordset
      dim seqn as long
      dim ssql as string
      ssql = "SELECT theSeqWithGaps FROM theTable ORDER BY theSeqWithGaps;"
      set dabs=currentdb
      set reci = dabs.openrecordset(ssql)
      with reci
        seqn = 0
        do while not .EOF
          if !theSeqWithGaps > seqn + 1 then 'we have a gap
            reco!fieldLong = seqn+1
          seqn = seqn+1
      end with
    exit sub
    Last edited by izyrider; 05-05-03 at 14:17.

  3. #3
    Join Date
    Oct 2002

    Code Question

    I tried your code but I was not able to run your code. THe name of the table is called tblSales and the field name is CaseNum. The format of the Case Number is A00-0000 (i.e. C99-1234, M03-4321). The letter indicates the type of sale (C = Cash) the next two numbers indicate the last two digits of the sale year and the remaining four numbers are a sequential number that begins at 1 for each year. Can you resubmit your code using my values? Thanks!!!!

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    ooops again - there were still errors in my code, but it would not have worked on your case anyway due to your compound CaseNum.

    i know you inherited the db, but those compound CaseNum are a pain - they are so easy to construct when you need them from simple fileds like [SaleType], [Year], [Serial]

    here's corected code. (also a working .mdb since there is some other stuff to do - make a table to receive the output, make a delete query).

    one poss other change:
    gotn = !NumNum 'text to long
    gotn = clng(!NumNum) 'text to long

    Private Sub Gaps()
        On Error GoTo err_Gaps
        Dim dabs As DAO.database
        Dim reci As DAO.Recordset   'input recordset from tblSales
        Dim reco As DAO.Recordset   'output recordset to tblGaps
        Dim seqn As Long            'var will be forced to increment sequentially
        Dim gotn As Long            'sequence number part of tblSales.CaseNum
        Dim maxn As Long            'largest sequence number part in tblSales.CaseNum
        Dim ssql As String          'SQL to extract sequence number part of CaseNum
        ssql = "SELECT mid$(CaseNum,5) as NumNum FROM tblSales ORDER BY mid$(CaseNum,5);"
        Set dabs = CurrentDb()
        Set reci = dabs.OpenRecordset(ssql)
        Set reco = dabs.OpenRecordset("tblGaps")
        With reci
            maxn = CLng(!NumNum)                    'get max sequence number used
            seqn = 0                                'seed the sequence
            Do While Not .EOF                       'intil you run out of input
                gotn = !NumNum                      'text to long
                If Not gotn < maxn Then Exit Do     'all done
                If gotn > seqn + 1 Then             'here is a gap
                    reco.AddNew                     'add a gap record
                    reco!gap = seqn + 1             'record the number
                    reco.Update                     'save the edit
                Else                                'there is no gap
                    .MoveNext                       'get next input record
                End If
                seqn = seqn + 1                     'increment sequential pointer
        End With
        MsgBox "All done - check tblGaps!", vbInformation, "Done"
        Set reci = Nothing                          'tidy up
        Set reco = Nothing
        Set dabs = Nothing
        Exit Sub                                    'and quit
        MsgBox Err.Description, vbCritical, "Unanticipated Error in Gaps()!"
        Resume exit_Gaps
    End Sub
    Last edited by izyrider; 05-06-03 at 05:21.

  5. #5
    Join Date
    Oct 2002

    Code works on one computer but not the next

    Izy, I ran your code on one computer and it runs great!!!! excellent idea, I never would have thought of doing that way. However, the code will not run on another machine. I believe that a DAO active X driver is not loaded for example in the top of the Gaps() Proc when I attempt to dim the DAO objects (i.e. dabs, reci, and reco) the way you wrote it I get the following error message:

    Compile Error
    User Defined Type not defined

    and the DIM dabs AS DAO.database statement is highlighted


    I figured out a work around regarding the compound case number problem. I am separated the case number into parts: Type, year, serial number then I search for the number and then before it is appended into the table I combine the type, year and serail number. It appears to work fine.

  6. #6
    Join Date
    Dec 2002
    Préverenges, Switzerland
    it's a references problem: open any code window and...
    menu: Tools¦References..... and scroll down till you find Microsoft DAO 3.6 Library - and "tick" it

    happy gaps


  7. #7
    Join Date
    Oct 2002

    Talking Thank you

    It worked like a charm!!!! Thank you again!! I modified your code slightly in order to handle those compound numbers as follows:

    First I had to create a query to separate the number into parts. I tried to use the SQL in your code but it kept on crashing:

    ssql = "SELECT Type, Yr, CNum FROM CNumbers;"

    and after it selected the missing (Gap) number I formated it again before it was appended:

    reco!gap = !Type & !Yr & "-" & Format(([seqn] + 1), "0000") 'record the number

    Thanks again!!!!

Posting Permissions

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