Assigning an event at runtime to a control (1 Viewer)

[Riverburn]

Dear people of the Access forums,

Once more I call for help.

I thought that this current problem I have would have a simple solution that is easily found on the internet, but apparently I am wrong because google has no answer for me.

what is my problem?
every employee has an amount of assignments he/she is working on during one week. my form should be able to assign new tasks to the employee.
in practise:
if an employee for example has three assignments in week 21, there appear four combo boxes, three with the current assignments (with the possiblity to adjust them) and one empty to assign a new task.
by the way, this is not done dynamically but simply by turning premade comboboxes visible (I didn't had the patience to learn how to make them dynamically).
at any rate, this last empty combobox, when it has been used to assign a new task should automatically call an afterupdate event that will make a new empty combo box appear.
however, to do this, I have to assign the event only to the last empty combo box, but I don't manage.
I'm playing with this:

boxAssignment_5.AfterUpdate = Assignment_After_Update()

(5 being a random number, it could be any number)
which naturally doesn't work. so how is it done well?

I do have found a solution. Since all my combo boxes are premade, I just gave them all the same AfterUpdate event handler and with a global variable it works fine. However I just got my code cleaned up, and I want to keep it as efficient as possible

another smaller question: in many other object orientated languages, global variables are a blight upon a program, does that go as well for VB ?

 

[vbaInet]

You can call the sub in the After Update event of boxAssignment like this:

Private sub boxAssignment_5_AfterUpdate()
   Call Assignment_AfterUpdate()
end sub

The "Call" statement is optional but I prefer putting it.

 

[SteveSchapel]

Riverburn,

Can I go back a step for a moment?

Am I correct in my interpretation here, that you have multiple fields in your table, Assignment1, Assignment2, Assignement3, etc, to allow for the multiple assignments for each employee?

If so, is your database design set in concrete, or can you consider a review?

 

[Riverburn]

Thanks all for the (very) quick replies

The thing is vbaInet, that I am trying not to use that tactic. it's what I have now

Private sub boxAssignment_1_AfterUpdate()
   Call Assignment_AfterUpdate()
end sub
Private sub boxAssignment_2_AfterUpdate()
   Call Assignment_AfterUpdate()
end sub
Private sub boxAssignment_x_AfterUpdate()
   Call Assignment_AfterUpdate()
end sub
 
Private sub Assignment_AfterUpdate()
'make combobox i+1 appear
//
end sub

I first was hoping that all controls could get one single AfterUpdate event but every time I put the name of the function in the property window (something that works like a charm in Visual Studio), access tells me that it cannot find the Macro.

and you're right about the Database Steve. there are two tables
1) Assignments
Primary_Key - Assignment_Code - Assignment_Name - Assignment_Priority - Assignment_descrip
2) Assigned_Hours
basically this has a row for every day, assignment and employee working on that particular assignment. it's that inbetween table to avoid a many on many relation. (4NF)

my boss is open minded about me changing his database, but I need some pretty good reasons.
so fire away those suggestions

 

[SteveSchapel]

Riverburn,

Well, I am having difficulty understanding what you have there now. Which field(s) are these boxAssignment_x comboboxes bound to?

I had begun to suspect that your problem was related to a poorly normalised data schema. But now I am not so sure.

But if you have it set up correctly, then the whole data controls question that you have raised is so easily managed via a subform, in which case Access automatically provides a blank row for the entry of the next record, without you needing to do any code or other shenanigans.

 

[Riverburn]

hmm, in which case I should really learn how to work with Access his buttons

it probably doesn't help my case, but I'm actually really unfamiliar with all that access can do for me automatically. I'm always kind of afraid of those automated things because I don't know what happens under the hood.
Code on the other hand gives me complete control and I know what can and cannot be done.
but maybe I should step off my code-throne and see what access can do for me.

to answer your question:
an employee has assignments, or can have new assignments assigned to him. that is what those comboboxes are for. they give the user the ability to assign a new assignment to the employee from a pre-made list. every time a new assignment is given, a new combobox should appear that can contain another new assignment, and so on and on. it are those new comboboxes that are the problem.

I'll see what this subform in access is

thanks the advice

 

[SteveSchapel]

Riverburn,

Yes, subforms are the "stock standard" way to approach the management of data related one-to-many in Access.

 

[ChrisO]

G’day Riverburn and Steve (long time no see, Steve)

If you’re still interested in the assignment of the event handler property you can try soft coding it as in the following;

Behind the Form: -

Option Explicit
Option Compare Text


Private Const conX As Integer = 5


Private Sub Form_Open(ByRef intCancel As Integer)
    Dim intSuffix As Integer
    
    For intSuffix = 1 To conX
        Me("boxAssignment_" & CStr(intSuffix)).AfterUpdate = "=Assignment_AfterUpdate(" & intSuffix & ")"
    Next intSuffix

End Sub


[color=green]' This must be a Function even though its return value is not used.
' If it were a Sub it would be called more than once on each update.
' I think that problem has been fixed in A2K7.[/color]
Private Function Assignment_AfterUpdate(ByVal intNum As Integer)
    
    MsgBox intNum

End Function

If you have the property sheet open at run time you will see what goes into the After Update property.

 

[SteveSchapel]

Yes, g'day Chris... nice to see you. Hope all going well with you.

 

[ChrisO]

Yes, not going too badly. Good to meet you, and the others, at the DevCon last year.
(I surprised myself by not making a comment on RC’s two ear rings. I must be getting old. )

 

[Riverburn]

Hey Christ0

I am Sooooooo grateful for the code you provided.

Option Explicit
Option Compare Text


Private Const conX As Integer = 5


Private Sub Form_Open(ByRef intCancel As Integer)
    Dim intSuffix As Integer
    
    For intSuffix = 1 To conX
        Me("boxAssignment_" & CStr(intSuffix)).AfterUpdate = "=Assignment_AfterUpdate(" & intSuffix & ")"
    Next intSuffix

End Sub


[COLOR=green]' This must be a Function even though its return value is not used.
' If it were a Sub it would be called more than once on each update.
' I think that problem has been fixed in A2K7.[/COLOR]
Private Function Assignment_AfterUpdate(ByVal intNum As Integer)
    
    MsgBox intNum

End Function

this works like a charm, and does exactly what I want it to do. I wonder what I did wrong when I did something alike, but no problem, it works now and I'm happy. Huuuuray.
I was throwing handles and addhandler around but could get nothing to work. assigning specific events to a control didn't work either, but your code is exactly what I need, thanks once more

 

[ChrisO]

No problems, Riverburn, good to see another happy punter.

Worth having a play with; good way to shove an awful lot of code into a very small volume.