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

[benshudson]

Hi,
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?

Cheers
Ben

[Rod]

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!

[benshudson]

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.