return a row in a continuous sub-form to edit (pencil icon)

[Rick Allison]

I have a form, with a sub-form. The sub-form is a continuous form.
All fields on the sub-form must be populated. Failure to populate
one or more fields will cause a message to be displayed, focus set to
the first missing field, and edit mode for the row.

I can do the message and the set focus but what is the command to
return the row to edit mode?

The sub-form is a bound form. I am using Access 2000.

Thanks much,

Rick

 

[Guest]

Try this:

1. Add this to the declarations section for the subform's module:

Dim CompleteFlag as Boolean

2. Add this sub, substituting a valid field name for <FieldName> and
repeating the If IsNull..Then loop for each field that needs to be complete.
Watch the word wrap on the Msgbox line.

Private Sub CompleteCheck()
CompleteFlag = False
If Not Form.NewRecord Or Me.Dirty Then 'do not run on blank/unchanged record
If IsNull(<FieldName>) Then
MsgBox "Please enter the <FieldName>.", vbExclamation, "Required
Information"
<FieldName>.SetFocus
Exit Sub
End If
End If
CompleteFlag = True
End Sub

3. Add this to the BeforeUpdate of the subform:

CompleteCheck
If Not CompleteFlag Then Cancel = True

Here is what happens: before the form is updated/saved, the code calls the
CompleteCheck Sub. If any field is null, the save is cancelled, and the focus
goes to the null field.

 

[Rick Allison]

Brian,

Thanks. It worked.

--
Rick Allison

Try this:

1. Add this to the declarations section for the subform's module:

Dim CompleteFlag as Boolean

2. Add this sub, substituting a valid field name for <FieldName> and
repeating the If IsNull..Then loop for each field that needs to be complete.
Watch the word wrap on the Msgbox line.

Private Sub CompleteCheck()
CompleteFlag = False
If Not Form.NewRecord Or Me.Dirty Then 'do not run on blank/unchanged record
If IsNull(<FieldName>) Then
MsgBox "Please enter the <FieldName>.", vbExclamation, "Required
Information"
<FieldName>.SetFocus
Exit Sub
End If
End If
CompleteFlag = True
End Sub

3. Add this to the BeforeUpdate of the subform:

CompleteCheck
If Not CompleteFlag Then Cancel = True

Here is what happens: before the form is updated/saved, the code calls the
CompleteCheck Sub. If any field is null, the save is cancelled, and the focus
goes to the null field.