Results 1 to 3 of 3
  1. #1
    benshudson is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2012

    Using the DLast or DMax functions within a SetValue action in a Macro

    I am building a purchase requisition (PR) database.

    the tables are (simplified) as follows:
    Table1 - tbl_PRList - contains only 3 fields with one of these fields being the "PR No".
    Table2 - tbl_PRData - contains all the item data, also with a field "PR No".

    The intent of the "item entry" form is to add items to the latest PR that has been generated.

    So, whenever an item that is to be placed on the new PR, you type in the item description, qty, etc in the form and then you press a button that saves this item to the main 'data' table (Table1). What I would like to do is, "on click" of this button, I'd like this record, prior to it moving to the next record, to set the value of the PR No field in Table 1 to be the same as the DLast value of the Table2 table.

    I hope this makes sense?



  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Metro Manila, Philippines
    Your concept makes sense and can be programmed ... but there's probably a better way.

    I would design this as a main form (single form) for the purchase request that conatains a subform (continuous form) for the purchase request item data. The subform is linked to the main form on PR No. (I hope there are no spaces in your field names?)

    When you wish to add new requisition items, navigate to the last (latest) requisition on the main form and add new lines to the subform. Access will take care of making the foreign key on the item data equal to that on the main form.

    If this fits your requirement let us know and I (or someone) will guide you through the setup.

    PS The subform does not have to be a continuous form so if there's a lot of item data you could make it a single form.

    PPS DLast returns a random value, not the most recent or last value! DMax returns the largest value in the domain - so ensure the keys are added in ever increasing sequence!
    Last edited by Rod; 10-28-2012 at 07:26 AM. Reason: Added PS and, later, PPS

  3. #3
    benshudson is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2012
    Thanks Rod, I am currently tinkering with the subform option you provided.
    I'll continue working on it and get back if I have any more questions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SetValue Macro
    By BrianF in forum Programming
    Replies: 1
    Last Post: 10-21-2012, 08:49 PM
  2. Replies: 0
    Last Post: 03-14-2012, 07:23 AM
  3. SetValue Macro action
    By rkalapura in forum Programming
    Replies: 3
    Last Post: 12-19-2011, 11:54 AM
  4. No SetValue in Macro editor
    By asabri in forum Programming
    Replies: 2
    Last Post: 09-21-2011, 01:39 PM
  5. Problem with SetValue Macro on Form
    By Accidental DBA in forum Forms
    Replies: 3
    Last Post: 04-01-2011, 01:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums