Cannot change focus to a list box after running loop

[leicklda]

Hi everyone, I'd be grateful for any assistance...

I have 9 listboxes ("list1", "list2", "list3", etc..) that are lined up on a form. When a user clicks on one of them, I need all the other listboxes to have the same index value be selected as well. This works well with a for...next loop, and is programmed in a procedure called "highlight". When I click on any of these listboxes, a different lisbox "Dest" is suppsed to update based on the value I picked. This also works fine. The problem comes when I try to click on the new values in the "Dest" listbox. I have to click twice to get it to make a selection. I've tried different things, including Me.Dest.setfocus - but this just gives an error that Access Cannot Change Focus.

Here is code that I've greatly condensed to try and diagnose the problem (unsuccessfully):

Option Compare Database
Dim sqltest As String
Dim i As Integer
-----------------------------------
Private Sub List1_Click()
lstnum = 1
i = Me.List1.ListIndex
Highlight
End Sub
---------------------------------
Private Sub List2_Click()
lstnum = 2
i = Me.List2.ListIndex
Highlight
End Sub
--------------------------------------
Private Sub List3_Click()
lstnum = 3
i = Me.List3.ListIndex
Highlight
End Sub
--------------------------------------------
Private Sub Highlight()
Dim lstctrl As Control
Dim cnt As Integer

For cnt = 1 To 3
Set lstctrl = Me("list" & Format(cnt, "0"))
lstctrl.Selected(i) = True
Next

sqltest = "SELECT [ZIP Territory].ID FROM [ZIP Territory]"

Me.dest.RowSource = sqltest
Me.dest.Requery
End Sub
-------------------------------------------

Does anyone know what I have to do in order to be able to click on the Dest listbox only once in order to get it to make a selection. Normally when there are multiple listboxes on a form, one can jump back and forth between them with no "coughs" of extra clicks such as this. This jumping still works with the list1, list2, etc... boxes, just not the Dest box (in which I've changed the rowsource)

 

[PHV]

Have you tried to add
Me.dest.SetFocus
at the end of the Highlight Sub ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[leicklda]

Hi PH,

I've tried that in many places, and it generates various errors. Access won't let me change focus with that command after running the loop.

 

[TheAceMan1]

Hay PHV . . . . .

When you update the RecordSource for any ListBox, you have to Requery to view the changes.

Me!ListBoxName.Requery.

See Ya! . . . . . .

 

[leicklda]

Hi Aceman1

I have a requery right at the end of the highlight procedure. I can see the new records just fine, I just cannot click on them on the first try.

 

[TheAceMan1]

leicklda . . .

Are the listboxes on a subform? (This would provide an explanation).

See Ya! . . . . . .

 

[leicklda]

TheAceMan1,
No, they are not on a subform. I even tried making a dummy form from scratch, putting generic listboxes on them with very simple data, and got the same problem after running my highlight procedure.

 

[TheAceMan1]

OK leicklda . . .

First, there is some incorrect syntax.

[blue]The Bang ([red]![/red]) Operator signifies what follows is a control. The Dot ([red].[/red]) Operator signifies what follows is a property or method.[/blue]

Alot of people do this and I don't know why. Anyway I need correct syntax before I can continue.

[blue]If the operators are not used correctly, it may not be picked up at compile time. The only way to tell is the [purple]Compile ToolBar Button[/purple] or the [purple]Compile Menu Entry[/purple]. They will grayout to an disabled look when compile detects no errors. They stay active if errors are present.[/blue]

Note there are many times I've had to hunt for errors because there simply [purple]not flaged/hilited[/purple]. So in your code, change to all the red as follow:

[blue]Option Compare Database
Dim sqltest As String
Dim i As Integer
-----------------------------------
Private Sub List1_Click()
    lstnum = 1
    i = Me[red][b]![/b][/red]List1.ListIndex
    Highlight
End Sub
---------------------------------
Private Sub List2_Click()
    lstnum = 2
    i = Me[red][b]![/b][/red]List2.ListIndex
    Highlight
End Sub
--------------------------------------
Private Sub List3_Click()
    lstnum = 3
    i = Me[red][b]![/b][/red]List3.ListIndex
    Highlight
End Sub
--------------------------------------------
Private Sub Highlight()
Dim lstctrl As Control
Dim cnt As Integer

For cnt = 1 To 3
    Set lstctrl = Me("list" & Format(cnt, "0"))
        lstctrl.Selected(i) = True
Next

sqltest = "SELECT [ZIP Territory].ID FROM [ZIP Territory]"

Me[red][b]![/b][/red]dest.RowSource = sqltest
Me[red][b]![/b][/red]dest.Requery
End Sub[/blue]

I would do the code differently, but since its working we'll leave well enough alone. Also I'm confused as to why you don't pass the variable [blue]i[/blue] to your [blue]Hilight[/blue] routine?

Sorry to be so picky here, but I wouldn't expect it to compile the way it was. It may not fix the problem, but at least it will compile correctly. Make the changes throughout your code.

Let me know . . . . .

See Ya! . . . . . .

 

[lastout]

All,

A good article on the subject of bang vs. dot:

http://www.advisor.com/Articles.nsf/aid/BAROA06

Cheers

 

[TheAceMan1]

Hay lastout . . . how are ya . . .

If ya read my previous post about compile indication, I'm tellin ya, a pure dot format doesn't always compile. In the past Ive gotton into trouble because, as I said no errors were flaged/hilited, and find them was a B.I.
For me, its not a matter of old habit. It's a matter of what works and what I can depend on. The bigger the project, the more you need everything that does work . . . too work.

Either way the choice is yours . . . . I'd just keep a good eye on that compile indicator . . .

See Ya! . . . . . .

 

[leicklda]

TheAceMan1 & lastout

Thanks for the info on the bang. I revised my code to have the ! instead of . where pointing to a control. I also simplified my example code even more to eliminate anything extraneous to describing this problem. (like the variables you were asking about why I didn't pass it into my highlight procedure) ... I now have a dummy form with 3 listboxes: list1, list2, dest, and the following code - the problem still exists - I have to click on dest twice to get it to make a selection. My real form actually has 9 listboxes, but I condensed this just to try and diagnose the problem

----------------------------
Option Compare Database
Dim sqltest As String
Dim i As Integer
----------------------------
Private Sub List1_Click()
i = Me!List1.ListIndex
Highlight
End Sub
-----------------------------
Private Sub List2_Click()
i = Me!List2.ListIndex
Highlight
End Sub
------------------------------
Private Sub Highlight()
Dim lstctrl As Control
Dim cnt As Integer

For cnt = 1 To 2
Set lstctrl = Me("list" & Format(cnt, "0"))
lstctrl.Selected(i) = True
Next

sqltest = "SELECT [ZIP Territory].ID FROM [ZIP Territory]"

Me!dest.RowSource = sqltest
'^^^^^^^^^^^^^^^^^^^^^^^^^^^if I rem this out, the problem doesn't occur, but I need this.
'Me!dest.SetFocus
'^^^^^^^^^^^^^^^^^This seems like the obvious solution, but if it is not rem'd out, then I get the error 2110 - Microsoft Access can't move the focus to the control dest."
Me!dest.Requery
End Sub
-------------------------------

 

[leicklda]

I finally got it -- I have to set the dest listbox to null:
me!dest = null

in order to requery it and set focus to it. Don't really know why, but it works!