ms access 2000 data validation 1

[thepope77]

Hi:
I need to perform data validation on an AfterUpdate Event. I am validating a dimension- a tolerance. I need to specify a range of which the data can be. Like, between 0.172 and 0.174, for example.

The data to be validated is in a text box. But the criteria for what the range needs to be depends on what is selected in a combo box on the same form. For example:

If cboWireSize=0.172 Then
txtActualWireSize >=0.172 or <=0.174
Else MsgBox &quot;The wire is out of spec.&quot;
End If

Pardon any inconsistencies. I am a newbie to this game.
Any help is greatly appreciated. Thanks a lot.

Daniel Pope

 

[TonyJollans]

Hi thepope77,

You should normally do validation in a BeforeUpdate event - AfterUpdate is too late. Apart from that, what is your problem / question?

Enjoy,
Tony

 

[thepope77]

Hi Tony:

I guess the BeforeUpdate is a better choice for this. Like I said, I am a newbie. I know just enough code to be dangerous.

My problem is that I am not exactly sure how to write the code to validate these data as there are about 10 different wire sizes. Will I have to declare variables in the procedure or can I simply do the validation on the text box?

I appreciate any help and thanks for bearing with me and my newness.

Daniel

 

[TonyJollans]

Hi Daniel,

Based on what you have posted so far here is some code you can start with. I've made up the values as you will see; other than that it should be OK.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Low As Double, High As Double

If IsNull(Me.cboWireSize) Then
    MsgBox "Please select one of the choices in the combo"
    Cancel = True
    Me.cboWireSize.SetFocus
    Exit Sub
End If

If IsNull(Me.txtActualWireSize) Then
    MsgBox "Please enter a value"
    Cancel = True
    Me.txtActualWireSize.SetFocus
    Exit Sub
End If

Select Case Me.cboWireSize
    Case "Hairline":  Low = 0.001:  High = 0.002
    Case "Thin":      Low = 0.01:   High = 0.02
    Case "Medium":    Low = 0.1:    High = 0.2
    Case "Thick":     Low = 1:      High = 2
    Case Else
        MsgBox "Please contact the Help desk"
        Cancel = True
        Me.Undo
        Exit Sub
End Select

If Me.txtActualWireSize < Low Or Me.txtActualWireSize > High Then
    MsgBox &quot;The wire is out of spec.&quot;
    Cancel = True
    Me.txtActualWireSize.SetFocus
End If

End Sub

Enjoy,
Tony

 

[thepope77]

Thanks a lot Tony. That really helped. Even I can get that to work without any problem.

I did try this on a form that contains a subform but the data in the subform doesn't show up until the AfterUpdate event. Do you know what might be causing that? This is not a major issue so you don't have to respond. Just curious. You have helped me out immensely already.

Thanks much again. Hopefully, soon, I can give a little back rather than just taking.

Daniel