Before Update event

[redalert]

I have an unbound form with a combobox which needs to be validated before the control is updated.

In the Before Update event I check that the value is valid and need to reset the value back to the previous value if not valid and then change focus to another control.

I thought that the following code would work but the selected value remains in the control and I cannot move the focus to another control.

Any ideas anyone?

Private Sub cboYears_BeforeUpdate(Cancel As Integer)
Dim dteNewDate As Date

    dteNewDate = DateSerial(Val(Me.cboYears), Month(Me.txtCalendarHeading), 1)

    If Not fncIsDateInRange(fncFirstDayInMonth(dteCalStartDate), fncLastDayInMonth(dteCalEndDate), dteNewDate) Then
        
        MsgBox "Date not in range"
        
        Cancel = True
        
        Me.cmdCancel.SetFocus
        
    End If

End Sub

 

[Roku]

Try this:

Private Sub cboYears_BeforeUpdate(Cancel As Integer)
Dim dteNewDate As Date
dteNewDate = DateSerial(Val(Me.cboYears), Month(Me.txtCalendarHeading), 1)
If Not fncIsDateInRange(fncFirstDayInMonth(dteCalStartDate), fncLastDayInMonth(dteCalEndDate), dteNewDate) Then
  MsgBox "Date not in range"
  Me.cboYears.Undo
  Cancel = True
End If
End Sub

If the value is not correct, the textbox content is highlighted; the the user must change to a valid value before the next control can be selected.

I suggest you check for null in the controls, in case no value has ever been entered. You can use IsNull or Nz functions for this.

 

[CJ_London]

How do you know the error is not in the function?

 

[redalert]

The function is working fine and the Msgbox line executes.

I can use the After Update event to the same effect but it means changing the value of the cboYears Combobox back to the previous value after having first saved it in a variable first.

It bugs me when things do not work in the way in which the documentation appears to suggest that it should.

 

[pr2-eugin]

Use Roku's code, but I think Cancel = True come before the Undo method.

Private Sub cboYears_BeforeUpdate(Cancel As Integer)
    Dim dteNewDate As Date
    dteNewDate = DateSerial(Val(Me.cboYears), Month(Me.txtCalendarHeading), 1)
    If Not fncIsDateInRange(fncFirstDayInMonth(dteCalStartDate), fncLastDayInMonth(dteCalEndDate), dteNewDate) Then
        MsgBox "Date not in range"
        Cancel = True
        Me.cboYears.Undo
    End If
End Sub

 

[redalert]

Roku: RE: If the value is not correct, the textbox content is highlighted; the the user must change to a valid value before the next control can be selected.

After the following code is executed, the combobox selection is still highlighted but I can still move to another control with the Tab Key or the Mouse and the incorrect selection is still visible. The After Update event is not called as expected.

Cancel = True
Me.cboYears.Undo

Re: I suggest you check for null in the controls, in case no value has ever been entered.

None of the values in the Value List are empty so this will not be a problem.

 

[pr2-eugin]

The do not UNDO the change.. Undo makes it to go to a valid option.. Just use Cancel = True.

 

[Roku]

Hmmm

I just tried a quick test using a combo box with three values - 2012, 2013 & 2014. With this, I used the following:

Private Sub cboYears_Enter()
Rem save the current value
intYears = Nz(Me.cboYears, 0)
End Sub
 
Private Sub cboYears_Exit(Cancel As Integer)
Rem check for invalid selection - restore previous value
Rem this is not a sensible test! Use something appropriate for your purposes
If Me.cboYears = 2013 Then Me.cboYears = intYears
End Sub

If you select 2012, the value remains on exit.
If you then select 2013, the value is restored to 2012.
Now select 2014 - remains intact. Again, select 2013 and the value is restored to 2014.

In other words, 2013 is deemed invalid, so the previous valid values of 2012 and 2014 are restored.

Perhaps you can build on this for what you need?

As an aside, I think Undo may depend on the value being bound to a table - but I'm not sure. I'm not going to test this just now, but someone else may be able to confirm (or otherwise ).