Continuous Forms as Subforms

[John S]

John Here, from "Savebutton madness". A few people suggested that I try a
bound, continuous forms instead of an unbound datasheets for data in a
subform. My goal was to to get fairly bulletproof data validation. I've been
trying to arrange data validation in the continuous form, using various
events, but with very little success. The continuous form is bound to a
table.

For example, when I use the "before_update" event on a control (a text box),
the event keeps triggering even after I fix an error. And when I use an
"exit" event, the database (and not the event) captures any attempt to put a
letter in a numeric field.

Has anyone any experience doing validation on bound continuous forms used in
subforms. Suggestions? Tips (like what is the best event to work with)?
References that might be helpful?

John S
Aylmer, PQ

 

[Rick Brandt]

John Here, from "Savebutton madness". A few people suggested that I try a
bound, continuous forms instead of an unbound datasheets for data in a
subform. My goal was to to get fairly bulletproof data validation. I've been
trying to arrange data validation in the continuous form, using various
events, but with very little success. The continuous form is bound to a
table.

For example, when I use the "before_update" event on a control (a text box),
the event keeps triggering even after I fix an error. And when I use an
"exit" event, the database (and not the event) captures any attempt to put a
letter in a numeric field.

Has anyone any experience doing validation on bound continuous forms used in
subforms. Suggestions? Tips (like what is the best event to work with)?
References that might be helpful?

If you want validation "per control" then the BeforeUpdate event of the control
is the best place. If you want to validate "per record" then the BeforeUpdate
event of the form is the best place. I don't understand your statement about
the event still firing after you fix the error. Perhaps if you posted your
code? For example the code below would prevent an entry in a TextBox named
ValidationTest that was greater than 100.

Private Sub ValidationTest_BeforeUpdate(Cancel As Integer)

If Me!ValidationTest > 100 Then
MsgBox "Invalid Entry"
Cancel = True
End If

End Sub

 

[John S]

Rick,

Could be me, but here is the offending code. What happens is that once an
error is made, it will not accept a correction (it keeps saying the wrong
letter has been used)..

If IsNull(txtType.Value) Or Len(txtType <> 1) Then
MsgBox "Vous devez mettre seulement[D] (Chien) ou [C] (CHAT)"
txtType.Value = ""
txtType.SetFocus
Cancel = True

End If

If UCase(txtType.Value) = "C" Or UCase(txtType.Value) = "D" Then
Exit Sub
Else:
MsgBox "Seulement 'C'[ chat] or 'D'[chien] SVP"
txtType.Value = ""
txtType.SetFocus
Cancel = True
End If

Tried variations such as removing setfocus and setting the value to null,
and moving around the "exit sub" to no effect. Both if statements are
failing (I tried removing the second).

John S
Aylmer, PQ

 

[OzPete]

I just love that MsgBox error message!!!
I am going to unashamedly steal that one...
twisted sense of humour I know....
but I can just see the Aussies trying to work THAT one out!

OzPete
[self-inflicted exiled Englishman]

in message...

 

[Rick Brandt]

Rick,

Could be me, but here is the offending code. What happens is that once an
error is made, it will not accept a correction (it keeps saying the wrong
letter has been used)..

If IsNull(txtType.Value) Or Len(txtType <> 1) Then
MsgBox "Vous devez mettre seulement[D] (Chien) ou [C] (CHAT)"
txtType.Value = ""
txtType.SetFocus
Cancel = True

End If

If you use BeforeUpdate you don't need to set focus back to the control or
change its value (neither of which will work anyway.) Cancel = True will cancel
the update which means the control will never lose focus in the first place.
You can then either leave the value alone and let the user change it or use the
Undo method to "undo" their entry. The former is more standard.

Len(txtType <> 1) is incorrect syntax. The closing parenthesis should be after
the control name.

 

[John S]

Rick

Sigh. How embarrassing (didn't see it) and jumped to conclusions. Thanks.

John S
Aylmer, PQ

Rick,

Could be me, but here is the offending code. What happens is that once an
error is made, it will not accept a correction (it keeps saying the wrong
letter has been used)..

If IsNull(txtType.Value) Or Len(txtType <> 1) Then
MsgBox "Vous devez mettre seulement[D] (Chien) ou [C] (CHAT)"
txtType.Value = ""
txtType.SetFocus
Cancel = True

End If

If you use BeforeUpdate you don't need to set focus back to the control or
change its value (neither of which will work anyway.) Cancel = True will cancel
the update which means the control will never lose focus in the first place.
You can then either leave the value alone and let the user change it or use the
Undo method to "undo" their entry. The former is more standard.

Len(txtType <> 1) is incorrect syntax. The closing parenthesis should be after
the control name.