Clone a Recordset Clone??

[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...

 

[ClydeDoggie]

Richard,
You don't need all the stuff for a second recordset. The same code, with a different FindFirst criteria will work. I recommend cleaning up your objects by setting rst=nothing before leaving the subs as a good practice. This would now be the second combos code.

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

 

[Rjc8513]

ClydeDoggie,
Thanks for the response but that didn't seem to work. Maybe I wasn't clear enough but I still want the ability to get the data first by the Function Number (cbxFIND) and then, if desired, to further filter the selected data by Finance No (cbxSEEK).

Thanks for your help.

Richard...

 

[ClydeDoggie]

OOPS!
I didn't understand you wanted both. As long as the second combo will always be used with the first you can create a combined filter statement (see new Sub below). If the second combo will sometimes be used alone, sometimes be used with first it gets trickier but is still doable. Let me know if you want code for an either/or situation.

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

 

[Rjc8513]

The way I need it to work is that first the user selects from the cbxFIND (the first combo) combo box. That may be the only choice the user makes, not wishing to filter the data further. cbxFIND selects all the data for the district for the specified function and displays it in the subform.

But, the user MAY then want to filter the data using the cbxSEEK (the second combo box) combo box. That filters the data for the function selected in the first combo box down to the particular sales unit and displays it in the same subform.

Hope that's clear! Thanks.

Richard...

 

[ClydeDoggie]

Richard,
the code in my second post should work for the situation you describe. Let me know if it doesn't.

 

[Rjc8513]

Sorry ClydeDoggie, doesn't seem to work.

What, if any, code should I have in the AfterUpdate property of the cbxFIND combo box?

Thanks.

Richard...

 

[ClydeDoggie]

Richard,
The code in the AfterUpdate property of cbxFIND should be the same as you show above (in your first post). Let me know what is and isn't happening when you have the code as I have shown. I built a little test db and recreated the situation you described and it seems to work fine. Also, what version of Access are you running, it shouldn't make a difference but ...

 

[Rjc8513]

Sorry to keep bugging you, ClydeDoggie. In effect, the second combo box does nothing. After I select from the first combo box, the subform is updated to show the selected function. No problems there.

When I select from the second combo box nothing happens. The subform is not updated. The data shown is exactly the same data shown after cbxFIND is updated.

Thanks again for your help.

Richard...

 

[ClydeDoggie]

Richard,
I think we have some miscommunication here that is preventing me from helping you. If you can clarify some things maybe we can make progress.
1. You say that you are 'filtering' records on a subform based on combo boxes on a main form. The code you show uses the 'me' reference which would not work in the above situation. Are the combo boxes really on the subform, or perhaps you have the main form and the subform linked such that the code works despite the misplaced reference?
2. You talk about 'filtered' and 'further filtered', yet you are using 'FindFirst' method. Do you want to actually filter the records or just move to the first that meets your criteria?
3. When you say that 'nothing happens' when you make a choice in the second combobox, are you sure there are records that meet the combined criteria? If you didn't have a record that met the criteria then 'nothing happens' would be the correct behavior for the code as it is written.

Let me know the answers to the above, I am interested in getting to the bottom of this.

 

[Rjc8513]

The combo boxes are on the main form. The main and subforms are linked by Function No.

I want to first see all records that meet the cbxFIND criteria which is all the units in the district that have, for example, Function 1 data. So, in the subform I would see several units all of which have Function 1 data and Function 1 data only. That part works fine.

Then what I want to do is to select one of the displayed units (from cbxSEEK- cbxSEEK lists every unit in the district) and have the desired unit displayed in the subform with just the desired Function 1 data selected in cbxFIND.

 

[ClydeDoggie]

OK!
I know what is going on now. Because of the link on FunctionNo the subform is being 'filtered' (only data that corresponds to the link is displayed) by cbxFind. For cbxSeek try this code (substitute your subform name for 'frmChild').

Private Sub cbxSeek_AfterUpdate

Dim rst As Recordset
'########
'This filters the subform to only show records that have a Finance No that is in cbxSeek
Me.frmChild.Form.Filter = "[FinanceNo]=" & Me.cbxSeek
Me.FilterOn = True
Me.Refresh
'#########

'*************
'This code is for moving to the first record that has a Finance No that is equal to cbxSeek, all records still showing. Uncomment this and comment out the above to change from filter to find first.
'Set rst = Me.frmChild.Form.RecordsetClone
'rst.FindFirst "[FinanceNO] =" & Me![cbxSeek]
'If Not rst.NoMatch Then Me.frmChild.Form.Bookmark = rst.Bookmark
'set rst=nothing
'*************

End Sub

I believe this will get you going. Let me know if I still am missing it.

 

[Rjc8513]

ClydeDoggie,

I think the second solution is what I'm looking for. It worked just fine.

I appreciate all the time you devoted to my problem. Thanks.

Richard...