Select current year from a dropdown (2012, 2011, 2010, 2009)

[Sydcomebak]

I would like to make a dropdown that populates with the current year and the 3 previous. In other words, the contents would change from year to year.

I also need to dropdown to pass the value as a number.

combo37.value=2012

I've tried everything I could think of and cut and pasted several close solutions from Google searches, no love.

Thanks, gurus!

-Dave

 

[Mr. B]

Welcome to the forum. You did not indicate how much you know about VBA but you will need to use VBA code to have your combo box dynamically populate with values like you have described.

Try this:

First, set the "Row Source Type" property of your combo box to "Value List".

Next, place the following VBA code in the "On Load" event of your form:

'declare a string variable to hold the list of values for populating the combo box list
Dim strValueList As String
'dim a variang type variable to hold the current year 
'and the "cntr" variant type varialble to use as a counter
Dim varCurYr, cntr
'assign the value of the current year to the "varCurYr" variable
varCurYr = Year(Date)
'add the value of the current year to the variable that will hold the values for the list
strValueList = varCurYr
'process the following code three times to add more values to the list
For cntr = 1 To 3
    'concatenate the previous year to the string variable each time the code is run
    strValueList = strValueList & "; " & Year(Date) - cntr
Next cntr

With Me.Combo37
     'actually place the list of value in the combo box on the form
    .RowSource = strValueList
     'select the current year in the combo box
    .Value = varCurYr
End With

When the form opens the combo box list will be populated with 2012, 2011, 2010, 2009, and the value of the current year will be selected.

 

[plog]

There's no simple way to do this, only horribly hacky ways. Here's 2 of those methods:

1. Set the Row Source of your drop down to a query based on one of your tables that has (and will have) data for the 4 years you want your drop down to cover. This can either be a date field or a numeric field. Either bring in the Year field or create one using the Year() function and set the criteria underneath it to <=Year(Date()) AND >=Year(Date())-3. Be sure to make this an aggregate query and GROUP By the Year.

2. Paste the below code into the Row Source of your dropdown and change 'YourTableNameHere' to any of your tables (the one with the least data is recommended):

SELECT Year(Date()) AS Y FROM YourTableNameHere GROUP BY Year(Date()) UNION ALL SELECT Year(Date())-1 AS Y FROM YourTableNameHere GROUP BY Year(Date()) UNION ALL SELECT Year(Date())-2 AS Y FROM YourTableNameHere GROUP BY Year(Date()) UNION ALL SELECT Year(Date())-3 AS Y FROM YourTableNameHere GROUP BY Year(Date());

 

[Sydcomebak]

Mr. B.

Perfect. Simply perfect.

Modified to this:

Private Sub Form_Load()
'declare a string variable to hold the list of values for populating the combo box list
Dim strValueList As String
'dim a variang type variable to hold the current year
'and the "cntr" variant type varialble to use as a counter
Dim varNxtYr, cntr
'assign the value of the current year to the "varCurYr" variable
varNxtYr = Year(Date) + 1
'add the value of the current year to the variable that will hold the values for the list
strValueList = varNxtYr
'process the following code three times to add more values to the list
For cntr = 1 To 4
    'concatenate the previous year to the string variable each time the code is run
    strValueList = strValueList & "; " & Year(Date) + 1 - cntr
Next cntr

With Me.Combo37
     'actually place the list of value in the combo box on the form
    .RowSource = strValueList
     'select the current year in the combo box
    .Value = varNxtYr - 1
End With

To incorporate the next year as well.

Alright. That's variable #1. Variable #2 is a dropdown where you select "A, B, C, D, or E" {Combo41}

If I wanted to pull and edit Lease_Rates.Rate in Table "Lease_Rates" where:
Lease_Rates.Lease_Year = combo37.value
-=AND=-
Lease_Rates.Class = combo41.value

How would I set that up? I'll probably have a bound text box that filters based on values from the dropdowns, but would anything I enter into that box store in the DB?

EDIT:
I can create an unbound TextBox that looks up and displays the value, but I can't edit and store it. (Obviously)
=DLookUp("[Rate]","Lease_Rates","[Lease_Year]=" & Combo37.Value & " AND [Class]='" & Combo41.Value & "'")

Thanks again, Mr. B. you got me down the right path.

