List Box Question

[Waynest]

Hi

I have a list box which is populated with fiscal year names from a view. When the form opens I want to select the appropriate entry in the listbox according to the current date. I run into problems when trying to read through the entries in the listbox, the following always gives me a lstyears.value of NULL so the loop always stops on the last listbox entry

For i = 1 To lstYears.ListCount
Me.lstYears.Selected(i) = True
If InStr(Me.lstYears.value, SearchYr) Then
GoTo FoundYr
End If
Next i

FoundYr:

e.g. There would be a line in the list box of 'Fiscal Year 2001' and I have already determined that SearchYr contains '2001' by matching today's date against my company's fiscal calendar table.

Whats up with it, Its really starting to wind me up???

 

[huzza]

Try using lstYears.text not lstYears.value and see if this makes any difference
Richard

 

[raskew]

Try this as the rowsource for your list box:

Function FYLoop() As String
'*******************************************
'Name:      FYLoop (Function)
'Purpose:   Creates list of FYs to be used as a
'           combo-box's value list
'Author:    raskew
'To test    In debug window: ? FYLoop
'To use:    In the form's OnOpen Event include
'           Me![cboFYr].RowSource = FYLoop
'           replacing cboFYr with the name of your control.
'*******************************************
Dim datehold As Variant, strSQL As String
Dim n As Integer, i As Integer

n = 9
strSQL = ""
datehold = year(DateValue(Date)) + IIf(Month(Date) >= 10, 2, 1)
For i = 0 To n
datehold = datehold - 1
'modify the next line to format the output
strSQL = strSQL & "FY" & datehold & "; "
Next i

FYLoop = strSQL
'Debug.Print FYLoop
End Function
[code]

 

[Waynest]

Thanks guys, but I'm no better off.

The .text property isnt available in access, I use it in VB but you cant use it here it seems.

Raskew, I populate the listbox ok, but I cant make the selection bar move to the correct row. I dont think your code is a cure for that prob. e.g. the form opens and there are 10 FYs available in the box, 1995 thru 2004. I want the 2001 row to be automatically selected so the form shows current FY info by default. Any more ideas?

 

[huzza]

Hi Waynest,
I think I've just worked out what the problem is. If you're using a multi-select list box and not a combo box then the .value propperty is meaning less.
What you want to use instead is
If InStr(Me.lstYears.list(i,column), SearchYr) Then
GoTo FoundYr

replacing column with your bound column. Also I think you don't want the
Me.lstYears.Selected(i) = True
line if you have a multi-select list box, otherwise you are selecting every item up to and including the one you're trying to find.
If it isn't a multi-select list box then the only thing I can suggest is it could be due to not deselecting each row after checking the condition before selecting another,
Richard

 

[raskew]

Suggestions, yes--dump your code. It doesn't work in this context. You are dealing with VBA, not VB.

What I provided, placed in an Access module, will result in this value list (date is 27-Nov-00):

FY2001; FY2000; FY1999; FY1998; FY1997; FY1996; FY1995; FY1994; FY1993; FY1992;

If the current date were anytime in 2000 prior to 01-Oct-00, it would result in this:

FY2000; FY1999; FY1998; FY1997; FY1996; FY1995; FY1994; FY1993; FY1992; FY1991;

Given that, figure out how to (in VBA) open the form pointed to the particular FY selected in the list box. Hint: It doesn't take a loop.

 

[Waynest]

Aaaaaaggggggggghhhhhhhh!

Its not a multi select list box

I understand and use list boxes populated by value lists. I can determine the current FY period and which figures to display when the form opens. The form updates correctly as different rows in the list box are selected. What I can't do is make the black highlight bar in the list box appear over the current FY when the form opens. That is my problem. How do I make the gosh darn black bar appear where I want it to in the list box before anything in the list has been selected?

(There are 4 list boxes on the form with fiscal years, months, weeks etc and when the form opens I want the black bar to be positioned correctly in each so it is clear what information is being shown)

 

[MarkSweetland]

Try this:

  For i = 0 To lstYears.ListCount - 1
  ' For i = 1 To lstYears.ListCount for Option Base 1
    If InStr(Me.lstYears.ItemData(i), SearchYr) Then
      Me.lstYears.Selected(i) = True
      Exit For
    End If
  Next i

Mark

 

[Waynest]

Mark, thanks, I already realised I was counting 1 too high.

Now here's a strange thing. While the list boxes are in the detail section of the form the .selected(i) doesnt produce a higlight bar although it does scroll the list to the right point. If I move the list boxes into the header or footer sections of the form then the code I had in the 1st place works ok and I get the highlight bar.

I can live with this now, but why, why, WHY???

 

[raskew]

While it's kind of entertaining watching you demonstrate that you flunked "Frustration-Management 101", I'm concerned that you may stroke-out before you seize on the solution.

As I read and reread your code, it appears that you are trying to use a list-box as some sort of a container. And, given that, you're trying to loop through that container. There's no evidence that you ever intend to select a particular record.

Pilgrim, it doesn't work that way. A list box can have only one value at any given time. If we want to loop through a set of values, we use a recordset (table or query). Eh, sound familiar?

Once again, go back and jettison your current code. It's never going to work. Forget about list boxes and think in terms of recordsets.

 

[Waynest]

Raskew, I hacked down the code snippet to try & get my problem with positioning the highlight bar across and I think that was misleading, there's an underlying recordset in there already.

Anyway, I've made it work how I intended. The solution was as simple as moving some of the controls out of the detail section of the form.