Updation of main form based on the sub form data

Question

Hi

I need expert help in completing my project. I have two questions to ask. I just started understanding the concepts of ms access. I have created two tables one table holds the master data of employees and the second table holds the transaction data like date of promotion and promoted as etc.

I wanted to update a field in the main form with the latest record from the sub form (date of Promotion). Sub form will hold more than one record of each employee. Similarly i want to capture the previous row data in the next line (these data types are date/time).

Thank you for the help in advance

Answer 1

Hi,

In relational database design, there's often no need to store the same information (date promoted) in multiple tables. Whenever you need to know or display the last time an employee was promoted, you can use a query with a calculated column using the DMax() function.

Hope it helps...

Answer 2

Hi raghu,

In addition to the correct answer from theDBguy, here are a few extra tips:

You can add a control on the main form. You can set the ControlSource property of the control to an expression. This property is normally set to the underlying field. In this way, the control element can display the value of the field.

But the ControlSourse can also be set to an expression. The advice that theDBguy has given you is a VBA function that you can use in a query, but also in the ControlSource property. A VBA function such as DMax can also be regarded as an expression. You place the expression in the ControlSource property: =DMax(Expr, Domain, Criteria).

More about the function DMax, type in the VBE editor DMax and press F1.

If you want to do it even more efficiently, you create a procedure Function. This is called a "user-defined function". You can write that function in such a way that it gives the same result as the above expression with DMax. Because a "user-defined function" can also be used as an expression. An expression can therefore be anything. In your case, you only need to pass on the employee's ID to the function; in the function (suppose you call that MyFunction) you then arrange the other criteria. In the ControlSource property you place: =MyFunction(varID) for example.

Great advantage to create a "user-defined function" is that you simply call your function when needed. And because you always call the same function, you ultimately have less maintenance, you can perfect your procedures and always get the right result. Place that function in a standard module. In this way you write reliable code. It is therefore very worthwhile to look at how you write functions. If this is all too difficult for you, you can also place the expression =DMax(Expr, Domain, Criteria) in the control as indicated.

Good luck.