Event for Screen.Activeform code

[Fred Boer]

Hello!

I have code which is meant to pre-load the comboboxes on my forms. The code
appears below. This has been in my application for a long time, but I just
realized now that it isn't working. I know the code will work, because I
have called the function in the immediate window with the form loaded, and
it had the desired effect on the combobox. What I *can't* seem to to is to
find an event that will work automatically.

I have tried Open, Load, Activate, and OnGotFocus, to no avail. OnCurrent
works, but not when the form initially opens, only when you navigate to a
new record. (Also, it would be silly to run this code in the current
event...).

My clumsy attempts at debugging seem to indicate that the when the code
actually fires, the active form is the calling switchboard form, not the
newly opened form...

Help!

Thanks!
Fred Boer

Function is in standard module:

Public Function fncLoadcbo()
On Error GoTo Err_fncLoadcbo
Dim i As Integer
' Cycle through the form's controls,
' testing for combo boxes,
' and pre-load each combobox.

For i = 0 To Screen.ActiveForm.Count - 1
If TypeOf Screen.ActiveForm(i) Is ComboBox Then
Dim lngDummy As Long
lngDummy = Screen.ActiveForm(i).ListCount 'Force combobox to
load all records
End If
Next
Exit_fncLoadcbo:
Exit Function
Err_fncLoadcbo:
fncWRMSErrMsg err.Number, err.Description
Resume Exit_fncLoadcbo
End Function

 

[Dirk Goldgar]

Hello!

I have code which is meant to pre-load the comboboxes on my forms.
The code appears below. This has been in my application for a long
time, but I just realized now that it isn't working. I know the code
will work, because I have called the function in the immediate window
with the form loaded, and it had the desired effect on the combobox.
What I *can't* seem to to is to find an event that will work
automatically.

I have tried Open, Load, Activate, and OnGotFocus, to no avail.
OnCurrent works, but not when the form initially opens, only when you
navigate to a new record. (Also, it would be silly to run this code
in the current event...).

My clumsy attempts at debugging seem to indicate that the when the
code actually fires, the active form is the calling switchboard form,
not the newly opened form...

Help!

Thanks!
Fred Boer

Function is in standard module:

Public Function fncLoadcbo()
On Error GoTo Err_fncLoadcbo
Dim i As Integer
' Cycle through the form's controls,
' testing for combo boxes,
' and pre-load each combobox.

For i = 0 To Screen.ActiveForm.Count - 1
If TypeOf Screen.ActiveForm(i) Is ComboBox Then
Dim lngDummy As Long
lngDummy = Screen.ActiveForm(i).ListCount 'Force combobox
to load all records
End If
Next
Exit_fncLoadcbo:
Exit Function
Err_fncLoadcbo:
fncWRMSErrMsg err.Number, err.Description
Resume Exit_fncLoadcbo
End Function

Fred -

There's no good event to use for the function as written -- you'll have
to modify it, I think. The problem is that the Screen.ActiveForm
property isn't updated until *after* the form's Activate event, when the
form actually gets the focus. But you can't use the form's GotFocus
event because that event isn't fired unless there is no control on the
form that can receive the focus.

However, you can pass to the function a reference to the form object to
be handled. That will let you call the function in the form's Open
event. Try this version of your function:

'----- start of revised code -----
Public Function fncLoadcbo(frm As Access.Form)
On Error GoTo Err_fncLoadcbo

Dim ctl As Access.Control
Dim lngDummy As Long

' Cycle through the form's controls,
' testing for combo boxes,
' and pre-load each combobox.
For Each ctl In frm.Controls
If TypeOf ctl Is ComboBox Then
'Force combobox to load all records
lngDummy = ctl.ListCount
End If
Next
Exit_fncLoadcbo:
Exit Function
Err_fncLoadcbo:
fncWRMSErrMsg Err.Number, Err.Description
Resume Exit_fncLoadcbo
End Function

'----- end of revised code -----

Then you can set each form's OnOpen property to

=fncLoadcbo([Form])

Note -- that is actually the literal "[Form]", not intended to be
replaced by the form name or anything like that.

Note also: I used "For Each ctl" in the revised function, instead of
looping and indexing with a counter, only because I knew that I wanted a
reference to the control to use more than once inside the loop.

 

[Fred Boer]

Dear Dirk:

Thanks so much! I didn't know that about Screen.ActiveForm; I learn
something every day! <g> I won't be able to work on this until tomorrow, but
I'm sure it will do the trick!

Funny, I remember thinking ealier in my Access days "What the heck do they
need so many events for just to open a form??!?" Now I think.. hmm..
wouldn't it be nice to have another event...

Fred

 

[Fred Boer]

Dear Dirk:

It works perfectly, and I even think I understand it! <g>

Cheers!
Fred

Hello!

I have code which is meant to pre-load the comboboxes on my forms.
The code appears below. This has been in my application for a long
time, but I just realized now that it isn't working. I know the code
will work, because I have called the function in the immediate window
with the form loaded, and it had the desired effect on the combobox.
What I *can't* seem to to is to find an event that will work
automatically.

I have tried Open, Load, Activate, and OnGotFocus, to no avail.
OnCurrent works, but not when the form initially opens, only when you
navigate to a new record. (Also, it would be silly to run this code
in the current event...).

My clumsy attempts at debugging seem to indicate that the when the
code actually fires, the active form is the calling switchboard form,
not the newly opened form...

Help!

Thanks!
Fred Boer

Function is in standard module:

Public Function fncLoadcbo()
On Error GoTo Err_fncLoadcbo
Dim i As Integer
' Cycle through the form's controls,
' testing for combo boxes,
' and pre-load each combobox.

For i = 0 To Screen.ActiveForm.Count - 1
If TypeOf Screen.ActiveForm(i) Is ComboBox Then
Dim lngDummy As Long
lngDummy = Screen.ActiveForm(i).ListCount 'Force combobox
to load all records
End If
Next
Exit_fncLoadcbo:
Exit Function
Err_fncLoadcbo:
fncWRMSErrMsg err.Number, err.Description
Resume Exit_fncLoadcbo
End Function

Fred -

There's no good event to use for the function as written -- you'll have
to modify it, I think. The problem is that the Screen.ActiveForm
property isn't updated until *after* the form's Activate event, when the
form actually gets the focus. But you can't use the form's GotFocus
event because that event isn't fired unless there is no control on the
form that can receive the focus.

However, you can pass to the function a reference to the form object to
be handled. That will let you call the function in the form's Open
event. Try this version of your function:

'----- start of revised code -----
Public Function fncLoadcbo(frm As Access.Form)
On Error GoTo Err_fncLoadcbo

Dim ctl As Access.Control
Dim lngDummy As Long

' Cycle through the form's controls,
' testing for combo boxes,
' and pre-load each combobox.
For Each ctl In frm.Controls
If TypeOf ctl Is ComboBox Then
'Force combobox to load all records
lngDummy = ctl.ListCount
End If
Next
Exit_fncLoadcbo:
Exit Function
Err_fncLoadcbo:
fncWRMSErrMsg Err.Number, Err.Description
Resume Exit_fncLoadcbo
End Function

'----- end of revised code -----

Then you can set each form's OnOpen property to

=fncLoadcbo([Form])

Note -- that is actually the literal "[Form]", not intended to be
replaced by the form name or anything like that.

Note also: I used "For Each ctl" in the revised function, instead of
looping and indexing with a counter, only because I knew that I wanted a
reference to the control to use more than once inside the loop.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)