Validation code 1

[Wrathchild]

Instead of using the validation on the field's properties, I'm trying to write code. The validation is no problem, it's what happens after...the focus will go to the next item in the tab order. I'm trying to stop it from leaving the field until the validation is satisfied, but can't seem to get it. Below is what I have (it's on a subform); I've tried it on AfterUpdate, LostFocus, and On Exit with no luck. I know the setfocus code works because I use it in a different instance on a form open, it just won't work here.

Private Sub num_dy_wrkwk_AfterUpdate()
If Me.num_dy_wrkwk > 7 Or Me.num_dy_wrkwk < 1 Then
MsgBox &quot;Please enter a number between 1 and 7.&quot;, vbOKOnly, &quot;INCORRECT ENTRY&quot;
Forms!switchboard!Window2.SetFocus
Forms!switchboard!Window2.Form![num_dy_wrkwk].SetFocus
End If
End Sub

 

[gol4]

use the before update
Private Sub num_dy_wrkwk_beforeUpdate(cancel)
If Me.num_dy_wrkwk > 7 Or Me.num_dy_wrkwk < 1 Then
MsgBox &quot;Please enter a number between 1 and 7.&quot;, vbOKOnly, &quot;INCORRECT ENTRY&quot;
cancel = true
End If
End Sub

 

[Wrathchild]

perfect, thanks!

 

[gwog]

I have a similiar problem.

Here is the code i have on the field that I want to require data before user can continue:

Private Sub Comments_BeforeUpdate(Cancel As Integer)
If Me![ReasonCode] = &quot;900 - Other&quot; Then
If IsNull(Me![comments]) Then
MsgBox &quot;Comments must be entered when using 900 - OTHER code&quot;, vbOKOnly, &quot;Please Enter Comments&quot;
Cancel = True
End If
End If

Basically there are two fields - ReasonCode which is a combo box, and comments which is a memo field.

If a condiiton (900 - other) is selected in combo box I want the cursor to not move from the comments of that line until a value is entered.

Any idea why it isn't working?

It does nothing - the curson goes to next field and no message is displayed?

Thanks.

 

[randy700]

Your code is probably working correctly because your text box might not actually contain a null value. Try this:

if isnull(me!comments) OR me!comments = "" then

 

[RoyVidar]

Guessing that the value in the combobox control is not &quot;900 - Other&quot; (assuming ordinary combo thingie with one bound column thats not containing the text).

Test it with using the line

[tt]MsgBox Me!ReasonCode.Value[/tt]

before the test. Then amend the test accordingly

But - if the user does not enter any value in this control, then there's nothing for the before update event to perform. In other words, before update will only work if the user enters a value, if they hit tab, nothing happens.

You might then perhaps consider the lost focus, exit event. Should you need to set the focus back, just set the focus to another control first, cause you can't set the focus to a control that has the focus, as the comments control has when executing one of its events.

Roy-Vidar

 

[gwog]

Thanks for the responses.

Roy-Vidar what you say about the user not entering anything in the comments field makes sense. that is what I'm trying to prevent. So I have put the following code in my comments lost focus property:

Private Sub Comments_LostFocus()
If Me![ReasonCode] = &quot;900 - Other&quot; Then
If IsNull(Me![comments]) Then
MsgBox &quot;Comments must be entered when using 900 - OTHER code&quot;
Me.ReasonCode.SetFocus
Me.comments.SetFocus
End If
End If

There is definitely value of &quot;900 - Other&quot; in the reasoncode field. It pops up the message - but moves the cursor to the next record.

Thus the user can simply continue without entering a comment.

I need it to require that they enter a comment.

thanks.

 

[gwog]

I'm willing to try another approach if anyone knows how to do this.

when 900 - Other is selected - it pops up a dialog box for them to enter comments into. It won't let them close the dialog box until something is entered.

just try to look for any way to accomplish what I need.

thanks.

 

[RoyVidar]

The above works in my setup, so I don't know what's happening.

Couple of guesses/suggestions:
- check if any other events fires (use breakpoints in the first executable line (F9) and step thru each line (F8) to see what happens (event collision/recursion)
- Access does sometime demand that the control names on a form differs from the field names. Try prefixing the controls to change their names (text controls txt..., combos cbo... or something along those lines) - will lead to an amendment of the code too, but... (I alwasy rename the controls I'm going to manipulate thru code, using the naming convention mentioned)

If that too fails, look into the before update event of the form. This can also be cancelled - but you'll need to setfocus. This event fires whenever an attempt is made to save the record.

Roy-Vidar

 

[Davidwazza]

Sorry everyone -> a quick interlude in this discussion. I was just wondering what the central difference was between BeforeUpdate and AfterUpdate. Ive always used AfterUpdate myself, but the more I think about it, the only thing I can think of is berfore you update similar to setting focus.
Hence, where would you one over another.

Thanx, David.

 

[gol4]

Private Sub test_AfterUpdate()
End Sub

Private Sub test_BeforeUpdate(Cancel As Integer)
End Sub

The difference is you get the cancel opportunity before updating

 

[gwog]

Thanks for all your help.

I figured out how to use a inputbox to require the user to enter additional comments.

Here is the code I put in the after update property of the combobox.

Private Sub ReasonCode_AfterUpdate()
If Me![ReasonCode] = &quot;900 - Other&quot; Then
If IsNull(Me.comments) Then
Me![comments] = InputBox(&quot;Please enter Additional Comments&quot;, &quot;Additional Comments Required&quot
End If
End If
End Sub

When 900 - Other is selected it pops up an input box, and if the user justs hits cancel and then trys to move to next record - it gives error that comments can't be 0 length.

Yeah!

Lisa.

 

[Edski]

RoyVidar

- Access does sometime demand that the control names on a form differs from the field names.

Really? Could you elaborate please?

Lisa,

comments can't be 0 length

You didn't put that in the table design, did you? (i.e. Required = Yes or Allow Zero length = No).

 

[gwog]

EDSKI,

Here are the properties of my comments field:

comments Memo -
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Ordinal Position: 18
Required: False
Source Field: comments
Source Table: MASTER ABS TABLE

 

[RoyVidar]

gwog:
Found any solution yet?
Did you try Edskis suggestion on the AllowZeroLength? (set it to True/Yes)

But again, this does not really require an input here. The user will be able to jump to the next control just by hitting cancel in the input box, and continue tabbing. But if this is sufficient for your needs, then great.

I often use the before update event of the form (as mentioned) something like this might perhaps work in your setup (used another test too)?

[tt]Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![ReasonCode] = &quot;900 - Other&quot; Then
If Len(Me![comments] &amp; vbNullString) Then
MsgBox &quot;Comments must be entered when using 900 - OTHER code&quot;
Me![comments].SetFocus
Cancel = True
End If
End If
End Sub[/tt]

This will perform validation when an attempt to save the record is made... Try it out if you like...

Edski:

http://www.dbforums.com/t806329.html

http://www.msaccessforum.com/t62756-s

You should also be able to find threads here concerning the matter, and there's also coverage in reference books.

Roy-Vidar

 

[gwog]

Royvidr,

Yes, I used an inputbox and it is now working wonderfully.

thanks for all your suggestions.

Lisa.

 

[Edski]

Roy,
Those links just say the same thing you posted! They don't mention WHY or in WHAT situations having the same control name as the field name is a bad idea. I'm yet to be convinced, and until someone tells me why, I will continue to give my control names the same name as their source. In ten years I have never experienced any problems. Maybe because I use such good names for my table fields (no spaces etc)
Cheers!