Form clearing Error (1 Viewer)

[-ian-]

I am new to Access2000 (5days) and have a problem with field clearing. I have looked through your postings throughout this time and have found them a great asset. I now have a problem I have not found a working solution for:

I have a form field which checks via the BeforeUpdate code to see if a number is within a certain range (and therefore valid or not)

When I find an invalid entry, I try to clear it by setting the field to Null.

When I execute the code, I get the error:
"The Macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving data in this field"

I assume this means that the field is locked for the duration of the BeforeUpdate subroutine? How can I circumvent this?

(Using Me.Undo isn't complete enough as it doesn't guarentee field clearing)

What am I doing wrong?

 

[David R]

You could set a validation rule on the field itself's BeforeUpdate to check right then that the value is correct. Cancel the update and send them back to the field to try again if the value is not correct.

If you need it to be form level (say not all fields are filled in yet that determine validity), then try posting your code here and maybe someone will spot what's wrong.

HTH,
David R

 

[-ian-]

Validation won't work in this case. So, Ok -Here's the code. I thought of streamlining it in the string section, but I thought the newline trick might be of use to others out there.

Private Sub Part1_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Dim bit As Variant
Dim NewLine As String

NewLine = Chr(13) + Chr(10)

If Me.Part1 > 0 Then 'This is to prevent a Null coming through into routine when the [part1] field is empty

If DCount("[part1]", "ProductInfo", "[ProductID]=" & [Part1]) = 1 Then
bit = DLookup("[ProductDescription]", "ProductInfo", "[ProductID]=[part1]")
Answer = MsgBox("Product Description: " & bit & NewLine & NewLine & "Do you wish to Accept Part?", vbYesNo, "Part Confirmation")

If Answer = vbNo Then
Part1 = Null 'reset field
Cancel = True
End If
Exit Sub

End If

MsgBox ("Part Number Does not Exist!")
Cancel = True
End If

End Sub

 

[boblarson]

First, I'm not sure if this is the problem, but it would appear that you are setting the property to null but then cancelling the operation (cancel=true) right afterwards. Have you tried doing the cancel=true before setting the Part1 field to null?

Second, why go through the hassle of setting up the variable NewLine when using vbCrLf will do the same thing and then you don't have to define the variable everytime you want to use it. vbCrLf (visual basic Carriage return Line feed is a built-in vb constant).


BL
hth

 

[Rich]

Msg box is a bit convoluted use this instead of NewLine & vbCrLf

 

[-ian-]

Thanks for the vbcrlf tip.

I get the error either way I put the cancel=true line.

Here is stripped version of my code that gives the same problem....

Private Sub Part1_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant

Answer = MsgBox("Cancel Entry?", vbYesNo, "Our Box")

If Answer = vbYes Then
'Cancel = True
Part1 = Null 'reset field
Cancel = True
End If
Exit Sub

End Sub

I think I must have a really basic misunderstanding of how the BeforeUpdate events work. Isn't this the type of thing everyone wants to do??

Rich, is your post complete?

 

[-ian-]

I've tried a different approach by using the sendkeys statement...
It's a VERY sloppy fix though and I don't like it. But it works. Kind of.

If Answer = vbYes Then
Cancel = True
SendKeys "{backspace}{backspace}{backspace}{backspace}{backspace}{backspace}"
End If

Any better ideas out there?

 

[Rich]

Private Sub Part1_BeforeUpdate(Cancel As Integer)
Dim Answer As String

Answer = MsgBox("Cancel Entry?", vbYesNo, "Our Box")

If Answer = vbYes Then
Cancel = True
Me.Undo
End If
Exit Sub

End Sub

 

[-ian-]

The Me.undo statement is not thorough. It only pushes back to the last entry (which might not be blank).

I need to ensure a clean field.


This is frustrating -which I now think might be a way of life for access programmers. I looked up this problem on the Microsoft Website, and this error is only mentioned for Access2.0 and Access97 -thats where I grabbed the sendkeys solution from.

 

[Rich]

I don't understand If the last entry had an acceptable entry why is it not now?

 

[-ian-]

Good point.

If there is a previously valid entry, the only reason why the user will be changing it is because its a mistake. Once they get to THAT point, I didn't want them to have the option of returning to the previous entry by having the field cleared completely.

 

[-ian-]

I've played a bit more with my forms and have been testing them out, and i've come now to the conlcusion that Me.undo is actually perfect.

Sorry to have wasted your time out there. But thanks.

And a message to all using this forum: The info here is tremendous AND USEFULL. Without the data here, Access would have been a nightmare.