Access 2007 Listbox quirk 1

[batteam]

With Access 2007 - using .adp projects, I have noticed that when I requery my listboxes, the vertical scroll back goes to the bottom of the list of records, so the user ends up looking at the bottom set of records instead of the top. Any ideas on how to fix this quirk without doing lots of fancy vb programming to force selection of the top record, then de-select it so the user won't notice, or something like that?

Thanks for any help you can offer.

 

[TheAceMan1]

How are ya batteam . . .

How do you requery?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[batteam]

I assigned a recordsource and requery thru vb code behind the form. My listbox is named 'lstOpenCases' and I requery it like this:

lstOpenCases.RowSource = "dbo.CVC1_frmOpenCasesClientNameSumm_NewFinal_2" lstOpenCases.Requery

The recordsource is a sp in SQL 2005. When the listbox fills up, the vertical scroll bar ends up at the bottom of the listbox, thus showing the bottom set of records.
Works normal with Access 2000. Also, its an Access Project (.adp). Ever see this before?

 

[TheAceMan1]

batteam . . .

Any time you write to the [blue]rowsource[/blue] of a listbox, it automatically requeries! Also as an direct requery Me.[blue]ListboxName[/blue].Requery is known not to work in most versions, the substitute of which is: Me.[blue]ListboxName[/blue].RowSource = Me.[blue]ListboxName[/blue].RowSource.

So try removing [blue]lstOpenCases.Requery[/blue].

BTW ... are you using [blue]VB[/blue] or [blue]VBA[/blue]?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[batteam]

I found something that was causing the scroll bar to hit the bottom. After I requeried the listbox, I perform the following code to clear any selections:

With lstOpenCases
For intI = 0 To .ListCount - 1
.Selected(intI) = False
Next intI
End With

Once I did not perform this clear routine, the listbox did not scroll to the bottom. Maybe its redundant - anyway, doesn't look like I can do this anymore in '07.

Interesting about your requery advice. I've never had any trouble with my versions but I will keep it in mind. Since I have you on line, would you know why Access 2007 gives a general date - e.g. 1/1/1900 10:00:00 AM - for a control when its formatted for "Medium Time" and a person enters a valid medium time value in it? Weird. Thanks for all your help.

 

[PHV]

What about this ?

With lstOpenCases
   For intI = .ListCount - 1 To 0 Step -1
     .Selected(intI) = False
   Next intI
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[batteam]

PHV

That snippet of code worked. Thanks lots.

 

[KingLearJet]

I have noticed the same thing. But I don't want to unselect all the items that are selected. I just want to sort them. So I have an SQL statement:

strSQL = "SELECT ContactEmail, LastName & ', ' & FirstName AS ContactName, ... etc.

and then an option button to choose how to sort it, which I use this in the AfterUpdate:

If Me.optSort = 1 Then
Me.lstContacts.RowSource = strSQL & "ORDER BY LastName, FirstName;"
Me.lstContacts.Requery
Else
Me.lstContacts.RowSource = strSQL & "ORDER BY ZipCode;"
Me.lstContacts.Requery
End If

In 2007, it ends up scrolling to the bottom, but in 2003 it stays at the top. Is this a bug in 2007?

 

[Domino2]

Something PHV helped me with sometime ago. After the line requerying the list, add the line

Me.lstContacts.rowsource = Me.lstContacts.rowsource

Hope it works for you

Regards