Updating two tables from one form

[bigal.nz]

Hello,

I have a form based a recordset of TableA which is a list of Events. The Form simply displays the current event in the recordset and there is a dropdown where the user can select what happens to the event.

If a Value 'Intervene' is selected on the dropdown where you select what happens to the event then the event needs to be tracked in terms of who it is assigned to.

Thus I have a new table called tbl_event_tracking. Here is where I have a few questions and it might be time to outline the basic data:

TABLE EVENT

ID DATE TEXT WHATNOW
========================
1 3/3/2017 ABC FILE
2 1/2/2017 DEF INTERVENE
3 1/1/2017 QRS FILE
4 5/4/2017 XYZ INTERVENE

TABLE EVENT TRACK

TRACK_ID ID DATE_ASSIGNED USER
============================
1 2 2/2/2017 John
2 2 3/2/2017 Mary
3 4 2/2/2017 James
4 2 7/7/2017 James
5 4 5/6/2017 Mary

So ID in TABLE EVENT TRACK is a foreign key to TABLE EVENT and I have created a 1:Many relationship.

I can show on my form the assignment history using a report in a subform (Thanks Minty) - but I need to add controls to the main form to:

a. display current assignment
b. allow changing of the assignment to another user (or to nobody / unassigned)

Do I need to change the underlying record source for my mainform or can I just use VBA for this?

Cheers

-Al

 

[MarkK]

...but I need to add controls to the main form to:

a. display current assignment
b. allow changing of the assignment to another user (or to nobody / unassigned)

This data, as I understand it, appears in the subform, so I would put/use controls on the subform to display/edit this data.

 

[bigal.nz]

This data, as I understand it, appears in the subform, so I would put/use controls on the subform to display/edit this data.

Yes so a log of the assignment history will appear in the subform and I need to add controls to dusplay current assignment as well as a field to assign new user.

I guess there is no reason I cant put this all on the subform. I will let you know how I get on.

Regards,

AG