Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    309
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Properly reading the recordset

    My scenario:
    1. Items for a job are kept in an items table and are shown in a subform when the job form is opened.
    2. When a delivery docket is formed rows from the the items table tblItems are copied to the delivery docket items table tblItemsDD. This way the text can be edited for the delivery docket with changes being made to the original items.
    3. Items cannot be added to the delivery docket after creation and of course, this is what they want to be able to do.

    This what I am trying:

    On the job form I have added this code to the subform enter event:
    Code:
    reccount = sbfItems.Form.Recordset.RecordCount
    To the subform exit event I added:
    Code:
    If sbfItems.Form.Recordset.RecordCount > reccount And [DelDocked] Then.....
    and it works. In that it only triggers if an item has been added to the list and the job is already flagged as being docketed.

    So I have my trigger event working ok.

    What I want to do now is read the values from the last (new) record and paste them into a new record in tblItemsDD

    I want to do it this way rather than reading from the last file in tblItems as I cannot guarantee it will always be the last item - multi-users

    But it dies in the position shown below

    Code:
    If sbfItems.Form.Recordset.RecordCount > reccount And [DelDocked] Then
    docket = [DDocket]   'read from form
    jobn = [Job #]          'read from form
    Dim rst As DAO.Recordset
    Set rst = sbfItems.Form.RecordsetClone
    rst.MoveLast
    iid = !ItemID        <<<<<<<<<<<<<<<<<<< it dies here with Compile Error: Invalid or unqualified reference with !ItemID highlighted
    iqty = !ItemQty
    idesc = !ItemDesc
    itcost = !ItemCost
    rst.Close
    Set rst = Nothing
    
    Set rst = CurrentDb.OpenRecordset("tblItemsDD")
                With rst
                .AddNew
                !ItemID = iid
                !DocketNum = docket
                !JobNum = jobn
                !ItemQty = iqty
                !ItemDesc = idesc
                !ItemCost = itcost
                .Update
                End With
    rst.Close
    Set rst = Nothing
    End if
    I can count the records but not read one what am I doing wrong?
    "Heading for the deep end"

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    309
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I worked it out!

    Set rst = sbfItems.Form.RecordsetClone needed to be Set rst = me.sbfItems.Form.RecordsetClone

    and I also needed to define my variables
    "Heading for the deep end"

Posting Permissions

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