Listbox Creation 3

zimmer9 · Feb 12, 2005

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 · Feb 12, 2005

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 · Feb 12, 2005

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 · Feb 12, 2005

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 · Feb 12, 2005

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

Code:

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Listbox Creation 3

zimmer9

Programmer

RoyVidar

Instructor

ZmrAbdulla

Technical User

zimmer9

Programmer

willir

MIS

Similar threads

Part and Inventory Search

Sponsor