Probably an easy one - populate from from to subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!
I do not know how to code, so please bear with me. (This is probably pretty
easy, though!)

I have a form with several tabbed subforms. When I enter a TERM DATE for an
employee, I would like that term date to populate the ACCESS REMOVED date
field in the subforms...only where there are records, however (maybe not so
easy???).

Basically when an employee terminates employment, instead of me having to go
to every tab of all of the different systems he has access to and filling in
the term date, I would like to be able to only have to enter the term date on
the form...

Can I do this???
Thanks so much in advance! If you need any more information, I'd be happy to
provide it. thanks!
Tanya
 
If I understand your statement, you have an end of term date field in
multiple tables that you want to update when you flag an employee as
terminated.

Is this what you are needing to do?

Could you not just use one field in the table where your Employees
information is stored and make that part of the criteria for your other
records?
 
Tanya,

The easiest way to do this is to create multiple Update queries... by
updating the underlying table that each subform is based on... the subform
will be updated

UPDATE My_Table AS r SET r.Access_Removed = CDate(Forms!My_Form!Term_Date)
WHERE My_Table.Employee_ID=Forms!My_Form!Employee_ID And r.Access_Removed Is
Null;

My_Table is the name of your underlying table of the subform
(I am assuming that each tab of subform is bound to a separate table.)

My_Form is the name of your Main form where you have the field called
Term_Date

CDate Function is just casting whatever was typed in the Term_Date into a
date data-type (just to be safe)

The WHERE clause is very important, because you need to specify which
records (in the underlying table) that you want updated... otherwise it will
want to update EVERY record for EVERY person in that table... so you have to
have a combo box or field that contains the Emp_ID in the main form...

The reason that I have the statement about Null in there is that I am
assuming that you are removing employee access from multiple programs... and
are trying to keep track of it.... so if you remove access from one program
(because the user does not require access to just that one program) you
probably already filled out the ACCESS REMOVED date... and don't require
another record to be written to that table/subform

Once you have created an update query for each underlying table... you need
to create a macro... I like to SetWarningsOff in the first line (otherwise
you will get a pop-up of the number of records updated... for every UPDATE
query we are running)

Next line should be OpenQuery and near the bottom of your screen put in one
of your update queries

Next line should be OpenQuery and near the bottom of your screen put in one
of your update queries... and so on... for every UPDATE query that you
made...

Last line should be SetWarnings On

and SAVE your Macro...

Next open your form in design view and add a button (from your toolbox) and
go to miscellaneous ->Run Macro
click Next & choose the macro that you created
now label your button (this what will be printed on your button)
and name your button...

Right click on your button and go to the event tab


look at the "On Click" ... event procedure (should be in the box to the
RIGHT)
Click on the box with 3 dots (lookup button)
(You may have to click in the area where it says Event Procedure in order
for the lookup button to become visible)
Under where it says "DoCmd.RunMacro stDocName"
type the following
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

this will refresh your subform with the data that our update queries applied
to the underlying tables

Ctrl + S to save and X out... will take you back to your form... close the
form (it will ask you to save) save your changes & you're done!

Hope that helps... take care,

Veritas
 
Thank you so much for taking the time to write this all out - I will give it
a try!
Tanya
 
Back
Top