Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make table Qry 1 record per number with a range (Access Office2000pro)

    Hello.
    I have 90 checkbooks Each starts with a checknumber and ends with a check number. I have an entered into a table the following fields. issue_date_of_book :: First_check_num_in_book :: Last_num_in_book :: total_qty_checks_in_book::
    I would like to create a table that lists all the checks I have ever received... with these fields check_number::Last_num_in_book (Last_num_in_book being the foreign key to query against the first table) Some how this query looks at the number ranges and churns out record for the first number, the last number and each number in between.... Any thoughts???

    Thank very kindly for your help...

    Jason

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

    Re: Make table Qry 1 record per number with a range (Access Office2000pro)

    Create the new table manually, with the two fields (number, long integer). Let's say the tables are named tblSource (with issue_date_of_book etc.) and tblTarget (with check_number etc.)
    Activate the Modules section of the database window and click New.
    Select Tools | References...
    Make sure the reference to Microsoft DAO 3.6 Object Library is ticked.
    Copy and paste the following code into the module window:

    Sub FillTable()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim lngLo As Long
    Dim lngHi As Long
    Dim i As Long

    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset("tblSource", dbOpenDynaset)
    Set rstOut = dbs.OpenRecordset("tblTarget", dbOpenDynaset)
    Do While Not rstIn.EOF
    lngLo = rstIn!First_check_num_in_book
    lngHi = rstIn!Last_check_num_in_book
    For i = lngLo To lngHi
    rstOut.AddNew
    rstOut!check_number = i
    rstOut!Last_num_in_book = lngHi
    rstOut.Update
    Next i
    rstIn.MoveNext
    Loop

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

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

    Substitute the correct table names and field names, where necessary. Correct spelling is extremely important! You can run the code by clicking somewhere in the code and pressing F5.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table Qry 1 record per number with a range (Access Office2000pro)

    Hans.

    As Always your solution works great.

    Thanks

    Jason

Posting Permissions

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