"Year" combobox default - can't get it to show

[Montez659]

I have a combobox that will display a list of years to choose. I am not even quite sure of the best way to fill it, but I tried doing it by values and from a table.

Either way, I am not getting it to populate with the current year, which I thought would be a simple

Me.cmbYear.Value = Year(Date())

I can actually get a month box to populate the correct month using this same method above, but it ain't working for year.

Any suggestions? Thanks in advance!

 

[Norie]

How exactly did you populate the combobox?

What values were in the table when you tried it that way and what value(s) did you use when trying to populate it with values?

By the way did you not try the combobox's Default property?

What I'm wondering is if it's a text/number thing.

PS Sorry for all the questions, and go easy on me - I've just spent the last 3 hours resurrecting this machine.

My own fault of course tried to 'clean' it up a bit.

 

[xenou]

I can actually get a month box to populate the correct month using this same method above, but it ain't working for year.

There is indeed a mystery here ...

 

[Montez659]

I am thinking that it is simply a matter of "type" as well. I actually tried a few different ways. I tried storing it as text and then as number in a table and populated it that way. Made sure in the code to add a CStr or CInt to make sure it was of the same type. No dice.

I also tried to populate it as a value list and was unable to get it to populate that way either.

The only thing that I haven't tried is to store a date in the table, i.e., 1/1/2011, 1/1/2012, and then have the combobox display an expression with Year.

The only time I was able to get it to populate was when I set default to 1, or 2 which was the record ID number from the table. Weird that I couldn't do it by date this way either.

Maybe I need to add an expression to make sure that the field in the table store as "Number" is really being read as an integer when populating.

 

[Norie]

The default will determine the value of the bound column.

What is this list of dates going to be used for?

Another thing you could try is setting the ListIndex.

Not sure if that's possible in Access but if they've added AddItem you never know.

How have you tried populating?

You've mentioned various methods but not really given much detail.

 

[xenou]

I would probably populate such a combobox with code in the form open event:

PSEUDOCODE (may not be correct syntax):

'//Populate years to choose - assume combobox uses a value list as its row source type
For i = 1 to -4 Step -1
    s = (Year(Date())+i) & ";"
Next i
Me.ComboBox1.RowSource  = Left(s,Len(s)-1)
Me.combobox1.Value = Year(Date())

Although it really should be a piece of cake to do so by using a query on values from a table. You are probably overthinking this.

 

[xenou]

Okay, forget the pseudocode. I mocked up a sample (both using a value list and a table as a data source). No problems. I'm not sure what is making this so hard!

Though you can see in my demo that the first has text values in it (loaded with a value list), and the second has number values in it (bound to the table). Pick your poison...

<a href="http://northernocean.net/etc/mrexcel/20110710_database101.zip">Sample Database</a>
sha256sum (zip file): 38b3ca25ebd926c415e6fdcd2a566394ac139a176da79cfc5418f7713d9fcba1

ξ


Edit: BTW, this was my actual code to populate the combobox on the fly in the form open event (this is for a combobox with a row source type of value list):

Private Sub Form_Open(Cancel As Integer)
    Dim s As String
    Dim i As Long
    
    For i = 1 To -3 Step -1
        s = s & (Year(Date) + i) & ";"
    Next i
    Me.Combo0.RowSource = Left(s, Len(s) - 1)
    
End Sub

 

[Norie]

This worked for me.

Option Compare Database
Option Explicit
 
Private Sub Form_Load()
Dim I As Long
Dim NoMonths As Long
Dim StartYr As Long
Dim NoYrs As Long

    Combo0.RowSourceType = "Value List"
 
    StartYr = 2005
        
    NoYrs = 10
    
    For I = StartYr To StartYr + NoYrs
 
        Combo0.AddItem I
        
    Next I
    
    Me.Combo0 = Year(Date)
 
End Sub

 

[Montez659]

Xen, I downloaded that sample but all I got was a blank db.

I will try the suggestions posted. I know that this should be a simple thing, which is why I can't figure out why it wouldn't work.

 

[xenou]

Okay,
I put another sample out again.
<a href="http://northernocean.net/etc/mrexcel/20110711_db.zip">Sample Database</a>
sha256sum (zip file): 46c776d626ffe5e8102433058636624093ce701ab2be7ffe28d2b1415df0d388

Is this a bound control or unbound control?