cannot open subform to specific control

[Stephen]

I posted a question earlier and Sandra Daigle answered. However, the
solution still did not work and I have been unable to post a threaded reply,
so I aplogize for this "new posting".

I have been trying to open a form with a continuous subform and then move to
the specified record on the subform. The code on the subform is setup to
highlight the current record's row. The following code ALMOST works. I put
an error msg in the code to indicate whether or not the desired record was
found. I get the following situation:

From the source form, I select the desired record and the "on click" event
runs. It opens the target form with the subform. The starget subform is
correctly populated with the desired subset of records, however the code
CANNOT find the desired record. The desired record DOES EXIST in teh
subform recordset, however the code returns the "not found" msg. Here is
the strange part: Now that the target for IS open with the correct subform
recordset, I run teh code again (click the source form record) . The
difference here, is that the target subform is already open and populated
with the desired recordset. This times it works and the current record of
the subform is moved to the desired record.

Other observances:
The data is organized similar to a student with grades. The source form
will have a list of all grades for an entire class. Whne I click on the
grade, the target form will open ti the profile of teh chosen student, and
the subform (list of classes taken by the student) will move to the class in
which the chosen grade was assigned.

In my scenario, if I choose a grade on the source form that belongs to the
SAME student, then the code works. If I choose a grade that belongs to a
difference student (hence although teh target form and subform are open, the
target subform must be re-populated with the recordset of classes taken by
the new student), then the code fails again.

I have walked through this code with the F8 step process and oddly IT WORKS
when stepping. However, it only gets this problem when running normally.
To reiterate, the code works only if the subform is currently OPEN and
populated with the desired recordset. If the code has to re-populate the
subform, then it fails.

-Help..


Code:

Private Sub Description_Click()
Dim sfrm As Form
Dim SearchRecord As DAO.Recordset

Call OpenMenuForm(Me.OrderID, Me.MenuType, Me.OrderItemID)

Set sfrm =
Forms![frmMenuItemSelection]![frmMenuItemSelectionSubformList].Form
Set SearchRecord = sfrm.RecordsetClone
SearchRecord.FindFirst "[GenItemID] = " & Me.GenItemID
If SearchRecord.NoMatch Then
MsgBox "Not found"
Else
sfrm.Bookmark = SearchRecord.Bookmark
End If
Set sfrm = Nothing
SearchRecord.Close

End Sub


Function OpenMenuForm(OrderID As Integer, MenuID As Integer, Optional
OrderItemID)

DoCmd.OpenForm "frmMenuItemSelection"
Forms!frmMenuItemSelection.MenuID = MenuID
Forms!frmMenuItemSelection.OrderID = OrderID
Forms!frmMenuItemSelection.OrderItemID= OrderItemID

End Function

 

[Kelvin]

The only thing I can see is that you first use the Call procedure to open
your form with some values set. Your subform will then be linked to this
info. You then have "SearchRecord.FindFirst "[GenItemID] = " &
Me.GenItemID". However, after you opened the form, the calling form, the
one that has GenItemID no longer has the focus. Therefore, Me.GenItemID
won't be valid. I would change this from Me.GenItemID to
Forms!CallingForm.GenItemID.

Kelvin

I posted a question earlier and Sandra Daigle answered. However, the
solution still did not work and I have been unable to post a threaded reply,
so I aplogize for this "new posting".

I have been trying to open a form with a continuous subform and then move to
the specified record on the subform. The code on the subform is setup to
highlight the current record's row. The following code ALMOST works. I put
an error msg in the code to indicate whether or not the desired record was
found. I get the following situation:

From the source form, I select the desired record and the "on click" event
runs. It opens the target form with the subform. The starget subform is
correctly populated with the desired subset of records, however the code
CANNOT find the desired record. The desired record DOES EXIST in teh
subform recordset, however the code returns the "not found" msg. Here is
the strange part: Now that the target for IS open with the correct subform
recordset, I run teh code again (click the source form record) . The
difference here, is that the target subform is already open and populated
with the desired recordset. This times it works and the current record of
the subform is moved to the desired record.

Other observances:
The data is organized similar to a student with grades. The source form
will have a list of all grades for an entire class. Whne I click on the
grade, the target form will open ti the profile of teh chosen student, and
the subform (list of classes taken by the student) will move to the class in
which the chosen grade was assigned.

In my scenario, if I choose a grade on the source form that belongs to the
SAME student, then the code works. If I choose a grade that belongs to a
difference student (hence although teh target form and subform are open, the
target subform must be re-populated with the recordset of classes taken by
the new student), then the code fails again.

I have walked through this code with the F8 step process and oddly IT WORKS
when stepping. However, it only gets this problem when running normally.
To reiterate, the code works only if the subform is currently OPEN and
populated with the desired recordset. If the code has to re-populate the
subform, then it fails.

-Help..


Code:

Private Sub Description_Click()
Dim sfrm As Form
Dim SearchRecord As DAO.Recordset

