Select Default Item in ComboBox

[Jason1971]

Hi,

I have a combobox displaying a list of years...2009, 2010, 2011, etc. I want to set the combobox's default value to year(now()) -1. i.e. 2009.
My options are to put some sort of expression in the combobox's default value property OR use VBA code.

Any suggestions on an expression or VBA code?

Thank you.
Jason.

 

[vbaInet]

Expression:

Year(Date()) - 1

You had it. Didn't you try using yours?

 

[Jason1971]

Expression:

Year(Date()) - 1

You had it. Didn't you try using yours?

It only works for new records; which isn't applicable here; the combobox is unbound (the combobox looks up its values from a table but does not write to this table).
I want the combobox to pre-select / pre-populate with 2009 (the current year -1) on load by simply looking up 2009 (or the current year -1) in the table.

 

[vbaInet]

Any particular reason why you've decided to use a combo box for this? What about a text box? Is this value going to be edittable?

On the form's Load event do this:

comboboxName.value = Year(Date()) - 1

 

[Jason1971]

comboboxName.value = Year(Date()) - 1 doesn't work because comboboxName.value is calling for the record ID not the year (it is a 2 column combobox: ID, RRYear).

I guess I need to 1)find the record of interest, and then 2) set my combobox ID to this found record of interest. To this end, I have written the code below. But the FindFirst line in bold below is not finding anything/not working.

Private Sub Form_Load()
    Dim dbsCurrent As DAO.Database
    Dim rstYearRecords As DAO.Recordset
    
    Set dbsCurrent = CurrentDb
    Set rstYearRecords = dbsCurrent.OpenRecordset("Tbl_Year", dbOpenDynaset)
 
    [B]rstYearRecords.FindFirst "RRYear = year(now())-1"[/B]
            
    Me.Combo15.Value = CurrentRecord
    
    rstYearRecords.Close
    Set dbsCurrent = Nothing
    Set rstYearRecords = Nothing
    
    
End Sub

 

[vbaInet]

You didn't mention it was a multi-column combo.

Use this:

Dim i as integer

For i = 0 to comboBoxName.ListCount - 1
    If comboBoxName.value = Year(Date()) - 1 Then
        comboBoxName.value = comboBoxName.column(0,i)
        Exit For
    End If
Next

I'm not sure why you've manually put those year values in the combo? If you're going to set it using code and their's no human interaction with the combo then you don't need pre-defined values.

 

[Jason1971]

Thanks vbaInet! It worked like a charm!
The code I am using is:

Private Sub Form_Load()
    
    Dim i As Integer
    Dim varLastYear As Integer
    
    varLastYear = Year(Now()) - 1
    
    For i = 0 To Me.Combo15.ListCount - 1
        If Me.Combo15.Column(1, i) = varLastYear Then
            Me.Combo15.Value = Me.Combo15.Column(0, i)
            Exit For
        End If
    Next
    
End Sub

 

[vbaInet]

Excellent! Glad to have helped.