Help with Cancel Parameter in Before Update Event

[CarlRostron]

I am trying to use a Combobox BeforeUpdate Event to achieve a required result.

I have two combo boxes inside a frame. The first is a year number 2011, 2012, 2013, 2014

The Second is the Week No ranging from 1-52, but 1-53 in some cases and this updates accordingly with the year number selected.

When the user has selected Week 53 and then changes the year to a year where only 52 weeks exist I want to Cancel their event.

I have already made a function to determine if 53 weeks exits however when I come to do the Cancel Event, the combobox for the year doesn't change back. It stays on the users new selection.

Below is the code that I am trying to use:

Private Sub cmbWeekNoFromYear_BeforeUpdate(Cancel As Integer)
  
  If UpdateWeekNoSelections Then
    Cancel = True
    Me.cmbWeekNoFromYear.Undo
  End If
End Sub

'cmbWeekNoFromYear is the name of the combobox which holds the year number

'UpdateWeekNoSelections Simply returns True if I want the users change cancelled for cmbWeekNoFromYear

 

[James Dudden]

Wouldn't it be easier - if the user changes the year to a 52 week year - to just update the box with 52 rather than cancelling anything?

 

[CarlRostron]

Yes that is another way and maybe I'll take that approach.

However, I would still like to understand what I need to do to fix this (just so i know if I wanted to do it again in the future)

 

[James Dudden]

Looking at the code I think it should do as required unless the function is not retuning the correct value and the cancel never gets triggered.

Have you stepped through the code to see what is happening?

 

[CarlRostron]

Yes, the function return comes back correctly and the code enters inside the if condition as expected. I know this because I have 'Stepped' through it as you mention.

Those two lines: Cancel = True, and the .Undo function does nothing though to cancel the User Action.

 

[pr2-eugin]

Are the combo boxes unbound? If yes, there is your answer. It does not have an OldValue to go back to. If it is bound it should work.

A little bit of twisted programming might help you do the trick ! But I would not suggest that !

 

[CarlRostron]

Yes it is unbound.

Is there a way around this?

 

[pr2-eugin]

Normally Cascading style allow only requery of Lower ComboBoxes, based on the ones on the top.. For example if you select year 2013, then the next combo box will have either 53 or 52 not both. If you want to change it to 53, you need to select the appropriate year that has 53. Not the other way around ! So fix this issue.