Control1 GotFocus and Control5 RowSource

[dbqph]

In Access 2007, I am using the GotFocus event on Control1 to set the
RowSource of Control5 (Control5 is a ListBox control that displays the
contents of the Control1.RowSource). As focus moves from Control1, Control2,
Control3, etc, I want Control5.RowSource to list the items for the active
control.

In Access 2000, the behavior works as I desire. In Access 2007, it appears
that focus switches to Control5 while the RowSource is set/updated and then
returned to the previous control causing a loop effect with the GotFocus
event.

Any ideas?

 

[Dirk Goldgar]

In Access 2007, I am using the GotFocus event on Control1 to set the
RowSource of Control5 (Control5 is a ListBox control that displays the
contents of the Control1.RowSource). As focus moves from Control1,
Control2,
Control3, etc, I want Control5.RowSource to list the items for the active
control.

In Access 2000, the behavior works as I desire. In Access 2007, it appears
that focus switches to Control5 while the RowSource is set/updated and
then
returned to the previous control causing a loop effect with the GotFocus
event.

Any ideas?

I don't see this behavior in a simple test. Please post your code so that
we can see if there is some other contributing factor.

 

[dbqph]

Here are the basics:

On each control whose list I want to display, I have:

Dim ctlCurrentControl As Control

Set ctlCurrentControl = Screen.ActiveControl
GetFormControl (ctlCurrentControl.Name)

In a shared Module, I have:

Sub GetFormControl(strCtrlName As String)
Dim strSQLRecord As String

If strCtrlName = "CategoryID" Or strCtrlName = "PaidWithID" Or strCtrlName =
"GroupID" Then
' These controls should have their list displayed in my Options Control
' Do Something

If strCtrlName = "CategoryID" Then
strSQLRecord = "SELECT Category FROM tblExpCategories WHERE
CategoryActive = True ORDER BY Category"
ElseIf strCtrlName = "PaidWithID" Then
strSQLRecord = "SELECT Method FROM tblExpPayMethods WHERE
MethodActive=True ORDER BY Method"
Else
strSQLRecord = "SELECT ExpGroup FROM tblExpGroups WHERE
GroupActive=True ORDER BY ExpGroup"
End If

' The following line seems to cause a loop problem in Access 2007
Forms![frmExpSummary]![Options].RowSource = strSQLRecord

ElseIf strCtrlName = "Purpose" Then
' Nothing really to show but additional info is displayed via a Text
control
' Do Something
Else
' No need to show anything
' Do something else
End If

End Sub

Thank you for any insights or suggestions!

dbqph

 

[Dirk Goldgar]

Here are the basics:

On each control whose list I want to display, I have:

Dim ctlCurrentControl As Control

Set ctlCurrentControl = Screen.ActiveControl
GetFormControl (ctlCurrentControl.Name)

In a shared Module, I have:

Sub GetFormControl(strCtrlName As String)
Dim strSQLRecord As String

If strCtrlName = "CategoryID" Or strCtrlName = "PaidWithID" Or strCtrlName
=
"GroupID" Then
' These controls should have their list displayed in my Options Control
' Do Something

If strCtrlName = "CategoryID" Then
strSQLRecord = "SELECT Category FROM tblExpCategories WHERE
CategoryActive = True ORDER BY Category"
ElseIf strCtrlName = "PaidWithID" Then
strSQLRecord = "SELECT Method FROM tblExpPayMethods WHERE
MethodActive=True ORDER BY Method"
Else
strSQLRecord = "SELECT ExpGroup FROM tblExpGroups WHERE
GroupActive=True ORDER BY ExpGroup"
End If

' The following line seems to cause a loop problem in Access 2007
Forms![frmExpSummary]![Options].RowSource = strSQLRecord

ElseIf strCtrlName = "Purpose" Then
' Nothing really to show but additional info is displayed via a Text
control
' Do Something
Else
' No need to show anything
' Do something else
End If

End Sub

Thank you for any insights or suggestions!

Huh. I don't see anything there that would explain it. Do the occasional
comments:

' Do Something

.... stand in for code that you have left out of your post, or are they
really comments in the code?

When you say, "On each control whose list I want to display, I have", is
that in the control's GotFocus event?

 

[Dirk Goldgar]

