validation error despite cancel in beforeupdate 1

[ramzoid]

I have a data-entry form with one bound text field to receive a number from a barcode scanner. The BeforeUpdate event for this field contains code to verify that the ID number scanned is valid:

Private Sub barcode_BeforeUpdate(Cancel As Integer)
    If DCount("*", "barcode name", "barcode=" & Me!barcode) = 0 Then
        Cancel = True
        Me!barcode.Undo
        Text_Box_for_Message = "I could not recognize your ID card.  Please sign in by hand on the paper sign-in sheet."
    End If
End Sub

There is nothing in the "validation rule" for this field, just the above code in BeforeUpdate.

(The error message appears in an unbound text box instead of a msgbox so the user doesn't have to hit enter.)

Everything's fine if a valid number is scanned, but if an invalid number is entered, I get a validation error.

I've tried this with and without the Undo; no difference. I moved the message to display after the cancel & undo to see if Access gets that far before the error, and the message does appear before it errors out.

Why isn't it cancelling?

Any insight is much appreciated!

Joe

 

[TheAceMan1]

How are ya ramzoid . . . . .

An initial guess, but I'm forced to believe Me!barcode is [blue]alphanumeric[/blue]. If true, use the following istead:

[blue]If DCount("*", "barcode name", "barcode='" & Me!barcode & "'") = 0 Then[/blue]

See Ya! . . . . . .

 

[ramzoid]

AceMan,

A very reasonable guess (thanks!), but it doesn't appear that that was the problem. I got a data type mismatch when I tried it.

Thanks though! Any other hunches?

Cheers!

 

[ramzoid]

So, I'm thinking through this a little more... The "If" statement with the Dcount function appears to be working OK, because when the barcode entered does exist in the table "barcode name", the sub ends as it should, and when the entered barcode does not match, I do in fact get the desired message in the text box ("I could not recognize..."), but I also get the validation error on top.

I don't know if that helps clarify the problem.

 

[TheAceMan1]

ramzoid . . . . .

Give an example of a barcode that passes and one that fails.

Also quote the actual error message & number if you can.

See Ya! . . . . . .

 

[ramzoid]

OK. I think you'll see there's not any real difference in format between passing and failing barcodes; it's just a matter of whether that particular number is in the database.

Pass: 9969, 97300, 129310
Fail: 9970, 97301, 129410

"The value in the field or record violates the validation rule for the record or field. For example, you may have changed a validation rule without verifying whether the existing data matches the new validation rule. Click Undo on the Edit menu to restore the previous value, or enter a new value that meets the valiadation rule for the field."

No error number is shown.

Using Access 2002 SP3.

Thanks!

 

[RoyVidar]

The message you're getting, is a form error, because you have a validation rule in the field/control.

If the behaviour is OK without the validation rule, would it be something to consider just removing the validation rule?

To get rid of the inbuilt message, you can use the form error event, I don't remember which DataErr it is, but for starters, just place a MsgBox DataErr within the routine, then:

[tt]private sub form_error(dataerr as integer, response as integer)
if dataerr= <the number you found> then
response = acdataerrcontinue ' removes default message
end if
end sub[/tt]

Note - this is not validation, just removing the default message.

Roy-Vidar

 

[TheAceMan1]

ramzoid . . . . .

Just a qualifier . . . . yous showing three seperate numbers that pass & 3 that fail . . . Yes?

The error certainly gives indication of a validation rule. You sure there's no rule in the form or the table?

See Ya! . . . . . .

 

[ramzoid]

As far as I can tell, there is no actual "validation rule" per se, neither on the text box on the form, nor on the underlying fields in the table. I have been assuming that the validation error comes from the code for the BeforeUpdate event; I don't really know, though, if Access considers this to be "validation" or not.

Yes, the example barcodes were 3 different passing ones and 3 failing ones.

The "validation" (if that's really what we should call it) of the input is necessary. If the barcode is recognized, all is well, but if the barcode is not recognized, then the user needs to take action (sign in manually on paper instead of electronically by scanning an ID card).

I will try to see if I can get the form error / data error sub to work. Just killing the canned error message would be fine for me, even if it's not really proper "validation". I don't totally understand the code yet, or how I will find the error number, since none displays, but I will tinker with it and see if I can figure it out. I may be missing something pretty basic; I'm just a beginner at all this.

Thanks to both of you!

 

[ramzoid]

I get it! In the form properties, for the On Error event, I put the MsgBox DataErr line, and this gives me the error number. Then I add the other code with the error number plugged in, and it kills that particular error message. Excellent! It works. (I still don't know why I was getting the error in the first place, but this'll do just fine.)

The actual code for the Form OnError event, then, is:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2116 Then
    Response = acDataErrContinue ' removes default message
  End If
End Sub

Thanks so much to both of you for your help!