Form event control updated

[Movian]

Hey,
i have an interesting situation. I have a list of "required" fields in a table. When the form is opened the system loops through this list and makes each field on the form Blue if there is no value in that field.

Each field then has an after update even that re runs this check so that if there is now data in the field it changes the color back to normal. However the list of required fields changes for each client "Hence why its a table list".
Is there a way i can set an event for all fields on a form "On Form Control Update" in essence. Otherwise i will have to add one line of code to each control manually for each client every time i alter the list in the table. I just want to change it so that all i have to do is alter the list without adding any code as we deploy .accde files.

Thanks in advance

[CreganTur]

The only thing that comes to mind is creating a ForEach loop to interate through the objects collection on your form. You'll need to add in a check to make sure it only looks at an object if it is a certain type, like Textbox. Then you can use DLookup to see if that textbox's name exists in your table list. If it does, then turn it blue; if not, ignore it.

Does that help?

[Movian]

actually thats the part i have done already.
I use an while not EOF

loop through the list of field names and set each one blue (Quicker to loop through just the list of names than each control and compare)

So there is no problem turning the fields blue. The issue is changing them to say white "After" the user updates the field (As the field is no longer empty it no longer needs to be marked as blue to reflect this fact) at the moment i am puting in a check on the after update event for each field What i need to is like a global after update event for the form. So that it does it for each control. This way i can just add a field to the table without having to add an after update event to each field in the list manually, or similar. Does that make more sense ?

[OBP]

Can you use this-
http://www.vbaexpress.com/forum/showthread.php?t=30403

[Movian]

I think you may have just shown me the solution to my problem! :D

However i will have to tweak quite a bit.... and change from click even to after update.

This feels like one of those major commands/types you wish you knew from the beginning. I think i could use WithEvents for a whole host of applications within the system. Let me play around with this and see if i can get it to work. If i can, then i will mark as resolved :) thanks for the help! i would never have found that thread through my searches.

[Movian]

Mabye i could get a little help implementing this?

I have attempted to implement this to my own ends. I have no problems compiling, however it just doesn't work, after i updated the fields the function is not called.

Here is What i have attempted to implement in the form
(The load event of the form called this function)


Public colRequired As Collection

Public Sub CreateRequiredCollection()
Dim myrs As DAO.Recordset
Dim clText As clsRequired

Set myrs = CurrentDb().OpenRecordset("RequiredFieldList")
Set colRequired = New Collection

While Not myrs.EOF
Set clText = New clsRequired

Select Case Me.Controls(myrs.Fields("Fieldname").Type)
Case "Textbox"
Set clText.mLText = Me.Controls(myrs.Fields("FieldName"))
Case "Combobox"
Set clText.mlCombo = Me.Controls(myrs.Fields("fieldName"))
End Select
colRequired.add clText
myrs.MoveNext
Wend

myrs.Close
Set myrs = Nothing
End Sub
Then here is my complete class Named clsRequired


Option Compare Database
Option Explicit

Public WithEvents mLText As TextBox
Public WithEvents mlCombo As ComboBox

Private Sub mlText_AfterUpdate()
SetRequiredFields
End Sub

Private Sub mlCombo_AfterUpdate()
SetRequiredFields
End Sub
and the function the afterupdates call

Public Sub SetRequiredFields()
Dim myrs As DAO.Recordset, MissingFields As Boolean
MissingFields = False
Set myrs = CurrentDb().OpenRecordset("RequiredFieldList")

While Not myrs.EOF
If IsNothing(Forms("frmMain").Controls(myrs.Fields("FieldName"))) Then
MissingFields = True
Forms("frmMain").Controls(myrs.Fields("FieldName")).BackColor = RGB(102, 204, 255)
Forms("frmMain").Controls(myrs.Fields("FieldName")).ForeColor = RGB(255, 255, 51)
Else
Forms("frmMain").Controls(myrs.Fields("FieldName")).BackColor = RGB(255, 255, 255)
Forms("frmMain").Controls(myrs.Fields("FieldName")).ForeColor = RGB(0, 0, 0)
End If
myrs.MoveNext
Wend

