Dynamic Default Value For Combo Box

[Help.Chris]

Hi everyone,

It would be a great help if someone can help me do this.

I have a combo box, for selecting months in. This is based on a table containing a unique id, month name, month start and end date.

I would really like to be able to have the current month displayed as the default value.

Thanks everyone.

Chris

 

[Abby N]

Hello Chris. If your months are in an abbreviated format (Jan, Feb, Mar, ect...) then place this in the combo's Default Value.

=Format(Date(),"mmm")

If you use the full names of the month then use four M's instead of three. Good luck.

~Abby

[This message has been edited by Abby N (edited 05-02-2001).]

 

[Help.Chris]

That would work on a text box, but not in a combo box. The default value needs to be one selected from the table containing the month names. Hence I think it would need some kind of select statement, but I can't see how to do it.

Chris

 

[AccessUser]

Try this on the form Open Event

Dim varItm As Variant

For varItm = 0 To Me.ComboBox.ListCount - 1
If Me.ComboBox.Column(ColumnOfMonthName, varItm) = Format(Date(),"mmm") Then
'select record
Me.ComboBox = Me.ComboBox.Column(ColumnOfMonthName, varItm)

'exit after the match is found
Exit For
End If
Next varItm

 

[Abby N]

It worked fine on the combo box I created in a test database in Access 97. Are you getting an error? If so, what data type and format is the ‘Month’ field in your table?

I believe the original formula, or a similar variation, will work. However, as an alternative, you had mentioned that you record a start-date and end-date for each month. If so then this may do the trick. You'll have to change the table and field names of course. Again, this would go in the combo's Default Value.

=DLookUp("[MonthField]","MonthsTable","[StartDate] <= #" & Date() & "# and [EndDate] >= #" & Date() & "#")

I hope this helps.
~Abby