Automatically update a field in a main form

How can i automatically update a field in a main form after a field was updated in the subform.

For example, i have a position's status flagged as vacant in the main form, however via the sub form i need to create amongst other fields a entry to flag the status field as filled and insert the staff member's particulars.

What VB code can i use in the after update event, to auto complete the same fields in the main form?

Any assistence will be greatly appreciated!!!!!

-------------Problems Reply------------

Well, to directly answer the question, you can use either VBA code or a Macro in the Subform's AfterUpdate event to populate the mainform field.

However, I'd very strongly suggest that these mainform fields should simply NOT EXIST!

Access is a relational database, and relational databases use the "Grandmother's Pantry Principle": "A place - one place! - for everything, everything in its place". The fact that a position is filled is stored in the child table; the staff member's particulars are (or should be) stored in a Staff table. That information need not be, indeed should not be, stored redundantly a second time in your mainform's table. You can display a "Filled" textbox on the mainform (without storing anything) by using the DLookUp function to see if there's a record in the child table for that position:

IIf(IsNull(DLookUp("PositionID", "ChildTableName", "PositionID] = " & [PositionID]), "Vacant", "Filled"))

but this will not store the "Filled" anywhere.

What Tables do you have? How are they related? What are the recordsources of the main and subform?

Thank you John for the valuable feedback.

Please understand that i am a novice when it comes to Access. Can help myself with some expert assistance though.

I have not yet created any tables. To start off with, i have a spreadsheet, which is exported from our payroll system, which has all employee static data i.e. job code, employment status etc.

We also have a seperate headcount tracker for all vacant positions, which is spreadsheet driven. My objective is to marry these two in an attempt to get a starting total for all positions in the company and from that point forward, maintain the positions via a Access DB. I obvisouly need to create a autonumber to represent a uniques position for each record in the connsolidated table (employee spreadsheet and headcount spreadsheet).

Once this is done, i need to keep track of each movement/ event pertaining to a position via a status field and status change date representing the following:

  • filled
  • vacant
  • headcount requested
  • headcount approved

The same goes for a Action/ Reason field, which should cover the following:

  • Terminated
  • New engagement
  • Transfered
  • Promoted
  • Demoted
  • etc.

One position might have many records, indicative to different status changes though. I must also be able to create new positions from scratch when necessary.

Ultimately, i need to report on positions with different statuses mentioned above and be able to get the last incumbent/ employee info for each position.

Hope the above makes sense. I would really appreciate some direction as to how to approach this in terms how my table and form structures should look like, with some guidence as to the vb code to be used.

Hope this is not too much to ask, however appreciate your assistence immensely.

My email is *** Email address is removed for privacy *** if required.

Kind regards


Well you need several tables here. You need a table with employee data. You need a table for the position data. You need lookup tables for the status and reason data. And you need a table for Assigned positions. This latter table would be one of the keys. This table would have a structure like:

EmpPositionID (PK Autonumber)
EmployeeID (Foreign Key)
PositionID (FK)

You could then have a query that would return only the most recent effective date to figure out what positions were occupied. Though an easier way (though not quite normalized) would be to add a Status to this table with values like: Current, Historical, Terminated. So while an employee occupies that position status is Current, When an employee is promotoed or transferred, the status changes to Historical. The Terminated status indicates the employee is no longer employed and this was the last position they held.

Category:Office Views:9 Time:2013-01-03

Related post

Copyright (C), All Rights Reserved.

processed in 0.242 (s). 11 q(s)