Subform Navigation

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

Guest

I will have 25 records in a Subform.

I am using code to save to Unbound Tables via DAO. No problem.
BUT, I would like to examine 3 fields in each record to see if any
are Null as I save. I would then tell the User & give him / her the
Option of entering data or not.

I can use "Filter" in the Subform to show a Record at a time.
I would rather move from record-to-record in the 25 records
& examine the 3 fields then.

Any sample code would be appreciayed.

TIA - Bob
 
You can loop through the subform's form's recordsetclone and check the value
of each field:

Dim intTest As Integer
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
intTest = IsNull(.Fields("Field1Name").Value) + _
IsNull(.Fields("Field2Name").Value) + _
IsNull(.Fields("Field3Name").Value)
If intTest <> 0 Then
If vbYes = MsgBox("You have not entered values " & _
"into a record. Do you want to do this?", _
vbQuestion+vbYesNo, "Enter Data?") Then
Me.SubformControlName.Form.Bookmark = _
.Bookmark
Exit Sub
End If
End If
.MoveNext
Loop
End With
 
Ken - thank you.

Ken Snell said:
You can loop through the subform's form's recordsetclone and check the value
of each field:

Dim intTest As Integer
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
intTest = IsNull(.Fields("Field1Name").Value) + _
IsNull(.Fields("Field2Name").Value) + _
IsNull(.Fields("Field3Name").Value)
If intTest <> 0 Then
If vbYes = MsgBox("You have not entered values " & _
"into a record. Do you want to do this?", _
vbQuestion+vbYesNo, "Enter Data?") Then
Me.SubformControlName.Form.Bookmark = _
.Bookmark
Exit Sub
End If
End If
.MoveNext
Loop
End With
Click to expand...
 
Ken - How do I get the "Record Pointer" on the Left side
of the Subform to "update" as I move down the Subform?

TIA - Bob
 
If you want that visible effect, then you'll need to use the subform's
Recordset instead of the RecordsetClone, or cause the subform to move to
each record as you test the recordsetclone.

Option of using Recordset:
---------------------------
Dim intTest As Integer
With Me.SubformControlName.Form.Recordset
.MoveFirst
Do While .EOF = False
intTest = IsNull(.Fields("Field1Name").Value) + _
IsNull(.Fields("Field2Name").Value) + _
IsNull(.Fields("Field3Name").Value)
If intTest <> 0 Then
If vbYes = MsgBox("You have not entered values " & _
"into a record. Do you want to do this?", _
vbQuestion+vbYesNo, "Enter Data?") Then
Exit Sub
End If
End If
.MoveNext
Loop
End With



Option of moving Recordset as you test RecordsetClone:
---------------------------
Dim intTest As Integer
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
Me.SubformControlName.Form.Bookmark = _
.Bookmark
intTest = IsNull(.Fields("Field1Name").Value) + _
IsNull(.Fields("Field2Name").Value) + _
IsNull(.Fields("Field3Name").Value)
If intTest <> 0 Then
If vbYes = MsgBox("You have not entered values " & _
"into a record. Do you want to do this?", _
vbQuestion+vbYesNo, "Enter Data?") Then
Exit Sub
End If
End If
.MoveNext
Loop
End With



--

Ken Snell
<MS ACCESS MVP>
 
Ken - I will implement that.

Thank you - Bob

Ken Snell said:
If you want that visible effect, then you'll need to use the subform's
Recordset instead of the RecordsetClone, or cause the subform to move to
each record as you test the recordsetclone.

Option of using Recordset:
---------------------------
Dim intTest As Integer
With Me.SubformControlName.Form.Recordset
.MoveFirst
Do While .EOF = False
intTest = IsNull(.Fields("Field1Name").Value) + _
IsNull(.Fields("Field2Name").Value) + _
IsNull(.Fields("Field3Name").Value)
If intTest <> 0 Then
If vbYes = MsgBox("You have not entered values " & _
"into a record. Do you want to do this?", _
vbQuestion+vbYesNo, "Enter Data?") Then
Exit Sub
End If
End If
.MoveNext
Loop
End With



Option of moving Recordset as you test RecordsetClone:
---------------------------
Dim intTest As Integer
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
Me.SubformControlName.Form.Bookmark = _
.Bookmark
intTest = IsNull(.Fields("Field1Name").Value) + _
IsNull(.Fields("Field2Name").Value) + _
IsNull(.Fields("Field3Name").Value)
If intTest <> 0 Then
If vbYes = MsgBox("You have not entered values " & _
"into a record. Do you want to do this?", _
vbQuestion+vbYesNo, "Enter Data?") Then
Exit Sub
End If
End If
.MoveNext
Loop
End With
Click to expand...
 
Back
Top