Edit record before update in query subform Access VBA

0
votes

Here is what I have:

Two tables:

  • tblA: Index, Date_Time, User_Name, Event, Product_Serial_Number

  • tblB: tblA_Index, Date_Time, User_Name, Event_Tag

The field Event_Tag is used to classify different characteristics about the event like what you would see with YouTube videos, hence, the many to one relation.

I have a main form with the information of tblA displayed such as the User_Name and index. I have a subform with only the Event_Tag field displayed in a datasheet view whose control source is a query filtering by only the index taking from tblA.

I know in queries you can add records by typing in the blank record at the bottom (if it is enabled). I want to be able to type in a new Event_Tag record for the specific index in tblA. I am wondering it is possible to programmatically edit the record being inserted into the table before it is updated?

For example, I type in the blank record at the bottom of the subform Defective Motor and then use a vba code to take the date_time from the computer and the index/User_Name from the controls in the mainform and add it to complete the record, then insert it into tblB.

I hope this is a good enough example to show what I am trying to do. I appreciate the feedback if anyone can direct me how to solve this, or if there is a better way.

Thanks

1
vbams-accesssubform
why are you having username in both tables? tableA and B are linked anyway – Krish
Good question, I should have clarified more. I steal the name of the person who is using the form via environ(username) method. The person who created the record in tblA may not be the same person who creates a record in tblB. For simplicity I removed that from my initial description because I am more interested in the underlying method of completing a record before insert via a sub form. – Chris Moore

1 Answers

0
votes

use the "onBeforeUpdate" event for the subform. within that event

txt_date.value = now()
txt_username.value = your string

and so on