Combo box changing values

[Adam]

Hi All,

I want to have a combo box which has values like below:

Month(now())-3
Month(now())-2
Month(now())-1
Month(now())
Month(now())+1

I realise my formula might be way out, but I hope you can see what I
mean.

Does anyone know how to do this? If even possible?

Many Thanks

Adam

 

[Douglas J. Steele]

You need to create a function in a very specific format, and then use that
function for the combo box's RowSource.

Function FiveMonths( _
fld As Control, id As Variant, _
row As Variant, col As Variant, _
code As Variant) As Variant
Dim intOffset As Integer
Select Case code
Case acLBInitialize
FiveMonths = True
Case acLBOpen
FiveMonths = Timer
Case acLBGetRowCount
FiveMonths = 5
Case acLBGetColumnCount
FiveMonths = 1
Case acLBGetColumnWidth
FiveMonths = -1
Case acLBGetValue
FiveMonths = Month(Date) + (row - 3)
End Select
End Function

Set the RowSource to FiveMonths, and leave the RowSourceType property blank.

If you want the name of the month to appear (with the month number as a
hidden first column), try:

Function FiveMonths( _
fld As Control, id As Variant, _
row As Variant, col As Variant, _
code As Variant) As Variant
Dim intOffset As Integer
Select Case code
Case acLBInitialize
FiveMonths = True
Case acLBOpen
FiveMonths = Timer
Case acLBGetRowCount
FiveMonths = 5
Case acLBGetColumnCount
FiveMonths = 2
Case acLBGetColumnWidth
If col = 0 Then
FiveMonths = 0
Else
FiveMonths = -1
End If
Case acLBGetValue
If col = 0 Then
FiveMonths = Month(Date) + (row - 3)
Else
FiveMonths = _
Format(DateAdd("m", (row - 3), Date), "mmmm")
End If
End Select
End Function

(Hopefully I've made the lines short enough that there won't be any
word-wrap!)

 

[Adam]

Hmm this doesnt seem to do anything for me?

I've created a new module, and pasted in the below code ( I tried both
ones ), but it doesnt seem to work.

 

[Douglas J. Steele]

Did you remember to set the RowSource and RowSourceType properties of your
Combo Box?

 

[Adam]

Yeah, just FiveMonths in RowSource and delete whatevers in
RowSourceType to leave it blank.

 

[Douglas J. Steele]

Don't understand, then. I just repeated it in a new database: copied the
function into a module (not a class module), created a new form with a
combobox and set the properties and it worked fine for me.

 

[Adam]

The combobox is bound, does that matter?

Don't understand, then. I just repeated it in a new database: copied the
function into a module (not a class module), created a new form with a
combobox and set the properties and it worked fine for me.

 

[Adam]

Ah

It needs to go in RowSourceType not RowSource.

It works now.

Thanks for your help!

 

[Douglas J. Steele]

Bound to what type of field? Since the combo box is going to return values
of 6, 7, 8, 9 and 10, the data to which its bound must have those values.

 

[Adam]

I've noticed one problem though!

This saves the value as a text value, could it be converted to date?

I know it only shows June, July etc but it would be complete if it
could show June-06, July-06 and be a date format.

 

[Douglas J. Steele]

D'Oh! Yes, RowSourceType needs to be the function, and RowSource needs to be
blank.

Sorry about that!

 

[Douglas J. Steele]

"June-06" isn't actually a date: dates have to have a day as well.

To have it display June-06 rather than June, change

FiveMonths = _
Format(DateAdd("m", (row - 3), Date), "mmmm")

to

FiveMonths = _
Format(DateAdd("m", (row - 3), Date), "mmmm\-yy")

 

[Adam]

And will this store the date value in a date field?

 

[Douglas J. Steele]

As I said, a Date requires a day: June-06 is not a date.

What are the actual values in the bound field?

 

[Adam]

Well I'll enter 01-06-06 and it will show June-06, this is fine. All i
need is the month and year. In my querys later I will just search in
the month.

 

[Douglas J. Steele]

In that case, change

Case acLBGetValue
If col = 0 Then
FiveMonths = Month(Date) + (row - 3)
Else
FiveMonths = _
Format(DateAdd("m", (row - 3), Date), "mmmm")
End If

to

Case acLBGetValue
If col = 0 Then
FiveMonths = DateSerial( _
Year(DateAdd("m", (row - 3), Date), "mmmm")), _
Month(DateAdd("m", (row - 3), Date), "mmmm")), _
1)
Else
FiveMonths = _
Format(DateAdd("m", (row - 3), Date), "mmmm")
End If

 

[Adam]

I'm getting a compile error?

In that case, change

Case acLBGetValue
If col = 0 Then
FiveMonths = Month(Date) + (row - 3)
Else
FiveMonths = _
Format(DateAdd("m", (row - 3), Date), "mmmm")
End If

to

Case acLBGetValue
If col = 0 Then
FiveMonths = DateSerial( _
Year(DateAdd("m", (row - 3), Date), "mmmm")), _
Month(DateAdd("m", (row - 3), Date), "mmmm")), _
1)
Else
FiveMonths = _
Format(DateAdd("m", (row - 3), Date), "mmmm")
End If

 

[Douglas J. Steele]

My fault. I was thinking of something totally unrelated!

If col = 0 Then
FiveMonths = DateSerial( _
Year(DateAdd("m", (row - 3), Date)), _
Month(DateAdd("m", (row - 3), Date)), _
1)
Else
....

Sorry about that!

 

[Adam]

That works a charm!

Thanks for all your help Douglas!