How to Populate a Control in Code and Make it Behave As Though Populated in the GUI (7 Viewers)

[whdyck]

I have a ComboBox cboDefaultVendorNo with a BeforeUpdate event that displays an error msg and sets Cancel = True if a condition exists. If I select data from that combobox using the form, cboDefaultVendorNo_BeforeUpdate fires. If cboDefaultVendorNo_BeforeUpdate sets Cancel = True, I cannot move off that control without fixing the data in the cbo.

Private Sub cboDefaultVendorNo_BeforeUpdate(Cancel As Integer)
On Error GoTo cboDefaultVendorNo_BeforeUpdateErr

    ' Validate the data
    Cancel = VendorIDBeforeUpdate(Me.cboDefaultVendorNo.Value)

cboDefaultVendorNo_BeforeUpdateBye:
    Exit Sub

cboDefaultVendorNo_BeforeUpdateErr:
    Select Case Err.Number
        Case Else
            MsgBoxErr Me.Name, "cboDefaultVendorNo_BeforeUpdate", , Me
    End Select
    Resume cboDefaultVendorNo_BeforeUpdateBye
End Sub

If I populate that control using code instead, cboDefaultVendorNo_BeforeUpdate does not fire. But I can then run cboDefaultVendorNo_BeforeUpdate in code, which causes it to display the error.

Private Sub cboDefaultVendorNo_DblClick(Cancel As Integer)
On Error GoTo cboDefaultVendorNo_DblClickErr

    ' Populate cboDefaultVendorNo in code
    MakeFormPick Me.Parent.Name, "frmCrdVendor-Pick", "cboDefaultVendorNo", "cboDefaultVendorNo", True, "fsubChild"
    
    If Not IsNull(Me.cboDefaultVendorNo) Then
        ' Is the selected vendor valid?
        cboDefaultVendorNo_BeforeUpdate Cancel
    End If

cboDefaultVendorNo_DblClickBye:
    Exit Sub

cboDefaultVendorNo_DblClickErr:
    Select Case Err.Number
        Case Else
            MsgBoxErr Me.Name, "cboDefaultVendorNo_DblClick", , Me
    End Select
    Resume cboDefaultVendorNo_DblClickBye
End Sub

