Results 1 to 13 of 13
  1. #1
    shah1419 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2013
    Posts
    30

    after update at table level macro

    REspected,


    i have access database which has two tables. one table has fields (userid,mob,intime,otime,dated) and other table fields (id,msgto,msg).my requirements is when a userid enter/update in first table the the values (id,mob,msg) insert into second table. i want to create macro (fires) on without going into table/query or forms.

    please advised.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I don't think you can have macros run at the Table level. I don't think it recommended to do it like that.
    Users should NOT ever be accessing/editing Tables directly.

    So, how is the data getting into the Database?
    If manual user entry, they should be entering the data through Forms, and you can have VBA code run on the Form that will also add it to the other table.

    If the data is being imported, you should create an Import process that prompts them for the name of the file to import, and then runs some scripts (or Action Queries) after the import to add the new records to the other table as well.

    How are the values being imported

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    data macros do not exist in access 2003

    I'm not an expert on macros but I do know there is not a data macro for entering a record, only update, insert, change or delete
    One of the macro commands is to create a record, so I guess it can be done. You haven't said what fields you want to populate or with what, so can't help with that.

    This link should point you in the right direction https://sourcedaddy.com/ms-access/uses-data-macros.html

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    shah1419,

    Please confirm your version of Access. Data macros available in Access 2010 forward - not 2003
    See this link for an example of data macro

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Interesting, I never knew about Data macros (though my formal Access training was back on Access 2000).
    I was taught to keep people out of the Tables, and control most things via Forms, and just have always followed that and it has served me pretty well.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by JoeM View Post
    I was taught to keep people out of the Tables, and control most things via Forms, and just have always followed that and it has served me pretty well.
    That's good advice and I completely agree. Having a data macro doesn't mean that you let users into the table, it runs whenever the specified action happens. If a user entered a new record via a form, a data macro could fire in response. I've never used one, but I have used SQL Server triggers, which are kinda-sorta similar in functionality.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Having a data macro doesn't mean that you let users into the table, it runs whenever the specified action happens. If a user entered a new record via a form, a data macro could fire in response.
    I can definitely see the benefit of that, it would address data added no matter how or where it gets added. Sort of like a "catch-all".
    Of course, if an admin is responsible for occassional data clean-up, they would want to be aware of it, as they may need to disable it in certain situations.

  8. #8
    shah1419 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2013
    Posts
    30
    Click image for larger version. 

Name:	1.jpg 
Views:	20 
Size:	37.3 KB 
ID:	40079
    please advised how i can enable these macro events.
    i have tried access options>>trust center>>macro setting>> Enable all macros. but not succeed.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Last time of asking - Your title says you are using 2003

    what version of access are you actually using?

    what is the actual version of the database?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If your app is split you probably have to be in the back end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    shah1419,

    From your graphic in post #8, it appears you have a version that supports named and data macros.
    Did you look at the sample database in post 21 of the thread referenced in post #4?

  12. #12
    shah1419 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2013
    Posts
    30
    Quote Originally Posted by orange View Post
    shah1419,

    From your graphic in post #8, it appears you have a version that supports named and data macros.
    Did you look at the sample database in post 21 of the thread referenced in post #4?
    Yes but macro not enable in the picture. My access version is 2010. Please advised how I can enable. Or give vba code. On after update/insert on field.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is the app split? If so, are you in the back end?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 11-21-2017, 02:40 AM
  2. Replies: 2
    Last Post: 03-26-2015, 08:29 PM
  3. Linking Tables at the Table Level Vs. the Query Level
    By Tammy in forum Database Design
    Replies: 3
    Last Post: 12-03-2014, 01:34 PM
  4. Replies: 7
    Last Post: 05-15-2014, 12:03 AM
  5. Replies: 1
    Last Post: 03-27-2014, 07:34 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