Open form make combo box select year

[Ruegen]

I have a combo box on a form with row source of table below



ID Year

1 2012
2 2013
3 2014


I'm trying to make a combo box on the form open to the current year - each year is year(#1/1/2014#), year(#1/1/2013#) etc

With Me
.cboYear = Format(Now, "yyyy")
Debug.Print .cboYear
.cboMonth = month(Date)


End With

month works fine because I am guessing months work from 1 - 12

however year won't give me the right result.

I have to be able to switch the year later in the combo box.

[ItsMe]

What is the bound columnfor the combo

Me.cboYear.Value is going to return the bound column

where
Me.cboYear.Column(0) will return the first column, regardless of the bound column

[Ruegen]

I've played around with that

I had bound column on 2 and it brought up the year however I can't change the year (I get an error)

I tried me.cboyear.column(1) as that is the second column (with year) and that gives me an error too

I notice the date gets changed into and early 1900 date too

note I have bound column to 1

[Ruegen]

so if I make .cboYear = format(now,"yyyy")

it works when bound to column 2

however changing the year gets me the error above (guessing since the bound column needs to be 1)

[Ruegen]

Private Sub cboYear_Click()
Me.cboYear.BoundColumn = 1
End Sub

.....maybe? just to make it swap back?

I took out format property "yyyy" on the combo box - now it gives me a different error

I have tried on enter and that updates the bound column

[ItsMe]

It is only going to change the row.

So if you have bound column 2

Me.cboYear.Value = 2013
would cause

msgbox Me.cboYear.Column(0)

to return 2

There is not a way to adjust the value of a single field within a row of the combo's rowsource. The exception to the rule is to use a Value list and edit the value list. But then why not base the combo on a table and update the table.

[Ruegen]

It is only going to change the row.

So if you have bound column 2

Me.cboYear.Value = 2013
would cause

msgbox Me.cboYear.Column(0)

to return 2

There is not a way to adjust the value of a single field within a row of the combo's rowsource. The exception to the rule is to use a Value list and edit the value list. But then why not base the combo on a table and update the table.

I have a table of years that relates to bookings created in those years

The form is a calendar - I am trying to open up the calendar to the same years as what the bookings are (hence the row source)

The trick I will use in VBA is selecting the year using the combo box will change the year on the calendar form and thus the months and dates - in turn those dates will represent visible bookings.

I have seen it done using a value list but that requires me typing it in and doesn't update when a list is added in the table.

Hence if they add a year say 2015 in the table then correspondingly the combo box gets that year.

Then I wanted the calendar to open up to now and current date and in turn the combo box and month combo box (which the month one does work because it knows the value)

since year() doesn't know what year() is in table number I can't assign/tie it to anything I guess....

The reason I want to use a row source is because I don't want a value list - with a table I can restrict the user to the same year as the bookings, area, etc etc etc

[Ruegen]

I thought about filtering with the same date but then even though it would show me the row source with the same date on that column - I would only get that column unless I took the filteron to false on click...

[Ruegen]

I've swapped some things around and it seems to be working

I have made the column into a string and filter it by current date which is also a string (dim)

the code below

    Dim theYear As String
    Dim strsql As String
    Dim intNum As Integer
    theYear = Replace(CStr(year(Date)), "/", "")
    Debug.Print theYear
  With Me
.cboMonth = month(Date)


strsql = "SELECT tblShowYear.ShowYearID, Replace(CStr(Year([ShowYear])),""/"","""") AS ShowYearString, tblShowYear.ShowYear " _
& "FROM tblShowYear " _
& "WHERE (((Replace(CStr(Year([ShowYear])),""/"",""""))=" & theYear & "));"
    
.cboYear.RowSource = strsql
.cboYear = .cboYear.ItemData(0)




End With

when the user clicks on the combo box I will swap out the filtered row source to one that isn't.

Then they will see all the years

the swap out

Dim strsql As String


strsql = "SELECT tblShowYear.ShowYearID, tblShowYear.ShowYear, Replace(CStr(Year([ShowYear])),""/"","""") AS ShowYearString " & _
"FROM tblShowYear;"


Me.cboYear.RowSource = strsql
Me.cboYear.Requery

[Ruegen]

I've swapped some things around and it seems to be working

I have made the column into a string and filter it by current date which is also a string (dim)

the code below

    Dim theYear As String
    Dim strsql As String
    Dim intNum As Integer
    theYear = Replace(CStr(year(Date)), "/", "")
    Debug.Print theYear
  With Me
.cboMonth = month(Date)


strsql = "SELECT tblShowYear.ShowYearID, Replace(CStr(Year([ShowYear])),""/"","""") AS ShowYearString, tblShowYear.ShowYear " _
& "FROM tblShowYear " _
& "WHERE (((Replace(CStr(Year([ShowYear])),""/"",""""))=" & theYear & "));"
    
.cboYear.RowSource = strsql
.cboYear = .cboYear.ItemData(0)




End With

when the user clicks on the combo box I will swap out the filtered row source to one that isn't.

Then they will see all the years

the swap out

Dim strsql As String


strsql = "SELECT tblShowYear.ShowYearID, tblShowYear.ShowYear, Replace(CStr(Year([ShowYear])),""/"","""") AS ShowYearString " & _
"FROM tblShowYear;"


Me.cboYear.RowSource = strsql
Me.cboYear.Requery

ignore the replace() - I don't actually need that

[Ruegen]

updated without replace()

Private Sub cboYear_Enter()
Dim strsql As String


strsql = "SELECT tblShowYear.ShowYearID, tblShowYear.ShowYear, CStr(Year([ShowYear])) AS ShowYearString " & _
"FROM tblShowYear;"


Me.cboYear.RowSource = strsql
Me.cboYear.Requery
End Sub


Private Sub Form_Load()




    Dim theYear As String
    Dim strsql As String
    Dim intNum As Integer
    theYear = CStr(year(Date))
    Debug.Print theYear
  With Me
.cboMonth = month(Date)


strsql = "SELECT tblShowYear.ShowYearID, Str(Year([ShowYear])) AS ShowYearString, tblShowYear.ShowYear " _
& "FROM tblShowYear " _
& "WHERE (((CStr(Year([ShowYear])))=" & theYear & "));"
    
.cboYear.RowSource = strsql
.cboYear = .cboYear.ItemData(0)




End With


End Sub

[ItsMe]

Man, that looks like a lot of work. I looked up what the ItemData property does. So you create a custom Rowsource and then retreive the ItemData from the first column? Does this have anything to do with User input/selection of the combo?

[Ruegen]

Man, that looks like a lot of work. I looked up what the ItemData property does. So you create a custom Rowsource and then retreive the ItemData from the first column? Does this have anything to do with User input/selection of the combo?

Yeah, if the form opens by itself it will open to current date automatically selected. If a user opens it to a specific tour term (4 terms in a year) then it shows the month relevant. If they change the year it changes the calendar and the dates of that month for that year (although I still have to do that).