Input form data validation on textboxes

Question

Hi,

I have the following data input form. The users will input the data for all the fields, but for some reason my data validation doesn't seem to work.

The code I have for validating the fields is this:

Private Sub submit_Click()

Dim prod As String

Dim SN As String

Dim PO As String

Dim SO As String

Dim TT As String

If IsNull(Me.enter_date) Then

MsgBox "Date cannot be empty!", vbInformation, "Date is required"

Me.enter_date.SetFocus

    ElseIf IsNull(Me.product) Then

MsgBox "Product cannot be empty!", vbInformation, "Product is required"

Me.product.SetFocus

ElseIf IsNull(Me.serial_number) Or Len(Me.serial_number.Value) = 5 Or Not IsNumeric(Me.serial_number.Value) Then

MsgBox "Serial Number cannot be empty and only numbers are allowed in this field!", vbInformation, "Serial Number required"

Me.serial_number.SetFocus

ElseIf IsNull(Me.prod_number) Or Len(Me.prod_number.Value) = 8 Or Not IsNumeric(Me.prod_number.Value) Then

MsgBox "Production Order Number cannot be empty and has to be 8 digits!", vbInformation, "Check PO"

Me.prod_number.SetFocus

ElseIf IsNull(Me.sale_order) Or Len(Me.sale_order.Value) = 9 Or Not IsNumeric(Me.sale_order.Value) Then

MsgBox "Sales Order cannot be empty and has to be 9 digits!", vbInformation, "Check SO"

Me.sale_order.SetFocus

ElseIf IsNull(Me.input_test) Then

MsgBox "Test Technician Name cannot be empty!", vbInformation, "Test Technician missing"

Me.input_test.SetFocus

ElseIf IsNull(Me.inspector) Then

Me.inspector = TempVars!OperatorName

Else

ED = Me.enter_date.Value

prod = Me.product.Value

SN = Me.serial_number.Value

PO = Me.prod_number.Value

SO = Me.sale_order.Value

OpName = TempVars!OperatorName

TT = Me.input_test.Value

DoCmd.RunSQL "INSERT INTO tblData ([enter_date], [product], [serial_number], [prod_number], [sale_order], [inspector], [Test Stage 1]) VALUES ('" & ED & "','" & prod & "','" & SN & "','" & PO & "','" & SO & "','" & OpName & "','" & TT & "')"

MsgBox "Data entry complete", vbInformation, "Task Completeted Successfully"

End If

End Sub

---

The values that I need to validate are:

Serial number - 5 digits, numeric, not null

Order number - 8 digits, numeric, not null

Sales order - 9 digits, numeric, not null

When I try to verify that it works, I get the following error and in the data table, I get a record with empty rows. Access is not running the data validation rules from above, but changing the value to null.

This is how I have the table set up. I understand why the error above comes up, because the fields are set to numbers, but why isn't the data validation kicking in before?

Also, one other odd thing, when I click the "Submit" button to add the record, the first time it doesn't work but if I click it again it works.

Answer 1

This is what we have a debugger for. Set a breakpoint at the top and step through.

IsNull(Me.serial_number) And Len(Me.serial_number.Value) = 5

Expressions like this are not often true.

The type conversion error you can debug by inspecting the field that gets the null value. What you are sending does not fit that data type.

It is not clear to me why you are using an unbound form. It's usually more hassle than it's worth.

Answer 2

Good for you. It's all a matter of perspective. You either test for what's good, or for what's bad, but you cannot mix. I usually use the side that has the fewest Not's.

Btw, you can condense a bit more:

IsNull(Me.serial_number) Or Len(Me.serial_number.Value) <> 5 Or Not IsNumeric(Me.serial_number.Value)

This is good enough:

Not IsNumeric(Me.serial_number & "")

Note the type coercion from what can be a null to an empty string.

Answer 3

I figured it out.

ElseIf IsNull(Me.serial_number) Or Len(Me.serial_number.Value) <> 5 Or Not IsNumeric(Me.serial_number.Value) Then

ElseIf IsNull(Me.prod_number) Or Len(Me.prod_number.Value) <> 8 Or Not IsNumeric(Me.prod_number.Value) Then

ElseIf IsNull(Me.sale_order) Or Len(Me.sale_order.Value) <> 9 Or Not IsNumeric(Me.sale_order.Value) Then

The logical test should have been Or, not And and to make sure the length of the fields is correct, it should have been <> not =.

Now it seems such a stupid thing to do. After taking a break from it for a few minutes I saw it straight away.