Form Objects Not Recognized?

[Steven V. Olson]

Why does the Set statement not recognize the form objects?

The following subroutine closes an existing Form named
EyeglassPrescriptionForm and opens another form named
Patient Safety RX Order Input Form. A nested subform named
OpthalmicDoctorPrescriptionForm is opened with the main
form, however the subform records are initialized to the
first record, which is not desired.

To solve this problem, a DAO RecordsetClone is created and
Set to match the subform records. However, the form object
is not being recogized in the Set statement. What am I
doing wrong?

Steve Olson

Private Sub Command60_Click()
On Error GoTo Err_Command60_Click

Dim rst As DAO.Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Dim intEyeglassPrescriptionID As Integer

intEyeglassPrescriptionID =
Me.EyeglassPrescriptionForm.Form.EyeglassPrescriptionID

stDocName = "Patient Safety RX Order Input Form"

stLinkCriteria = "[PersonalInformationID]=" & Me!
[PersonalInformationID]

DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria

'Sets Recordset to [Patient Safety RX Order Input
Form].OpthalmicDoctorPrescriptionForm subform
Set rst =
Me.OpthalmicDoctorPrescriptionForm.Form.RecordsetClone
rst.FindFirst "[EyeglassPrescriptionID] =" &
intEyeglassPrescriptionID

If rst.NoMatch Then
MsgBox "No match was found. Something is
wrong!"
Else

Me.OpthalmicDoctorPrescriptionForm.Form.Bookmark =
rst.Bookmark
End If
rst.Close
Set rst = Nothing

Exit_Command60_Click:
Exit Sub

Err_Command60_Click:
MsgBox Err.Description
Resume Exit_Command60_Click

End Sub

 

[Albert D. Kallal]

I would try removing your close command. You are executing a close command
in the code you are running, and I don't think that is such a good idea. I
would perhaps have the close command as the last line of the code. Also,
while running that code, if there is a focus change, then you might wind up
closing the wrong form, so, use:

DoCmd.Close acForm, Me.Name

The set statement you have look ok, and should work. The only thing I can
think of is that the form is not opened, or perhaps you have the wrong form
name, or perhaps the sub-form control you are reference is wrong.

I would also as a general rule avoid spaces in field names in tables. A lot
of databases don't support spaces in field names , and thus moving your data
in, or out of ms-access can be come very difficult. Even Microsoft's own sql
server does not support spaces in field names. To be fair, in your example
you don't show any spaces in field names, but it does seem you like to use
spaces in your object names, and this habit might come back to bite you.

 

[Steven V. Olson]

Actually, I need to execute the close command first, so I
can do work on another form. I'm wondering if switching to
another form in the middle of the code affects recognition
of the recordset.

Steve