Full Version: How to trigger AfterUpdate after VBA fieldvalue change?
UtterAccess Forums > Microsoft® Access > Access Forms
want to trigger the AfterUpdate event after I have changed the value of a field in VBA.
In this context I have made a Calendar form, that changes the date in another (sub)form by:
Dim Ctr as Control
Set Ctr = Screen.ActiveControl
Ctr.Value = <NewValue>
At this point I have tried to code:
SendKeys "{TAB}", True
Bu I would like to code:
Ctr.Dirty = True
This is not supported; how can I code it differently?
You should just be able to explicity call the AfterUpdate procedure. If the control is say ctlYourControl then you could write
to call it. Since it's a private sub, if it's outside of the scope where you're executing code, then you can create a public sub within the appropriate form which in turn calls this private sub.
Hope that helps. frown.gif
I have the problem that in the Calendar Form (where I must code Ctr.Value =) I do not know what the (sub) form or Control name is (Form name would be Screen.ActiveForm.Name, but subform? and control name would be Ctr.Name) and even if so I cannot call the appropriate routine dynamically, for the name would be different for each call. How can I resolve this?
Just to be sure I understand:
You have a form, on which is a subform. From that subform, you are opening another form, which is basically a calendar control used as a date picker. The date picker form needs to set the value of a control on the subform, after which you want code on the subform to run, and you want it to be generic, so it can be called for multiple forms and controls.
Is this it? If so, here are two methods:
Method 1
1) Open the datepicker form as a DIALOG. This will stop any and all running code in the calling form until the dialog is closed or made invisible. Your datepicker form will set the value of the control and then close, at which point code will resume running in the calling form. You can do your updates or whatever other code you need directly in the lines of code following the DoCmd.OpenForm, since they won't be executed until the datepicker form has done its thing and closed.
2) Pass the name of the form/subform/control/etc. to the datepicker form using the OPENARGS property. Parse it out in the open event of the subform; now, when it is time for the subform to close, you use the values you passed it to set the value of the control on the appropriate form/subform.
Method 2
1) Open the datepicker form as a DIALOG. Code will stop running in the calling form until the dialog is closed or made invisible.
2) When a date is picked, instead of closing the datepicker form in its code, set its VISIBLE property to FALSE. The form will still be open, but not visible. This will cause the code to resume in the calling form/subform.
3) In the code that resumes in your calling form, grab the value from the now hidden datepicker form, put it in the appropriate place, close the datepicker form, and do whatever updates/etc. are necessary.
The second method may be a bit easier to implement.
Just a note that here is a THIRD way to do a calendar:

This one uses an API class, so no datepicker form is needed.

Ane here's a list of a whole host of other ready-made samples for doing calendar/date-pickers:
Edited by: schroep on Tue Jan 10 11:31:29 EST 2006.
Okay, I think I have a better understanding of your issue--sorry I didn't quite follow you before. The only manner in which I'm aware that you would be able to accomplish your goal without putting any extra code in to each calling procedure would be to change the AfterUpdate subs to Public subs and then use the Eval function. The eval function would look something like
val ("Forms![" & Ctr.Parent.Name & "].[" & Ctr.Name & "_AfterUpdate]")
Remember, though, that the AfterUpdate sub must be public for this to work. Additionally, you may run into a problem depending on your Access version and your macro security level setting which you may need to set to low.
However, beyond that, recommend just calling the AfterUpdate event explicitly from the same sub that opens your calendar form.
HTH frown.gif
Dear Peter, Jack
irst thank you very much for replying my post.
Peter: you are correct in your analisis of the question, however the Calendar form is just an example, as I want to use this method for picking Departments, Ledger Accounts etc that I yet have to develop.
Jack: I have tried your method first, because I think that would be the most generic solution. However I have pasted your solution in the Calendar PutForm function (where Ctr.Value = <new value> is coded) like this
Private Function PutForm(Waarde) As Boolean
Dim Ctr As Control
Set Ctr = Screen.ActiveControl
Ctr.Value = Waarde
Call Eval("Forms![" & Ctr.Parent.Name & "].[" & Ctr.Name & "_AfterUpdate]")
PutForm = True
End Function
Note: Waarde is Dutch for Value (I am Dutch)
But on running this code error 2465 occurs: cannot find field txtDatVanaf_AfterUpdate
txtDatVanaf being the correct Ctr.Name of the control in the correct Ctr.Parent.Name (frmDatum) form name.
So appently the coding must be slightly adjusted. Can you tell me how?
I would be ever so gratefull
An example database is attached that will hopefully assist you. It's very basic and doesn't make sense by itself. Basically, you use another form to insert code into a text box on the original form when it opens (just double click either text box). After you enter text and click the button on the form that pops up, it should enter that text onto the original form, trigger the AfterUpdate event, and finally close itself. Hopefully it will work for you! frown.gif
Dear Jack,
How stupid of me!!!!! Although you explicitly told me to make the AfterUpdate Sub Public, I somehow overlooked it anyway. Making it public your coding works fine. Sorry about that. Thanks very much for your trouble. This is exactly what I was looking for!!!! blush.gif blush.gif thanks.gif yayhandclap.gif thumbup.gif
Not a problem--happy to assist! frown.gif Just remember that depending on your version of access, you might have to set macro security to low to make it work (which you may not want to do). I know you have to do this in Access 2003--not sure about previous versions.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.