UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Level Trigger (after Insert,after Update), Access 2016    
 
   
shah1419
post Jan 21 2020, 11:10 AM
Post#1



Posts: 71
Joined: 10-January 13



CODE
Table-1:  Checkinout
Fields:    (userid,checktime,type,sensorid)
Values:   (1,21-jan-2020 05:34:06,I,101)
              (2,21-jan-2020 05:35:06,I,101)
              (3,21-jan-2020 05:64:06,I,101)
              
Table-2:  Msgout
Fields:    (id,msgto,msg,send)
Values:   (1,923334882088,'Respected Parents Cheild has arrived')
              (2,923334882088,'Respected Parents Cheild has arrived')

i want to use table level events. which one i used (after insert, or after update). when user insert a record in checkinout.userid it will create record in second table(msgout). when a user insert in checkinout.userid. the create record event fires but could not happening in second table.please see the attached pic.

Attached File(s)
Attached File  Untitled.jpg ( 64.53K )Number of downloads: 12
 
Go to the top of the page
 
BentBrain
post Jan 23 2020, 03:51 AM
Post#2



Posts: 522
Joined: 10-February 03
From: Thailand


HI shah1419.

Use the After Insert event to perform any actions that you want to occur when a record is added to a table.

Use the After Update event to perform any actions that you want to occur when a record is changed.


This is kind of old but the user is doing the same thing. create record in another table after insert
https://www.youtube.com/watch?v=wqFYvsemoZo

Let me know if that helps

Regards
BentBrain




--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
shah1419
post Jan 23 2020, 05:39 AM
Post#3



Posts: 71
Joined: 10-January 13



i have done all things which is shown in video. but could not succeeds. please find the attached file accdb.
This post has been edited by shah1419: Jan 23 2020, 06:18 AM
Attached File(s)
Attached File  att20_Backup12.zip ( 1.7MB )Number of downloads: 2
 
Go to the top of the page
 
June7
post Jan 23 2020, 06:44 AM
Post#4



Posts: 1,220
Joined: 25-January 16
From: The Great Land


Why are you referencing USERINFO table in the macro code? This won't work. Access does not know which record to pull value from.

PK/FK relationships not right. You have a composite key in CHECKINOUT yet do not save this composite key anywhere as foreign key. Set these two fields as composite index but use an autonumber as PK. Save this PK into msgout as FK. Advise not to use exactly same field name in multiple tables.

Msgout.id
[CHECKINOUT].[ID]

Msgout.msgto
[CHECKINOUT].[UserID]

Actually, by saving ID, really don't need to save UserID. UserID can be retrieved by linking tables in query. But why even have a separate table Msgout?


Name is a reserved word. Should not use reserved words as names for anything.

This post has been edited by June7: Jan 23 2020, 06:45 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th February 2020 - 12:34 PM