VBA Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - I wonder if someone would be able to help with the
following:-

I have a form which contains a sub- form and I want to
ensure that the user completes all mandatory fields. When
I use VBA to do this, checking the main form is not a
problem however it doesn't recognise the sub form.

What code do I need to get the 'focus' shifted to the sub
form?

Thanks for your help.

Ian
 
There is a timing issue in your question.

Access saves the main form record before focus moves into the subform. Then
it saves the subform record before you move focus back to the main form.
That means you have to validate the two separately.

The BeforeUpdate event of the Form is the only reliable way to write code to
ensure the fields are filled in. Example:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.SomeField) Then
strMsg = strMsg & "SomeField required." & vbCrLf
End If
If IsNull(Me.AnotherField) Then
strMsg = strMsg & "AnotherField required." & vbCrLf
End If
'etc for other fields.
If Cancel Then
MsgBox strMsg, vbExclamation, "Invalid data"
End If
End Sub

There is an even easier way though, and it requires no code. Open your table
in Design view, and set the Required property to Yes for the mandatory
fields. Now Access won't let the record be saved if the field is null.

(For Text fields, set AllowZeroLength to No as well so you do not end up
with a zero-length-string in the field.)
 
Back
Top