myrs.Close
Set myrs = Nothing
If MissingFields = True Then
Forms("frmMain").ReadyforBilling = "Not Ready For Billing"
Forms("frmMain").ReadyforBilling.ForeColor = RGB(200, 0, 0)
Else
Forms("frmMain").ReadyforBilling = "Ready For Billing"
Forms("frmMain").ReadyforBilling.ForeColor = RGB(0, 200, 0)
End If
End Sub

[Movian]

Ok with a couple alterations i eventually got this working partially.

HOWEVER i have found one limitation with my setup. It only appears to work if the control already has code in the matching event. For example if my Lastname field has an afterupdate even that checks for other records with the same last name then after the update it will fire this dynamic. However if it does not have an afterupdate event already in existence it will NOT fire the dynamic. Any thoughts ?

[Kicker]

Thanks OBP. I was just going to submit the idea to Movian.

Movian. One of the key elements to having the "event" work regardless of whether it is a click event or an update event is to have the Properties for the control for the event set to [Event Procedure]. Changing the code for what OBP directed you to shouldn't take much.

Can you post a sample of what you are trying to do?

[Kicker]

I just tried the Afterupdate event with the code used in my Public Click Event example OBP linked earlier. It worked.

If you can't post the example, use the for each ctl..... code in the form load shown in the example and put it in the cls module.

[Movian]

Ok, i s there any way to set all controls to have the event procedure property set ? As i don't know which fields will be set and we have a large number of Subforms (50-60) with at least 80 + fields on each i need a good way to have all of them have the event procedure in the after update field to get this code to work

~Edit

Looks like i should be able to use field.AfterUpdate = "[Event Procedure]" To set this automaticly.... however it doesn't apear to working. If i can figure out why i should be able to loop through all Text Fields and combo fields

[CreganTur]

Looks like i should be able to use field.AfterUpdate = "[Event Procedure]" To set this automaticly.... however it doesn't apear to working. If i can figure out why i should be able to loop through all Text Fields and combo fields

You are going in and assigning a call statement for each field's AfterUpdate event, correct?

[Movian]

Right, Essentially i want it setup so that ALL text boxes and combo boxes can trigger the after update event. Then the first section of code i setup will trigger for the fields in the table. This way we can add a field name to the list in a run time system and it will automatically be setup with the after update code.

Let me see if i can clarify as it appears we might be getting hung op on understanding what im trying to achieve and what im doing.

1) the form loads and in CODE every text box and combo box is programmaticly set to have an after update event.
2) The system then goes through a table with a list of fields and sets each of those fields to have after update code as given in the first example.

This means that i can add a new text box to the form and without editing any properties or adding any code, if i add that text boxes name to the list it will be able to trigger the after-update event.

This means that i will not have to go through a large number of text boxes setting the after update event procedure property and that any new fields will benefit from this without having to enter a line of code for each field.

does that make sense ?

[CreganTur]

I've never experimented with editing VBA via a VBA procedure. I believe it can be done, but you might run into a number of unintended consequences.

[Movian]

its not Editing VBA,

to get this to work you have to select event procedure from the drop down next to the event you want. There isn't any actual code entered just a property change...

[geekgirlau]

This may be a stupid question, but why is it necessary to check each field as you enter it? Could you not check the record before updating to ensure that all required fields are filled, then after updating the record reset the colours?

By changing the colour to blue you are giving the user a visual cue as to which fields are required. Provided you don't save the record until they are all filled, what value are you adding by checking each individual field?

[Movian]

I need the color to Update in soft real time. Meaning as the data is entered into the form the fields change color to reflect that the required field has been entered.

[geekgirlau]

Why? I understand that you want to ensure that all of the required fields are filled in, but why does this need to be done on the fly for each field?

In programming often the challenges we faced are caused by the way we approach a particular problem. Think about the end result: there may be a very good reason for approaching it in the way you have, but almost without exception there is more than one way to get to the end result you want. I'm just asking you to think about exactly what you are trying to achieve, and whether there might be another way to get there.