I tried searching Google for this, but could not find a definitive list (if someone has one, please let me know the link). I would like to know what the best option is for an event to run some VBA code when a record is chosen on a form. Currently I have VBA code in the afterupdate event of some controls, and typically in the onload event of the form. For example I have a text box that displays a calculated date, that date is different for every record in the database. Which property should I place that code in? OnLoad? OnCurrent?
Thanks.
Full Version: Which Event To Use On Record Load?
Hi,
What version of Access are you using? You shouldn't need to use an event for that. You could just use an expression as the Control Source of an unbound textbox. Or better yet, include the calculation in your query.
Just my 2 cents...
What version of Access are you using? You shouldn't need to use an event for that. You could just use an expression as the Control Source of an unbound textbox. Or better yet, include the calculation in your query.
Just my 2 cents...
I'd agree, an expression is simpler and negates the need to perform any action, allowing it to auto calculate for each row.
However, there is expression resolution lag time (the form renders and then checks for expressions - and will do so in order).
If you have a continuous form - then you can't use an unbound control and insert the values.
Otherwise you can get an more immediate response by putting the value insertion in code. It would be normal to call that code from more than one place (hence having a dedicated sub which performs the insertion and calling that sub from various event procedures).
The OnCurrent event would be the one you're looking for yes. I see you say you're already calling it in the AfterUpdate event of the source controls involved.
It might be that you're pushing this value into a stored field. This would be calculated values, i.e. data which refers to other data in the same table results in this kind of effort and a denormalised schema.
I'm not saying I've never done it... But it's got to be for good reason rather than just, as theDBguy says, calculating it as required in a query.
Cheers.
However, there is expression resolution lag time (the form renders and then checks for expressions - and will do so in order).
If you have a continuous form - then you can't use an unbound control and insert the values.
Otherwise you can get an more immediate response by putting the value insertion in code. It would be normal to call that code from more than one place (hence having a dedicated sub which performs the insertion and calling that sub from various event procedures).
The OnCurrent event would be the one you're looking for yes. I see you say you're already calling it in the AfterUpdate event of the source controls involved.
It might be that you're pushing this value into a stored field. This would be calculated values, i.e. data which refers to other data in the same table results in this kind of effort and a denormalised schema.
I'm not saying I've never done it... But it's got to be for good reason rather than just, as theDBguy says, calculating it as required in a query.
Cheers.
QUOTE (theDBguy @ Aug 24 2011, 02:05 AM) 
What version of Access are you using? You shouldn't need to use an event for that. You could just use an expression as the Control Source of an unbound textbox.
Using Access 2010, and you are correct, that's what I ended up doing. I actually found the answer in a roundabout way while looking for something else.
What about with check boxes? for example I have 2 unbound check boxes, if I check 1 and leave the next unchecked, when I move to a new record 1 is already checked. I want them both to be unchecked. Does that go into the OnCurrent event?
Depending upon your needs, yes - the event raised upon navigation to a record in a bound form is the OnCurrent event.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.