Listbox Creation 3

[zimmer9]

Suppose you want to provide a user with a list box in which the choices are the current year, up to 10 prior years and up to 10 future years.

In other words, the choices in the list box should be 2015, 2014, 2013, 2012, 2011, 2010, 2009, 2008, 2007, 2006, 2005, 2004, 2003, 2002, 2001, 1999, 1998, 1997, 1996, 1995, 1994

Can you tell me how I could load these years on a rolling basis so that every year I wouldn't have to make a program change to adjust the years 1 year up ?

In other words, next year the values would be in the range 2015 ... 1995.

I suppose I would start with a routine such as

(Year(Date)+9) = 2014
(Year(Date)+8) = 2013
(Year(Date)+7) = 2012
...
(Year(Date)-9) = 1996
(Year(Date)-10) = 1995
(Year(Date)-11) = 1994

How would I stuff these values into a listbox named lstYears ?

Could these values be placed in the value list (in a dynamic fashion: I didn't mean to manually place the values
2014 through 1994 in the value list this year and then next year revise the value list for values of 2015 through 1995.

 

[RoyVidar]

If you're using 2002+ versions, the .AddItem method can be used, else concatinate the string, ensure the .rowsource type is "Value List".

[tt]dim lngYear as long
dim strRowSoruce as string
for lngYear = year(date)-10 to year(date)+10
strRowSource=strRowSource & cstr(lngYear) & ";"
next lngYear
me!lstYears.rowsource=strRowSource[/tt]

- typed not tested

Roy-Vidar

 

[ZmrAbdulla]

works fine except a typo
dim strRowSoruce as string changed to Dim strRowSource As String


Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[zimmer9]

May I pose another Listbox question:
I have a multiselect listbox named lstProperty

Let's say the user can select from among items 1, 2, 3, and 4 in this list box named lstProperty.

Item 1 has the value CASH
Item 2 has the value BONDS
Item 3 has the value IRAS
Item 4 has the value STOCKS

I have parts of SQL statements stored in string variables which I will ultimately concatenate together to create a Dynamic SQL statement once the users makes selection(s) from the list box.

The string variables with assigned values that correlate to the list box values have the names strCash, strBonds, strIRAS and strStocks.

How would I write a loop to get the string variable(s) (that correlated to the selected value(s) from the list box) into a string variable named strProperty:

One other point is that these string variables need to have an OR statement between them:
(either " OR " or a string variable with an assigned value of " OR ").

I could create a string variable as follows:
Dim strOr As String
strOr = " OR "

For example, if the user selects:
Items 1 (CASH) and 4 (STOCKS) from the listbox

Then the string variable strProperty would be created
and contain the value:

strCash & strOr & strStocks (= strCash OR strStocks)

because the user selected items 1 and 4.

If the user only selected Item 4 (STOCKS), then the
string variable strProperty would have the value:

strStocks

there would be no "& strOr" because the user did not select multiple items.

 

[willir]

Perhaps a simpler approach would be to use SELECT ...IN

SELECT YourField FROM YourTable
WHERE YourField IN (ListItem1, ListItem2,..)

Loop thorugh the selected items in the ListBox and build the string.

There are three things to watch for...
- add a comma between each item selected. Usually easiest to add the comma regardless

Dim strSelectedItem As String, strSQL As String, strQ As String
Dim lst As ListBox, varItem As Variant

Set lst = Me.lstBox2
strSelectedItem = ""
strQ = Chr$(34)

If lst.MultiSelect > 0 Then
  If lst.ItemsSelected.Count > 0 Then

    For Each varItem In lst.ItemsSelected
      strSelectedItem = strSelectedItem & strQ & lst.ItemData(varItem) _
      & strQ & ", "
    Next varItem

  End If
End If

If Len(strSelectedItem) Then
    strSelectedItem = Left(strSelectedItem, Len(strSelectedItem) - 2)
    strSQL = "SELECT Critters FROM Crtters Where Critters IN (" & strSelectedItem & ")"
    'And take what ever action you want with the SQL statement
End If

Since your variables are text strings, you have to add quotes before and after, "BOND", "STOCK".

By-the-way...
Normally, a new problem deserves a new post.

Richard