I have a problem that I can't figure out. I have a database application
to keep track of boxes that contain files. For data entry, I have a
form with a main form section for the box-specific data (box
description etc), and a subform for entering details on each file
that's in the box (type, subtype etc). The subform is in a datasheet
form, with one record on each row.
Some of the subform fields are filled in using drop-downs, which have a
query on underlying tables as recordsource. Some of the fields (not
all) in the subform are required, ie Null is not allowed.
I want to check that all these required fields are filled in by the
user when he/she moves on to the next record in the subdatasheet. I
can't figure out how to do this. I have tried using the OnCurrent event
for the subform, cloning the current recordset, going back to the
previous record, and checking the values of these fields. But this
doesn't seem to work, it gets stuck in a loop betwen the last record
and the current record. Here's some code:
---
>From subform code:Public currfile As Long
Public lastfile As Long
Public currbox As Long
Public lastbox As Long
Sub UpdateCurrentFile()
If Not IsNull(Me.FileID) And currfile <Me.FileID And currfile <0
Then
lastfile = currfile
End If
If Not IsNull(Me.FileID) Then currfile = Me.FileID
If Not IsNull(Me.BoxID) And currbox <Me.BoxID And currbox <0 Then
lastbox = currbox
End If
If Not IsNull(Me.BoxID) Then currbox = Me.BoxID
End Sub
---
Private Sub Form_Current()
Dim strSQL As String
Dim choice As Integer
Dim rst As Recordset
UpdateCurrentFile
If lastfile <0 Then
Set rst = Me.RecordsetClone
rst.FindFirst "FileID=" & lastfile
Me.Bookmark = rst.Bookmark
If IsNull(Me.DocType) Then
MsgBox "DocType is required. Please select from drop-down box."
Me.DocType.SetFocus
Me.DocType.Dropdown
ElseIf IsNull(Me.MeetingType) And Me.DocType <"Election" Then
MsgBox "MeetingType is required for this DocType. Please select
from drop-down box."
Me.MeetingType.SetFocus
Me.MeetingType.Dropdown
ElseIf IsNull(Me.SubType) Then
MsgBox "SubType is required. Please select from drop-down box."
Me.SubType.SetFocus
Me.SubType.Dropdown
ElseIf IsNull(Me.DocumentDate) Then
MsgBox "DocumentDate is required. Please select from drop-down
box."
Me.DocumentDate.SetFocus
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
rst.Close
Set rst = Nothing
Me.Refresh
End If
End Sub
I am using Access 2003, so the Access 2002 On Record Exit doesn't seem
to be avaiable. Any ideas?
Thanks for your help.
/Per