(reposting, as my original reply doesn't seem to have appeared)

Here are the basics:

On each control whose list I want to display, I have:

Dim ctlCurrentControl As Control

Set ctlCurrentControl = Screen.ActiveControl
GetFormControl (ctlCurrentControl.Name)

In a shared Module, I have:

Sub GetFormControl(strCtrlName As String)
Dim strSQLRecord As String

If strCtrlName = "CategoryID" Or strCtrlName = "PaidWithID" Or strCtrlName
=
"GroupID" Then
' These controls should have their list displayed in my Options Control
' Do Something

If strCtrlName = "CategoryID" Then
strSQLRecord = "SELECT Category FROM tblExpCategories WHERE
CategoryActive = True ORDER BY Category"
ElseIf strCtrlName = "PaidWithID" Then
strSQLRecord = "SELECT Method FROM tblExpPayMethods WHERE
MethodActive=True ORDER BY Method"
Else
strSQLRecord = "SELECT ExpGroup FROM tblExpGroups WHERE
GroupActive=True ORDER BY ExpGroup"
End If

' The following line seems to cause a loop problem in Access 2007
Forms![frmExpSummary]![Options].RowSource = strSQLRecord

ElseIf strCtrlName = "Purpose" Then
' Nothing really to show but additional info is displayed via a Text
control
' Do Something
Else
' No need to show anything
' Do something else
End If

End Sub

Thank you for any insights or suggestions!

Huh. I don't see anything there that would explain it. Do the occasional
comments:

' Do Something

.... stand in for code that you have left out of your post, or are they
really comments in the code?

When you say, "On each control whose list I want to display, I have", is
that in the control's GotFocus event?

 

[Dirk Goldgar]

I don't see why it is behaving like that. I would be willing to look at a
copy of your database to see if I can figure it out. If you'd like to send
me a cut-down copy of your database, containing only the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB in
size (preferably much smaller) -- I'll have a look at it, time permitting.
You can send it to the address derived by removing NO SPAM and ".invalid"
from the reply address of this message. If that address isn't visible to
you, you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Hi Dirk,

The comments were inserted into the post for brevity. In their place I'm
toggling the visibility of various controls based on what I want to see. As
an example, one scenario is:

Forms![frmExpSummary]![Options].Visible = True
Forms![frmExpSummary]![LabelOptions].Visible = True
Forms![frmExpSummary]![LabelPurpose].Visible = False

The entire function as it stands right now is as follows:

Sub GetFormControl(strCtrlName As String)
Dim strSQLRecord As String

If strCtrlName = "CategoryID" Or strCtrlName = "PaidWithID" Or strCtrlName =
"GroupID" Then
' Focus in a control whose list should be expanded, show label and
options
Forms![frmExpSummary]![Options].Visible = True
Forms![frmExpSummary]![LabelOptions].Visible = True
Forms![frmExpSummary]![LabelPurpose].Visible = False

If strCtrlName = "CategoryID" Then
strSQLRecord = "SELECT Category FROM tblExpCategories WHERE
CategoryActive = True ORDER BY Category"
ElseIf strCtrlName = "PaidWithID" Then
strSQLRecord = "SELECT Method FROM tblExpPayMethods WHERE
MethodActive=True ORDER BY Method"
Else
strSQLRecord = "SELECT ExpGroup FROM tblExpGroups WHERE
GroupActive=True ORDER BY ExpGroup"
End If

' Debug.Print "Options recordsource = " &
Forms![frmExpSummary]![Options].RowSource

' Forms![frmExpSummary]![Options].RowSource = strSQLRecord
ElseIf strCtrlName = "Purpose" Then
' No RowSource but show a hint about the field
Forms![frmExpSummary]![Options].Visible = False
Forms![frmExpSummary]![LabelOptions].Visible = True
Forms![frmExpSummary]![LabelPurpose].Visible = True
Else
' Nothing special, hide all hints
Forms![frmExpSummary]![Options].Visible = False
Forms![frmExpSummary]![LabelOptions].Visible = False
Forms![frmExpSummary]![LabelPurpose].Visible = False
End If

End Sub

Thanks for your persistence. I gave up trying to make it work and removed
the Or strCtrlName = "PaidWithID" Or strCtrlName = "GroupID" from the first
If statement. However, if you can give me a clue about what might be
happening, I'd love to re-incorporate those additional checks.