By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,604 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Ensuring non-null values in subdatasheet on record exit

P: n/a
Per
Hi,

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

Aug 18 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 18 Aug 2006 11:20:08 -0700, "Per" <pa**********@gmail.comwrote:

The proper event to perform the check is Form_BeforeUpdate of the
subform.
There is no need for a RecordsetClone; just access the values in the
various controls.

-Tom.

>Hi,

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
Aug 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.