But then it does not prevent me from moving off that control or even saving the record. Is there a way to make it so that, even if I populate the control in code, it behaves the same as if the user had populated the control using the GUI? (In other words, if the code-populated value fails validation, I'd like the GUI to disallow moving off that control or saving the record until the data is fixed or the update canceled.)

Thanks for any help you can give.

Wayne

 

[RonPaii]

Form events are for user actions, In code you are filling the current data where the user input is not inserted into the field until the after the before update event. But why would you want to populate a control with invalid data from code?

 

[whdyck]

Well, in this case, MakeFormPick() actually displays a lookup form for the user to browse and select the desired value. After the user selects the value in the lookup form, VBA code takes the selected value and populates the target control (cboDefaultVendorNo).

 

[Gasman]

Well surely you would only supply data that was valid for that control?
I once saw a system where one had to pick a town in a particular country, but was also offered towns not in that country?
If one picked one of those, you got an error message, saying that town was not in that country???

 

[MajP]

Regardless if you have data validation at the control level, you still need a final trap at the form's before update event. You should always do this.
Only way to ensure something does not sneak by. Additionally you can have validation on the controls so that you get immediate validation, but things like the data being updated in code can by pass existing control level validation.
But as said your procedure should check for valid data, and if none return alert the user. That procedure could then set focus on your control and you could also run your check on the lost focus. Either way you catch it later in the form's before update.

 

[whdyck]

@Gasman:

It's not that simple.

The Lookup form is a generic form that's used for lots of different data lookups, so it does not check for valid data in that form. It simply passes along the selected data to the target control and expects the target control to validate. This is the system I inherited, so I can't change that behavior.

But that is all a different question from the one I posted originally. Maybe what I'm asking cannot be done.

There are other ways I can prevent the user from saving the bogus data: (1) if the data fails validation in BeforeUpdate, I can revert the data to .OldValue, or (2) I can validate again when the user tries to save the record and throw another error then. But ideally, I'd like it to behave same as if the user just entered the data directly in the form rather than in the lookup form: namely, user sees an error and cannot leave the control without fixing or blanking out the bad data.

 

[MajP]

 MakeFormPick Me.Parent.Name, "frmCrdVendor-Pick", "cboDefaultVendorNo", "cboDefaultVendorNo", True, "fsubChild"

Your pick form knows where to push the selected data.
Can you modify that form to also set focus the to the control that was passed to the pick form when it sets the value.

Then in your controls exit event

Private Sub SomeControl_Exit(Cancel As Integer)
 If NotValid(Me.SomeControl) Then   ' do some validation
   MsgBox "Value Required"
   Cancel = True
 End If
End Sub

Now this could get you into some trouble where you tab into it at another time and you cannot get out.
So you can turn this check on and off

 MakeFormPick Me.Parent.Name, "frmCrdVendor-Pick", "cboDefaultVendorNo", "cboDefaultVendorNo", True, "fsubChild"
me.someControl.OnExit = "[EventProcedure]"

with the handler it will fire after call MakeFormPick

Turn check back off

Private Sub SomeControl_Exit(Cancel As Integer)
 If NotValid(Me.SomeControl) Then   ' do some validation
   MsgBox "Value Required"
   Cancel = True
 Else
   me.someControl.OnExit = ""
end if
End Sub

 

[Gasman]

How about the AfterUpdate then?
Call VendorIDBeforeUpdate(Me.cboDefaultVendorNo) and if True, set focus and send error message?

 

[MajP]

How about the AfterUpdate then

If you set a value of a control neither the before or after update events fire. These only fire from a user action.

 

[MajP]

See if this does what you want.
Double click Val 1 to set the value with the calculator (what I had laying around).
If the value is greater than 100 it is valid. I left Null as also valid

If you use the pop up and it is less than 100 then you cannot exit the control because you can cancel the exit event like the before update event.

Private Sub val1_BeforeUpdate(Cancel As Integer)
  If val1 < 100 Then
    Cancel = True
    MsgBox "Before Update Code: Value One must be 100 or greater.  Returning to Value One"
  End If
End Sub

Private Sub val1_DblClick(Cancel As Integer)
  On Error Resume Next
  Dim rtn As Variant
  Me.Dirty = False
  rtn = getValueFromPopUp("FormCalculator", "lblReadOut", Nz(Me.val1, "0"))
  If Not IsNull(rtn) Then Me.val1 = rtn
  Me.val1.SetFocus
  Me.val1.OnExit = "[Event Procedure]"
End Sub

Private Sub val1_Exit(Cancel As Integer)
  If val1 < 100 Then
    Cancel = True
    MsgBox "On Exit Code: Value One must be 100 or greater.  Returning to Value One"
  Else
    Me.val1.OnExit = ""
  End If
End Sub

However, you have to be very careful when canceling events you do not tie your hands and cannot get out.

 

[arnelgp]

you call VendorIDBeforeUpdate() again on the DblClick Event:

Private Sub cboDefaultVendorNo_DblClick(Cancel As Integer)
On Error GoTo cboDefaultVendorNo_DblClickErr

    ' Populate cboDefaultVendorNo in code
    MakeFormPick Me.Parent.Name, "frmCrdVendor-Pick", "cboDefaultVendorNo", "cboDefaultVendorNo", True, "fsubChild"
    
    If Not IsNull(Me.cboDefaultVendorNo) Then
        ' Is the selected vendor valid?
        Cancel = VendorIDBeforeupdate(Me.cboDefaultVendorNo)
    End If

cboDefaultVendorNo_DblClickBye:
    Exit Sub

cboDefaultVendorNo_DblClickErr:
    Select Case Err.Number
        Case Else
            MsgBoxErr Me.Name, "cboDefaultVendorNo_DblClick", , Me
    End Select
    Resume cboDefaultVendorNo_DblClickBye
End Sub