Append current view record of Table1 to the end of Table2 using datamacro - O 365 Web App Access 2013

Question

Working from a View where a record in Table1 is open to be edited I'm trying to use a button click event to 1) Save the current changes to the current record. That works fine. 2) Use Create a Record in Table2 using SetField. I have no problems with the syntax of either command. Have used them to loop through one table and append records meeting criteria to the end of a second table. And to do that I need a DataMacro.

My understanding is that I need to run a DataMacro from the click event after the record has been saved. In the DataMacro I need to point to the record I just saved in Table1, Append a new record in Table2 - Where - using SetField. My only issue is the variable (or where to pull the parameter from) that let's me point to the correct record in Table1. I need the Table1.Title value of the record I just saved to find that record from the DataMacro and Set the value of 5 of the 9 field values in Table2.

I can't figure out how to pass a variable??? (is that how?) from the click event on the view to the DataMacro.

Thanks,

RJ

Answer 1

After a lot of searching I still haven't found a solution for what I'd like to do. Not sure if there is a way to pass a variable from a view to a datamacro that would let me find the record I just updated in my list view. That would allow me to find that record from the datamacro and do several things with it.

So I'm trying a second approach. May be a better solution. The real issue is I have a main table. That table is a 'Current Status' on about 1,000 products. So it has product name, manufacturer, date purchased, other general information about that product. Including the current status of the product. In use, in repair, available, and date it was last seen.

That product gets checked out for the reasons listed above. We update that table. One record for each product we have, in the table. Then each time the product moves someplace (in use, in repair, available), each time it's status changes, we need to save the product name, date, and where it goes (only those fields) in a secondary file which is product history. At the end of the date you may have 40 records in history for 1 product. Then from the product view you can click a button and see all corresponding history movements of the product. Be it 2 or be there 40.

Then the day comes when the product is taken out of inventory to be sold. One last movement which needs one last entry into history, a save to the originating file giving a final history of 'Out of Service', and a third movement which copies the product to a third table that goes to sales. They then start the selling process and can consult history if a prospective buyer asks.

The only way I've found to point to records from a datamacro is through conditional statements. This is greater than that, field contains this, date is this... a conditional query. But I haven't found any way to pass a specific field value in table1 (Product control number, Name, Record ID) that will let the datamacro open the record that was just updated with a new status and create the history record and, at the end of product life, create the sales record.

So now I've started looking for a way to save the same record back to Table1 after updating, and a copy of that same record to a temporary file that contains only one record, the one that was just saved. Then I can address the temp table directly in the datamacro, use a SetField to pass data, and append to append the record to other files.

Any thoughts?

Thanks,

RJ

Answer 2

Hi RJ,

>> I can't figure out how to pass a variable??? (is that how?) from the click event on the view to the DataMacro.
You could create a datamacro with parameter, and then pass a variable to datamacro. Here is a simple demo:

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 3

Thanks. I made progress. My thinking, given I finally understand how Parameters work (like dim varable as long) that the best thing would be to pull the record ID and, using the ID, find that record in the datamacro.

Parameter = svarRecordID, Number (No Decimal)

_______________________________________

For Each Record In Products

    Where Condition = [Products].[ID] = [svarRecordID]

And was able to save that as a macro. Then I moved on to creating a new record in the history file with:

Create a Record In History

    SetField

          Name [History].[ProductDescription]

          Value = [Product].[ProductDescription]

At this point I tried to save, received this error: Unable to resolve the reference to 'Product'. Common causes... so on and so forth.

Before using For Each Record I tried LookupRecord to grab my Table1 record from Products. Gave me the same error on the use of 'Product'.

So progress. And just getting a handle on parameters is great.

Any thoughts?

Thanks,

RJ

 

Answer 4

Found the issue. I removed the ID as variable value. I missed a key part - variable value comes from a control in the view, not an actual record field. Moved to title as planned, finished my variable connection in the control macro, cleared a bad connection by field type (Text into a Date field) and working like a champ.

Thanks much.

RJ

Answer 5

Hi RJ,

>> Before using For Each Record I tried LookupRecord to grab my Table1 record from Products. Gave me the same error on the use of 'Product'

Do you want to get ProductDescription from table Product or table Products, is it a field in Product or Products? If it is a field in Product instead of Products, you could not access [Product].[ProductDescription] directly within “For Each Record In Products”, you could try to get [Product].[ProductDescription] from Product, and pass it to data macro with parameters.

Since your original issue about passing variable to data marco has been achieved, if you still have any issues about this new issue, I would suggest you mark the helpful reply as answer to close this thread, and post a new thread for this new issue, and share us your table design, and details requirement.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 6

I'm interested to a solution based on your approach, I can try to suggest a workaround that I edopted in a similar situation and under time pressure to release a solution. 1) add an instrumental field in table1. It can be a boolean if monouse or a text / integer if you need to code more "messages". Let's assume a bbolean is ok. 2) the boolean should be ste to false by default 3) in the form that edites table 1 the boolean should be hidden, you should set it to true only from the macro that you recall from the button (macro1), do it before macro1 saves the edited record in table 1 4) you shoul build 2 queries and a macro2 .1 append query in table2 from table1 where the boolean is true .2 updates the boolean to false .3 the macro recalls the 2 queries in sequence 5) you can then launch the macro with 2 options .1 run macro2 from macro1 (or merge macro2 instructions ar end od macro1); this should work but you launch 2 queries at each update, it is slow, .2 find a way to launch the macro2 when you leave the edit form, this will bulk update your table2 with all the changes (never used but it should work) I'm aware this suggestion is very inelegant compared to your variable based approach but in web access applications fields behave more predictably than variables ...... GG