Universal GotFocus() event?

[Gregorio]

AC2000 with SP3, Jet engine 4.0.8618.0
Win XP Pro SP2
I'd like to trigger a function each time a user tabs from control to control
on my UNBOUND form. The form is unbound because I use code to modify records
from a number of tables that would be a spaghetti of queries to bind to the
form. What I have instead is a capellini of code, which has worked well for
years now and offers advantages we need.

I seek an event trigger other than writing repetitive GotFocus() and
LostFocus() events for each textbox on my form. I think because the form is
unbound, the Form_Dirty(), AfterUpdate(), BeforeUpdate(), BeforeInsert() and
AfterInsert() events are not called as modifications are made to controls on
the form.

 

[Jeff Boyce]

Gregorio

You haven't indicated what you want the 'trigger' to do.

One possibility would be to open a module and create a procedure that does
what you want. Then, within each of the GotFocus & LostFocus events, call
that function.

You wouldn't need to visit each control sequentially to do this. Design
mode for forms allows you to select all the (relevant) controls and set a
common property/event/value.

Good luck

Jeff Boyce
<Access MVP>

 

[Gregorio]

Hi Jeff

Believe it or not, this architecture is modeled on systems that go back 20+
years in process control.

The event to be triggered is: when a user hits tab or clicks a control, the
system evaluates what control they were in, and saves the value from that
control - if the value has been changed from what was previously stored.
When a control gets the focus, a listbox gets queried to show a range of
previous entries in that control.

At present, I have to code something like this below for evey control. This
is easy but repetitive.

txtBox1_LostFocus()
EvaluateContentsAndSaveIfChangedOrNewEntry (column, table, timestamp)
End Sub

txtBox1_GotFocus()
RequeryListOfPastEntriesIntxtBox1 (column, table, timestamp)
End Sub

So, rather than coding the above, I'd like to set it so the Tab key or
clicking in a control causes an evaluation of which control just got the
focus, and evaluates and queries the appropriate data from the appropriate
table, or saves a user's new entry accordingly.

Everything is coded, except for some form-level event that would trigger
evaluation of which control got or lost the focus and then execute the code
above.

Any thoughts welcome.

- Greg

 

[Jeff Boyce]

Gregorio

I haven't come across something like that, so I don't think I can be of much
more help.

Best of luck

Jeff Boyce
<Access MVP>

 

[John Griffiths]

Grace Hopper had to do some basic admin housekeeping to keep a record of
code and how to call it.

In the room was a Unisys computer to run these programs.

She got the computer to do the work and so invented the compiler.

Lesson if it is boring and repetitive that is just the job for a machine.

The code you are writhing

EvaluateContentsAndSaveIfChangedOrNewEntry (column, table, timestamp)

"column" can come from ControlSource of a bound control so if
we pass the control to the procedure it can be worked out.

EvaluateContentsAndSaveIfChangedOrNewEntry (ControlName, table, timestamp)

Table is part of the form RecordSource, so if we copy the relevant value to
the form.tag property we can get it.

EvaluateContentsAndSaveIfChangedOrNewEntry (ControlName, Me.Tag,

timestamp)

timestamp is easy so we use Now(), which you are probably already using.

By adding a new module to the database window we can write our code.
Sub CreateEventHandlers( frm As Form)

And call it from the immediate debug window "CreateEventHandlers
forms!form1"

So now all we have to do is get access the do the writing part for us.
well first we have to see if the form already has a module using
Me,HasModule if not then create it
Debug.Print frm.Module.Type
' this should print 1 (object module)

If we iterate through the forms controls and only process the ones of
interest.
Sub CreateEventHandlers(frm As Form)
Dim Mdl As Module
Set Mdl = frm.Module
Dim EventProcLine As Long

Dim Ctl As Control
For Each Ctl In frm.Controls
If Ctl.ControlType <> acTextBox Then
' skip
ElseIf Ctl.ControlSource = "" Then
' skip
Else
EventProcLine = frm.Module.CreateEventProc("LostFocus",
Ctl.Name)
Mdl.InsertLines EventProcLine + 1, vbTab & "Call
EvaluateContentsAndSaveIfChangedOrNewEntry(""" & Ctl.Name & """, Me.Tag,
Now())"

End If

Next Ctl

End Sub

Any ideas? John

 

[Marshall Barton]

I don't think the architecture matters here, you're using an
Access form with controls. One of the features of the
form/control event properties (OnGotFocus, OnLostFocus, etc)
allows you to enter a function call instead of
[Event Procedure]
(check Help for "Event Properties").

That means (if your function's arguments can be written in
an acceptable way) you can set the event property to
something like:
=EvaluateContentsAndSaveIfChangedOrNewEntry (fieldname,
"tablename", Now())

Just make sure the function is declared Public.

As Jeff already pointed out, if the function call is the
same for a bunch of controls, you do not even have to type
that stuff one control at a time. You can hold down the
Shift key and select multiple controls, then type (or Paste)
the function call into the property of all the selected
controls.