Call OpenMenuForm(Me.OrderID, Me.MenuType, Me.OrderItemID)

Set sfrm =
Forms![frmMenuItemSelection]![frmMenuItemSelectionSubformList].Form
Set SearchRecord = sfrm.RecordsetClone
SearchRecord.FindFirst "[GenItemID] = " & Me.GenItemID
If SearchRecord.NoMatch Then
MsgBox "Not found"
Else
sfrm.Bookmark = SearchRecord.Bookmark
End If
Set sfrm = Nothing
SearchRecord.Close

End Sub


Function OpenMenuForm(OrderID As Integer, MenuID As Integer, Optional
OrderItemID)

DoCmd.OpenForm "frmMenuItemSelection"
Forms!frmMenuItemSelection.MenuID = MenuID
Forms!frmMenuItemSelection.OrderID = OrderID
Forms!frmMenuItemSelection.OrderItemID= OrderItemID

End Function

 

[Wayne Gillespie]

I suspect it is a timing issue on when the subform is actually updated and when the recordset is created based on the recordsetclone.

When a form containing a subform is opened, the subform opens first before the main form. At this point if the subform has LinkChild and LinkMaster
properties set to a field on the main form, these values do not yet exist so the recordsetclone of the subform returns no records.
The mainform then opens and the link fields are set and the subform requeries. So by the time the main form is visible the subform displays the
required records BUT the subform has actually been requeried twice in this process, the first time returning no records.

Access will often place a lower priority on secondary processes such as refreshing the subform while other code is currently executing. Therefore I
suspect that at the point you create the recordset, the subform has not yet requeried and so the recordset is created on the intial recordsetclone of
the subform which returns no records. Therefore the search fails.

Try forcing the subform to requery before creating the recordset by placing DoEvents on the preceding line. This will force Access to complete all
pending processes before continuing.

Call OpenMenuForm(Me.OrderID, Me.MenuType, Me.OrderItemID)

Set sfrm =
Forms![frmMenuItemSelection]![frmMenuItemSelectionSubformList].Form

DoEvents <============

Set SearchRecord = sfrm.RecordsetClone

I posted a question earlier and Sandra Daigle answered. However, the
solution still did not work and I have been unable to post a threaded reply,
so I aplogize for this "new posting".

I have been trying to open a form with a continuous subform and then move to
the specified record on the subform. The code on the subform is setup to
highlight the current record's row. The following code ALMOST works. I put
an error msg in the code to indicate whether or not the desired record was
found. I get the following situation:

From the source form, I select the desired record and the "on click" event
runs. It opens the target form with the subform. The starget subform is
correctly populated with the desired subset of records, however the code
CANNOT find the desired record. The desired record DOES EXIST in teh
subform recordset, however the code returns the "not found" msg. Here is
the strange part: Now that the target for IS open with the correct subform
recordset, I run teh code again (click the source form record) . The
difference here, is that the target subform is already open and populated
with the desired recordset. This times it works and the current record of
the subform is moved to the desired record.

Other observances:
The data is organized similar to a student with grades. The source form
will have a list of all grades for an entire class. Whne I click on the
grade, the target form will open ti the profile of teh chosen student, and
the subform (list of classes taken by the student) will move to the class in
which the chosen grade was assigned.

In my scenario, if I choose a grade on the source form that belongs to the
SAME student, then the code works. If I choose a grade that belongs to a
difference student (hence although teh target form and subform are open, the
target subform must be re-populated with the recordset of classes taken by
the new student), then the code fails again.

I have walked through this code with the F8 step process and oddly IT WORKS
when stepping. However, it only gets this problem when running normally.
To reiterate, the code works only if the subform is currently OPEN and
populated with the desired recordset. If the code has to re-populate the
subform, then it fails.

-Help..


Code:

Private Sub Description_Click()
Dim sfrm As Form
Dim SearchRecord As DAO.Recordset

Call OpenMenuForm(Me.OrderID, Me.MenuType, Me.OrderItemID)

Set sfrm =
Forms![frmMenuItemSelection]![frmMenuItemSelectionSubformList].Form
Set SearchRecord = sfrm.RecordsetClone
SearchRecord.FindFirst "[GenItemID] = " & Me.GenItemID
If SearchRecord.NoMatch Then
MsgBox "Not found"
Else
sfrm.Bookmark = SearchRecord.Bookmark
End If
Set sfrm = Nothing
SearchRecord.Close

End Sub


Function OpenMenuForm(OrderID As Integer, MenuID As Integer, Optional
OrderItemID)

DoCmd.OpenForm "frmMenuItemSelection"
Forms!frmMenuItemSelection.MenuID = MenuID
Forms!frmMenuItemSelection.OrderID = OrderID
Forms!frmMenuItemSelection.OrderItemID= OrderItemID

End Function

Wayne Gillespie
Gosford NSW Australia