years comboBox

D

dshemesh

Hello,
I have a form in which I want to put a comboBox with all the years between a
certain range. I want the range to change according to another comboBox. For
example, when opening the form, I want the year comboBox to consist of the
all years between 2000 and 2020. But, if the user makes a specific change in
another comboBox in the form, I want the year comboBox to consist of all the
years between 1990 and 2020. Suggestions?

thank you very much
 
J

John Griffiths

Hi

If you set the row source type to "Value List",
you can set the row source to a string with
semi-colons between members.

If this needs to be dynamic ie a different range in 2006
then you can create the string programmatically.

Regards John

Such as
Me.cmbYears.RowSource = GenerateYears(5., 15)

with a function such as ->
Private Function GenerateYears(Start As Long, Finish As Long) As String
Dim Result As String
Result = ""

For x = Year(Date)-Abs(Start) To Year(Date) + Abs(Finish)
If Result <> "" Then
Result = Result & ";"
End If

Result = Result & Format(x, "0000")
Next 'x

GenerateYears = Result

End Function
 
O

Ofer

I would create a text box and let the user enter the year, and on the before
update event of the text box I'll check the range of the data entered

If ComboName = Value and (Me.taxtBox < 2000 or Me.textBox > 2020) then
msgbox "Value must be between ...."
cancel = true
else
If ComboName = Value and (Me.taxtBox < 1990 or Me.textBox > 2020) then
msgbox "Value must be between ...."
cancel = true
end if
end if
=========================================
If you insist on the combo then

1. Change the RowSourceType Property of the year combo to Value List
2. Write in the RowSource Property of the combo 2000;2001;2002....;2020
3. On the after update event of the first combo write the code
If me.ComboName = Value then
me.YearComboName.RowSource = "1990;1991;1992....;2020"
else
me.YearComboName.RowSource = "2000;2001;2002....;2020"
End if
==========================================
Create a table that list the years for each value and the assign the record
set to the combo rowsource.
 
6

'69 Camaro

Hi.
Suggestions?
Click to expand...

In the following example, cboYear is the combo box holding the years,
cboOther is the other combo box, qryYears is the data source with all
possible years for the cboYear combo box, StartYr is the column in the
qryYears query displaying the years, and Column(1) is the combo box's display
column.

In the form's Form_Open( ) event, try:

Me!cboYear.RowSource = "SELECT StartYr " & _
"FROM qryYears " & _
"WHERE (StartYr BETWEEN 2000 AND 2020);"

In the BeforeUpdate( ) event of the other combo box, try:

If (Me!cboOther.Column(1) = "specific change") Then
Me!cboYear.RowSource = "SELECT StartYr " & _
"FROM qryYears " & _
"WHERE (StartYr BETWEEN 1990 AND 2020);"
Else
Me!cboYear.RowSource = "SELECT StartYr " & _
"FROM qryYears " & _
"WHERE (StartYr BETWEEN 2000 AND 2020);"
End If

Me!cboYear.Requery
 
6

'69 Camaro

Hi.
Suggestions?
Click to expand...

In the following example, cboYear is the combo box holding the years,
cboOther is the other combo box, qryYears is the data source with all
possible years for the cboYear combo box, StartYr is the column in the
qryYears query displaying the years, and Column(1) is the combo box's display
column.

In the form's Form_Open( ) event, try:

Me!cboYear.RowSource = "SELECT StartYr " & _
"FROM qryYears " & _
"WHERE (StartYr BETWEEN 2000 AND 2020);"

In the BeforeUpdate( ) event of the other combo box, try:

If (Me!cboOther.Column(1) = "specific change") Then
Me!cboYear.RowSource = "SELECT StartYr " & _
"FROM qryYears " & _
"WHERE (StartYr BETWEEN 1990 AND 2020);"
Else
Me!cboYear.RowSource = "SELECT StartYr " & _
"FROM qryYears " & _
"WHERE (StartYr BETWEEN 2000 AND 2020);"
End If

Me!cboYear.Requery


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
D

dshemesh

great! thanks!
--
dshemesh


Ofer said:
I would create a text box and let the user enter the year, and on the before
update event of the text box I'll check the range of the data entered

If ComboName = Value and (Me.taxtBox < 2000 or Me.textBox > 2020) then
msgbox "Value must be between ...."
cancel = true
else
If ComboName = Value and (Me.taxtBox < 1990 or Me.textBox > 2020) then
msgbox "Value must be between ...."
cancel = true
end if
end if
=========================================
If you insist on the combo then

1. Change the RowSourceType Property of the year combo to Value List
2. Write in the RowSource Property of the combo 2000;2001;2002....;2020
3. On the after update event of the first combo write the code
If me.ComboName = Value then
me.YearComboName.RowSource = "1990;1991;1992....;2020"
else
me.YearComboName.RowSource = "2000;2001;2002....;2020"
End if
==========================================
Create a table that list the years for each value and the assign the record
set to the combo rowsource.
Click to expand...
 
Top