Validate in sub-form all inputs are good before an insert can happen

[Guest]

Hi

I have a sub-form within the main form. The sub-form has a combo-box for Activity list and a text box for time input and this allows users to enter multiple rows of Activity/Time

Both the Activity and Time inputs are mandatory

I tried using the BeforeUpdate event to trap user input and then do a setfocus on the combo box and this resulted in this error "run-time error 2108". I read about that the setfocus() will not work with a BeforeUpdate event

how do I validate users have entered both the Activity and the Time before they are allowed to enter the next set of Activity/Time ?

Appreciate your helping..

 

[Allen Browne]

The simplest solution would be to open the subform's Table in design view,
and set the Required property (lower pane) to Yes for both fields. That way
there is no way the record can be saved without values for both fields.

You could also use the BeforeUpdate event of the *form* (not the controls):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as string

If IsNull(Me.[Activity]) Then
Cancel = True
strMsg = strMsg & "Activity required." & vbCrLf
End If
If IsNull(Me.[Time]) Then
Cancel = True
strMsg = strMsg & "Time required." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & "Correct the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, "Invalid data."
End If
End Sub


Notes
1. If you have a required field where an entry has been started and
backspaced out, you will not be able to SetFocus to another field (unless
you supply an entry, or undo (press Esc)).

2. If your field really is called "Time", consider renaming it. Time is a
reserved word in VBA (for the system time), so it may be ambiguous in your
code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a sub-form within the main form. The sub-form has a combo-box for

Activity list and a text box for time input and this allows users to enter
multiple rows of Activity/Time.

Both the Activity and Time inputs are mandatory.

I tried using the BeforeUpdate event to trap user input and then do a

setfocus on the combo box and this resulted in this error "run-time error
2108". I read about that the setfocus() will not work with a BeforeUpdate
event.

how do I validate users have entered both the Activity and the Time before

they are allowed to enter the next set of Activity/Time ??

 

[Guest]

Thank you so much... It works so beautiful ....

I had spent so much time last nite web surfing for good answers but unable to find the solution - many mentioned the problem but none talked about solutions !



----- Allen Browne wrote: -----

The simplest solution would be to open the subform's Table in design view,
and set the Required property (lower pane) to Yes for both fields. That way
there is no way the record can be saved without values for both fields.

You could also use the BeforeUpdate event of the *form* (not the controls):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as string

If IsNull(Me.[Activity]) Then
Cancel = True
strMsg = strMsg & "Activity required." & vbCrLf
End If
If IsNull(Me.[Time]) Then
Cancel = True
strMsg = strMsg & "Time required." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & "Correct the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, "Invalid data."
End If
End Sub


Notes
1. If you have a required field where an entry has been started and
backspaced out, you will not be able to SetFocus to another field (unless
you supply an entry, or undo (press Esc)).

2. If your field really is called "Time", consider renaming it. Time is a
reserved word in VBA (for the system time), so it may be ambiguous in your
code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

Activity list and a text box for time input and this allows users to enter
multiple rows of Activity/Time.setfocus on the combo box and this resulted in this error "run-time error
2108". I read about that the setfocus() will not work with a BeforeUpdate
event.