Calling afterupdate events with variables

Jeff,

I think the issue is the OP wants to be able to call the _AfterUpdate event for a variety of controls on "any form" from within a subroutine,  so they need a way with only the control name, to call the afterupdate event of that control.

Then this is too deep for me...
(don't have the time right now for something like this)

Jeff

Can't say I've ever tried anything quite like this, so I'm out of clues as well.

Jeff and Fyed,

Thanks for your interest in the puzzle.  Sorry I have been offline for the past hours.  I will most likely do something with Jeff's first answer.  But first, here is what I am doing:

I am managing the workflow of a database that handles distribution of manufacturers' products to their customers.  This workflow goes over a series of steps that each have its own form and set of tables.  It goes from Sales Order to either Purchase order or Service Order depending on the manufacturer, then from either of those to deliveries, then shipments, then invoices on those shipments to the end user, then invoices on those invoices to the manufacturers for the service.

The afterupdate event of a control in a order lines subform of each of the forms for those steps writes pricing lines or lot number lines (depending on the form) in another subform for each order line.  I have a public function that handles this event.

When creating a new purchase order from a sales order, delivery from a purchase order, container from a delivery, or invoice from a container, I have another public function that takes the relevant data from the table of the source form and writes it to the destination form (there is high normalization already, but I record information that is different in each like quantity, date, different parts of pricing . . . ).  I am trying to make this public function activate the afterupdate events of the forms so the pricing and lot number functions can be activated from one place only.  mbizup already gave me line 16 of the code above to handle the different form names (thanks mbizup), but now I am also trying to get the control name to change because the name of the control is necessarily different in each form.

If you have any ideas on how to rewrite lines 6 and 23 above besides using a bunch of if then statements as Jeff suggested initially, that would be great.  Otherwise I will go with Jeff's answer.

Thanks for reading all the way down to this line.  Thanks also for your help.

Chad

I believe what you're asking for can be done - you'd have to put change the Private in your after update definitions to Public, and if you can create a string variable to the effect of strAfterUpdate = Forms!YourFormName.YourControlname_AfterUpdate() I *think* that you could call those procedures using the Eval function: Eval(strAfterUpdate)

That said... I think the better approach would be to take a good look at those After Update events to determine what they have in common/what can be compiled in to a single sub... and call that sub passing form or control names as parameters if needed.

It sounds like your After Update events are basically writing lines of data to a table, and that can be done in a generic sub or function if the right parameters are passed.

Miriam,

I tried a variety of different formats of that approach, but never was able to get it to work.

I tend to agree that the best way to handle this might be to either go the route of the multiple IF/Then/Elses and then call the specific AfterUpdate event (method attributed to Jim) or do as you indicated and try to create a public routine that would handle all of the actions in the various AfterUpdate events based on the specific form/control being processed.

Hello ChadLittle,

Visual Basic doesn't support functions variables, and the only way to run a variable function by name is through Eval(), as mbizup said, while a variable sub can be run with Application.Run (meant for normal public subs, not event handlers in a subform).

Instead, let me give you an other idea on how to implement this.

You basically want a procedure to run with a certain number of different forms, having different subform names and different control names. What you need from each is thus: the subform, the control, and the routine to run “after update” of the control. You can define these in a class module, used as implementation template. That way, all your forms will be forced to expose these objects.

For example:

MyOrders — A Class Module

Option Explicit

Public TheList As Form
Public TheItem As Control
Public Sub ProcessItem(): End Sub

Select allOpen in new window

To test this, I have created a form, TestForm, with a subform, TestSubform displayed in the control subForm, having a text box, txtOrderItem, with an After Update event.

Form_TestForm — Class Module behind the main form

Option Explicit

Implements MyOrders

Private Sub MyOrders_ProcessItem()
    Me.subForm.Form.txtOrderItem_AfterUpdate
End Sub

Private Property Set MyOrders_TheList(ByVal Dummy As Form)
End Property
Private Property Get MyOrders_TheList() As Form
    Set MyOrders_TheList = Me.subForm.Form
End Property

Private Property Set MyOrders_TheItem(ByVal Dummy As Control)
End Property
Private Property Get MyOrders_TheItem() As Control
    Set MyOrders_TheItem = Me.subForm.Form!txtOrderItem
End Property

Select allOpen in new window

Form_TestSubform — Class Module behind the subform

Option Explicit

' changed to Public!
Public Sub txtOrderItem_AfterUpdate()
    MsgBox "Hello"
End Sub

Select allOpen in new window

With this, let's try to open the form, write something into the control, and run the After Update event handler:

Sub TestIdea()
    
    Const ANY_FORM As String = "TestForm"
    Dim This As MyOrders
    
    DoCmd.OpenForm ANY_FORM
    Set This = Forms(ANY_FORM)
    
    This.TheItem = "something"
    This.TheList.Dirty = False
    This.ProcessItem
    
End Sub

Select allOpen in new window

This is as close as VB gets to Object Oriented Programming. As you see, it makes the final code very clean and simple. The idea is that the object itself (the form) contains and exposes everything you will need from the outside: the subform, the control, &c.

Once you have updated each form to implement your template, you can use them all in the same way, and if you update one of them, everything you need to change is right there in the same module.

Please also read the help pages starting with the page “Implements Statement”.

I hope this will provide a better alternative solution
(°v°)

I think you need to use CallByName()
http://office.microsoft.com/en-us/access-help/callbyname-function-HA001228790.aspx

Thanks for that, aikimark, I had completely forgotten about this. You are right, this is the formal answer to the question, I just tried (in the context of my comment above)

    CallByName Forms!TestForm!subForm.Form, "txtOrderItem_AfterUpdate", VbMethod

Note: AfterUpdate isn't a method of the control txtOrderITem, but the event handler becomes a method of the form, if it's declared as public... So you need an object reference for the first argument, but the method name is a string, which can be built through code.

This is why I love EE!
(^v°)

It looks like that might be the ticket from reading the KB article, but I've been toying around with this for about 30 minutes and cannot figure out how to make it work.

I'm getting an "Expected variable or procedure, not project" error message, and it is highlighting "CallByName"

Maybe it requires a reference that I currently don't have in my test accdb (VBA, MS Access 12 Obj Libr, OLE Automation, MS Office 12 Access database engine object).

@fyed

It should work in any VBA environment with Office 2000 or later.  That is the VB6 standard level of the language.

“Expected variable or procedure, not project”

The error message indicates that you are using your own project name in that line. Perhaps you have created a database called “CallByName”, and the VB project inherited it? In any case, change your project name, and this error should go away.

From VBE: (Tools | ProjectName Properties...)

(°v°)

duh, thanks Markus!

It's been awhile since I made that newbie mistake.

;-(

My head hurts and my wall is dented.  I have been trying variations of what mbizup and harifang suggested and trying to understand the article that aikimark sent and it’s not working.

I have several variations in the code below.  All of them are commented out with further comments describing the error if that line is used and what line the debug points to.  

The best option is to have everything as below, except using line 27.  For an order with 2 records, the first pass works fine, but the second pass fails because of Runtime Error 3265 - Item not found in this collection with error pointing to Line 20.  I think I need to update the form or something in between passes or go back to form I left as a result of Line 27?  If Line 27 is commented out, the function works fine even with more than one record.  If there is only one record and Line 27 is used – the function works fine.

 Do you know what to do?  If so, it would be handy if we could make it work as a variable like line 8.

Public Sub CreateOrderLines(frm As Form, strFormName As String, strSubformName As String)

Dim rsSourceLines As Recordset
Dim strAfterUpdate As String
    
'strAfterUpdate = Forms![frmSvcO]![fraSvcOLines].Form.cboSOLinesID_AfterUpdate() 'Runtime Error 2450 - Microsoft Cannot find the referenced form 'frmSvcO'. Error on this line.
'strAfterUpdate = ("Forms![frmSvcO]![fraSvcOLines].Form.cboSOLinesID_AfterUpdate()") 'Runtime error 31005 - Access failed to evaluate one or more expressions because 'cboSOLinesID_Afterupdate' was referenced in an expression. Error on Line 26.
'strAfterUpdate = ("CallByName Forms![frmSvcO]![fraSvcOLines].Form, 'cboSOLinesID_AfterUpdate', VbMethod") 'Runtime 2482 - Access cannot find the name 'CallByName' you entered in the expression. Error on Line 26.

    Set rsSourceLines = CurrentDb.OpenRecordset("SELECT " & strTableName & "." & strFieldName & ", " & strTableName & ".UnitsID, " & strTableName & "." & strSelectField & ", " & strTableName & ".WarehouseID, tblSOHeader.PVID, tblSOHeader.dtmDate, tblItems.VendorsID " _
                    & "FROM tblItems INNER JOIN (tblSOHeader INNER JOIN " & strTableName & " ON tblSOHeader.SOHeaderID = " & strTableName & ".SOHeaderID) ON tblItems.ItemsID = " & strTableName & ".ItemsID " _
                    & "WHERE (((" & strTableName & "." & strSelectField & ") > 0) And  ((tblSOHeader.SOHeaderID) = " & frm![txtSOHeaderID] & strCreateOrderLinesWhere & varVendorWhere & ")) " _
                    & "ORDER BY " & strTableName & "." & strFieldName & "; ")
                    
DoCmd.OpenForm strFormName
DoCmd.GoToRecord acDataForm, strFormName, acLast
    
    Do Until rsSourceLines.EOF
            With Forms(strFormName).Controls(strSubformName).Form
                    .Recordset.AddNew
                    .Controls(strControlName) = rsSourceLines(strFieldName)
                    'Forms![frmSvcO]![fraSvcOLines].Form!cboSOLinesID = rsSourceLines(strFieldName) ' Using Line 26 has the same result if this line is substituted for the line above.
                    .lngUnitQuantity = rsSourceLines(strSelectField)
                    .UnitsID = rsSourceLines!UnitsID
            End With
            'Eval (strAfterUpdate) 'See errors if I use this in conjunction with lines 6, 7 or 8.
            'CallByName Forms![frmSvcO]![fraSvcOLines].Form, "cboSOLinesID_AfterUpdate", VbMethod 'Runtime Error 3265 - Item not found in this collection with error pointing to Line 20 on the second loop (The first loop works, but it there is another loop for a second record, it fails before it adds a new record.)
            With Forms(strFormName).Controls(strSubformName).Form
                    .Dirty = False
            End With
            rsSourceLines.MoveNext
    Loop

Select allOpen in new window

Thanks again!
Chad

If you need to pass arguments, instead of letting the form decide what to do, I would add a public variable in the General Declarations section of the participating forms.  You can use these variables like a public property for the form and store data for the form's routine(s) to process. Use a delimited string for multiple data values.

harfang,

I appreciate the way you looked at each error in turn and told me what is going on.  What is going on is I am over my head!  Besides that, if what you said is correct about not being able to use eval() and use variables to change the name of the form and afterupdate event, then I might as well go with Jeff's very first suggestion of nested if then statements.  

Or i might just throw elegance aside (not that nested if then's are elegant) and just copy past the public function in the class objects for each form and be done with it until I can return as a hobbyist to fix it better instead of as a business man under the gun to get something that works live. This is my first database to ever make after all so I should keep it simple (too late!) and get it wrapped up.

I will leave the question open another day or so and see if you or someone else can variablize the calling of AfterUpdates.  Otherwise I'll regret the wasting of everyone’s time, be appreciative of the lessons I attempted to learn, and move on to get stuck somewhere else further down the programming path.

Is it possible to call an afterupdate even with some sort of a variable? (Lines 6 and 23 of my original question's code)

Chad

The formal answer to that is: accept aikimark's http:#a38173383

I was under the impression that it works the first time through the loop, but not the second. Is that not the case?

« just copy paste the public function in the class objects »

Or, the other way around, extract the code in the various After Update events and place them in public Subs, using the same name for all forms. The events themselves calls that sub, and you can call it as well from the “outside”. A poor man's object oriented approach: you create a similar method (same name and arguments) for all your forms, so you don't have to figure out the different names. Come to think of it, it would already be this way if your controls had coherent names across forms...

Cheers!
(°v°)

Dear Experts,

I am sorry that it took me so long to close out this question.  I got depressed with this database project and allowed myself to get busy with other things for a while and occasionally tested different things you said even after I had rejected those things I was testing.  

I solved the problem though by taking first taking Jeff's first answer of using a bunch of If-Thens, but that quickly got more complicated and started exceeding the value of the shared public function.  When that happened I took his suggestion to make multiple subs.  I am trying so hard to make a clean database by having code in one place, but that code was getting so complicated and big it was making a bigger mess than various copies of various very similar code would make.  

I would never have been happy with this solution however if I did not explore options further.  Fyed's post offering to reach out to other experts was very encouraging and encouraged me to stick with it for a bit.  Harfang's diligence in teaching my why what I was trying was failing was extremely helpful in making me realize where things were breaking and how the fix is very complicated - which led me to Jeff's solution.

Thank you so much for helping me.

Chad

Thank you for the feedback.

It is surprisingly difficult to write good modular code. I'm seeing this right now in two other open questions, but I am also facing the opposite in my current job. I have inherited a database with many, many “various copies of various very similar code” and I hate going through them character by character to determine their differences and whether the upgrade I made in one of them should be propagated to the other. I spend most of my development time modularizing the code.

Although it is very desirable in the long run — even only for maintenance, debugging, and documentation — it's difficult to find the right mechanism and the right place.

I have found that it is often best to separate conceptually the procedural model from the interface model. In your question, you want to add an order line (a procedure) while using forms, controls and control events (all part of the interface). This tells me that a portion of the procedure has slipped into the interface, and has become too intertwined. That is a very common problem in Access applications. I know that it's a broad generalisation, but try it has helped me before. Go back to the concept of adding an order line, determine its input (data) and output (a new record or an error condition). Create a procedure that does only that and does it well. Then call this from your interface, not passing interface objects but the raw data, and handle any resulting error condition.

Anyway, success with your project!
(°v°)

Thanks Markus, wise words as always.

/gustav