Access 2010 - Combo box values 1

[ICCIIT]

On my form I wish to have an unbound dynamic combo box that is limited to 2 values only...

The start date of the current week
The start date of the next week
(both being Mondays)

Naturally as time moves on, the requirement is that these dates will move with it..

I cannot find a way to populate these options in the value list with a DateSerial function

Anyone able to give me some pointers?

Thanks

 

[Andrzejek]

How about:

Dim datMonday As Date

datMonday = Date

Do Until Weekday(datMonday) = vbMonday
    datMonday = datMonday - 1
Loop

With cboMondays
    .AddItem datMonday
    .AddItem datMonday + 7
    .ListIndex = 0
End With

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ICCIIT]

Hi Andy
Cannot get this to work at the moment, I have placed this code in the on-click event of the combo but it does not populate the list... it does not error either so not sure what is happening..

The combo Row source is set to Value list
and Rowsource is empty
Bound column=1

Thanks

 

[ICCIIT]

Andy
OK.... some success...

Its now in my FormLoad event
it gives me error 7777

"You have used the Listindex property incorrectly"

But when I end the error, the combo shows me the 2 dates....

so nearly there!




(

 

[Andrzejek]

Could you try this:
1. Start a new Access with one UserForm
2. Place a combo box, name it cboMonday (you may want to set it to List so you cannot type in it)
3. Place a command button on the Form
4. Place the code in the Click event of the command button.
5. Run the application, click on the button

You may also try this in Excel (UserForm, combo box, command button, etc.), it should work the same way.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ICCIIT]

OK.... I have now removed the .listindex=0 from within the With statement and its all working!!

very elegant solution

Thanks

 

[ICCIIT]

Revised code

Now in the Form Current event...

Private Sub Form_Current()
Dim datMonday As Date

datMonday = Date

Do Until Weekday(datMonday) = vbMonday
    datMonday = datMonday - 1
Loop

With cboMondays
    .AddItem datMonday
    .AddItem datMonday + 7
    
End With

 

[Andrzejek]

instead of
[tt].ListIndex = 0[/tt]

you may try
[tt].ItemData(0)[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

O, just to be on the safe side, Clear it before populating.

With cboMondays
    .Clear
    .AddItem datMonday
    .AddItem datMonday + 7
    .ItemData(0
End With

I'm not sure how often the Curent event fires.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ICCIIT]

Thanks Andy... a deserved star....I can see many uses for this....