subform focus on field

[Guest]

I have a main form, with a subform in it. The subform pulls a recordset from
a table and displays it, blah blah blah.

When I close the main form, I have a specially designed close function for a
few extras, but I need to add one thing. I want to check if any of the
records in the set the subform pulls have a null value in a certain field
(the same field in eache record). If the field is null for any of the
records, I want to set the focus to that particular field in that particular
record, then end the close procedure (so the form won't be closed until that
field is filled in for all the records)

I've gotten this far:


Set rstBigSet = dbs.OpenRecordset("SELECT * FROM mytable
WHERE PKey = " & Me!PKey, dbOpenForwardOnly, dbSeeChanges)
While Not rstBigSet.EOF And Not rstBigSet.BOF
If IsNull(rstBigSet!Date) Then
MsgBox "You must enter a date!", vbInformation, "MISSING DATE"
Forms!frmBigForm!tabLittleForm.SetFocus
Forms!frmBigForm!tabLittleForm!Date.SetFocus '*********'
Exit Sub
End If
rstBigSet.MoveNext
Wend
rstBigSet.Close
dbs.Close

with everything initialized, of course. That code generates an error -
"Object doesn't support this property or method - which I assume the starred
line throws.
But it does set the focus to the first field of the first record on the
subForm. I need to replace the starred line with something else.

Is there a way to do this? Is it possible? I would think it is, but I can't
seem to find the work around yet.

Thanks,
~Yb

 

[Marshall Barton]

I have a main form, with a subform in it. The subform pulls a recordset from
a table and displays it, blah blah blah.

When I close the main form, I have a specially designed close function for a
few extras, but I need to add one thing. I want to check if any of the
records in the set the subform pulls have a null value in a certain field
(the same field in eache record). If the field is null for any of the
records, I want to set the focus to that particular field in that particular
record, then end the close procedure (so the form won't be closed until that
field is filled in for all the records)

I've gotten this far:

Set rstBigSet = dbs.OpenRecordset("SELECT * FROM mytable
WHERE PKey = " & Me!PKey, dbOpenForwardOnly, dbSeeChanges)
While Not rstBigSet.EOF And Not rstBigSet.BOF
If IsNull(rstBigSet!Date) Then
MsgBox "You must enter a date!", vbInformation, "MISSING DATE"
Forms!frmBigForm!tabLittleForm.SetFocus
Forms!frmBigForm!tabLittleForm!Date.SetFocus '*********'
Exit Sub
End If
rstBigSet.MoveNext
Wend
rstBigSet.Close
dbs.Close

with everything initialized, of course. That code generates an error -
"Object doesn't support this property or method - which I assume the starred
line throws.
But it does set the focus to the first field of the first record on the
subForm. I need to replace the starred line with something else.

I think the error might be coming from the abbreviated
referenceL Try using:
Forms!frmBigForm!tabLittleForm.FORM!Date.SetFocus

Note that Date is a reserved word and using it as a field or
control may confuse you and/or Access.

I think(?) you can make this a lot simpler if you use the
subform's recordset instead of opening a new recordset on
the entire table.

Private Sub Form_Unload(Cancel As Integer)
With Me!tabLittleForm.Form.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull(rstBigSet!Date) Then
MsgBox "You must enter a date!", _
vbInformation, "MISSING DATE"
Me!tabLittleForm.SetFocus
Me!tabLittleForm.Form!Date.SetFocus
Me!tabLittleForm.Form.Bookmark = .Bookmark
Cancel = True
Exit Sub
End If
rstBigSet.MoveNext
Loop
End With
End Sub

Note that I used the Unload event instead of the Close
event. Unlike the Close event, the Unload event can be
cancelled.