Results 1 to 4 of 4
  1. #1
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92

    Query a different table in a macro

    I don't know if what I'm trying is doable through a macro or not. I have some tables that are updatable through a number of forms, so to make tracking changes easier I decided to add an after update macro to the tables instead of programmatically tracking the changes in each form. Each macro is very simple, after a table has been updated it checks if the updated field is a certain one, if so it adds a record to the changes tracking table registering the user making the change, the date, the table and field that have been updated and the previous and current values.

    There are some tables in which I would like to add some particular info depending on the value of a table other than the table that's been updated. I don't see how to run a query from a macro, I would need to check a value in another table with a Select query then add a value to the changes tracking table depending in the result. Can that be done in the macro itself or would I need to launch a custom VBA function from the macro? And if so how can I launch the function and store a, say, boolean return value from the macro?

    Thanks!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I decided to add an after update macro to the tables instead of programmatically tracking the changes in each form.
    I've never heard of using a macro from within a table but like most long term users of Access I have very little, if anything, to do with macros.
    With a better understanding of the database and a more detailed explanation of your requirements, I sure that it could all be done using vba. Perhaps you could tell us more and perhaps post a copy of the db.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    are you talking about data macros?

    I've never had the need to use them, preferring to maintain control via vba but a quick look at the actions you can take suggests you can use the lookup action for your query followed by the edit action. Don't understand your comment about programmatically tracking the changes in each form - just use a standard module and pass the parameters required

    Be wary of unintended cascades of updates which can happen if a change in one table results in a change in another.

    perhaps this link will help

    https://sourcedaddy.com/ms-access/wo...variables.html

  4. #4
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Thank you both!

    Yes, I can definitely do it through VBA code and know how to do so. And yes, I can build a track changes function and just call it from each form/event triggering changes I wanna keep track of so it's not that much of a hassle. Two reasons I wanted to try to do that through an after update table macro though (closest to a SQL trigger to me). First, I can build/rebuild/add any new form (since I'm still updating and trying to improve the form structure) without having to worry about forgetting to programmatically track the changes to those tables. Second, if someone (myself included) tries to go full Rambo on the tables (cause even with an autoexec form and the Access UI hidden we know that's totally possible) I'd still be able to track any changes made to those tables. Those are the main reasons for me trying to do that through a macro even if I despise them and don't really know how to use them properly.

    So yes, there is that lookup option I've occasionally used to search and edit a record, but I can't see how to use it for what I intend, as I would need to check a field from that record from that other table and add a field to my track changes tables depending on that. Here's a small schematic description of what I mean (won't copy the macro cause it's in Spanish and it wouldn't be too clarifying). Let's say for this example I want to add a certain value to the track changes table depending on the location of the workstation it's in. Current macro of that component's table would be something like this:

    Code:
    If Updated = Workstation
       New variable userCH = GetUserName()
       New Record in Track changes
          SetField User= userCH
          SetField Date = Now()
          SetField Table = Whatever the table is
          SetField UpdatedField = "Workstation"
          SetField OldValue = Old.Workstation
          SetField NewValue = Workstation
    Now, assuming I want to add a new field depending on the location of the workstation, I would do something like If (Select Location From Workstations Where.....) and alter the field accordingly. I guess I can build a function and call it passing the updated record or even just the workstation ID as a parameter. I guess I could even build a VBA function that built the whole TrackChanges record and just call it with a few parameters and let it do its thing. Just wanted to know if there's a way to do it through macro commands, because I don't see a way to look for that record in workstations, get the desired field and edit the track changes record accordingly without resorting to VBA.

    Ps: Sorry Ajax, missed your link, looks promising, will try that and if I can't get it to work I'll resort to VBA functions. Thank you very much!

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

Similar Threads

  1. Replies: 5
    Last Post: 02-06-2021, 02:41 PM
  2. Replies: 8
    Last Post: 06-23-2014, 12:13 PM
  3. Replies: 5
    Last Post: 04-05-2014, 08:52 PM
  4. Replies: 2
    Last Post: 01-31-2013, 04:41 PM
  5. Macro Help with Combing Table with Date Query
    By Jerseynjphillypa in forum Programming
    Replies: 15
    Last Post: 05-03-2012, 10:25 AM

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