plog, I'm looking at your code now... I like it so far... Amazing that there are so many ways to do this and I couldn't get one to work. >-(

-Dave

 

[missinglinq]

...I can create an unbound TextBox that looks up and displays the value, but I can't edit and store it. (Obviously)
=DLookUp("[Rate]","Lease_Rates","[Lease_Year]=" & Combo37.Value & " AND [Class]='" & Combo41.Value & "'")

As you've found out, you cannot store a Value that already has a Control Source that is Calculated, i.e. a Control Source that begins with the = (equal sign). SO you need to move the assignment of Value to somewhere else, so you can use the Control Source to bind the Value to the underlying Table.

In this case you need to do it in the AfterUpdate events of both Comboboxes.

Private Sub Combo37_AfterUpdate()
 If Not IsNull(Me.combob37) And Not IsNull(Me.Combo41) Then
  Me.TargetTextbox = DLookup("[Rate]", "Lease_Rates", "[Lease_Year]=" & Me.Combo37.Value & " AND [Class]='" & Me.Combo41.Value & "'")
 End If
End Sub

Private Sub Combo41_AfterUpdate()
 If Not IsNull(Me.combob37) And Not IsNull(Me.Combo41) Then
  Me.TargetTextbox = DLookup("[Rate]", "Lease_Rates", "[Lease_Year]=" & Me.Combo37.Value & " AND [Class]='" & Me.Combo41.Value & "'")
 End If
End Sub

Replace TargetTextbox with the actual name of your Textbox. Now Select the Textbox then go to Properties - Data and set the Control Source the the appropriate Field in the underlying Table or Query.
Linq ;0)>

 

[Sydcomebak]

missinglinq:

The code worked to populate the value into the TextBox "Rate" but any updates/changes do not overwrite the stored value.

I don't know if this means anything, but the navigator at the bottom is showing me that I am on record 1 of 325 on load.

Edit: I guess it sort of is working, except that the record # at the bottom navigator seems to control which record's "Rate" Field is being updated rather than the dropdowns...

 

[missinglinq]

...The code worked to populate the value into the TextBox "Rate" but any updates/changes do not overwrite the stored value.

Then you didn't follow the last instruction:

"Select the Textbox then go to Properties - Data and set the Control Source to the appropriate Field in the underlying Table or Query."

The Textbox has to be Bound to the Field in the underlying Table/Query in order for it to be updated in the Table.

Linq ;0)>

 

[Sydcomebak]

If I drag it right off of the available fields, I thought it was automatically bound?

I'll double-double check, but I thought it was bound properly...

 

[Sydcomebak]

Linq, please PM me. I'd like for you to remote in and look at this monster. It's easier to show than describe.

 

[Sydcomebak]

*Bump*

Linq, I'm having issues, please email me:

my username @gmail.com

 

[missinglinq]

Sorry, I responded to your post # 9 first thing this morning, but it appears to have gone astray!

But as I said then, at this forum, or at any Access forum, for that matter, we don't do this kind of thing using PMs, emails or by 'remoting in.' Everything has to be done through posting here so that

1. All ideas suggested are available to everyone taking part in the discussion; that way no one wastes time posting a suggestion that has already been put forth
2. The problem is available to all members so that anyone with an idea and/or experience in the particular area may participate
3. Others who may come later, with similar problems, can benefit from the thread

If you need us to look at the app you need to

1. Remove any confidential/proprietary data
2. Zip your File
3. Post it here as an attachment

Be aware that many members. like myself, currently run Access 2003 and cannot open apps saved in the 2007/2010 format, but many members can. You could also save a copy of your app in the earlier, v2000/2003 format and post it. Either way, I'm sure someone will download it and take a look.

Linq ;0)>

 

[oneidalake]

I hope its not poor etiquette to post on such an old thread.

I'm using the above solution, but with a bit of a twist. I need to have the dropdown recordsource show 3 years before the current year AND 3 years after the current year. I have it working, but its not sorting the years properly in the combobox.

I want it to sort like this (2016, 2017, 2018, 2019, 2020, 2021, 2022), but instead its sorting 2019, 2018, 2020, 2017, 2021, 2016, 2022. Note: I still want it to default to the current year and that part is working.

I've tried to insert an orderby statement in several places, but I seem to be doing something wrong. Here it is without any attempt to sort.

Private Sub Form_Current()
'declare a string variable to hold the list of values for populating the combo box list
Dim strValueList As String
'dim a variang type variable to hold the current year
'and the "cntr" variant type varialble to use as a counter
Dim varCurYr, cntr
'assign the value of the current year to the "varCurYr" variable
varCurYr = Year(Date)
'add the value of the current year to the variable that will hold the values for the list
strValueList = varCurYr
'process the following code three times to add more values to the list
For cntr = 1 To 3
    'concatenate the previous year to the string variable each time the code is run
    strValueList = strValueList & "; " & Year(Date) - cntr & "; " & Year(Date) + cntr
Next cntr

With Me.Combo37
     'actually place the list of value in the combo box on the form
    .RowSource = strValueList
     'select the current year in the combo box
    .Value = varCurYr
End With
End Sub

Any ideas how I can sort this?


Thanks in advance.