Two Combo Boxes and Subform

[Rjc8513]

I have two combo boxes on a form. The user makes a selection from the first combo box (cbxFIND) and the subform on the form is updated via the following code:

Private Sub cbxFIND_AfterUpdate()
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "[FUNCTION NO] ='" & Left(Me![cbxFIND], 6) & "'"
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
Exit Sub

I would like to have the subform updated again (using the results of the above) based on the user's selection in the second combo box (cbxSEEK). I've tried to use the above code again but I can't seem to get it to work. Something like:

Private Sub cbxSEEK_AfterUpdate()
Dim rst As Recordset
Dim rstSub as Recordset
Set rst = Me.RecordsetClone
Set rstSub=rst.Clone
rstSub.FindFirst "[FINANCE NO] ='" & Left(Me![cbxSEEK], 6) & "'"
If Not rstSub.NoMatch Then Me.Bookmark = rstSub.Bookmark
Exit Sub

Any suggestions? Am I way off base here? Thanks.

Richard...

 

[turtlemaster]

Instead of creating all of those objects I would use the tools already there. The recordset clone is just that a clone of the recordset used to create your form. So I would just search that and if a match is found maneuver your form to the appropriate record by way of the primary key.

Private Sub cbxSEEK_AfterUpdate()
Me.RecordsetClone.FindFirst "[FINANCE NO] ='" & Left(Me![cbxSEEK], 6) & "'"
If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub