RecordsetClone not working after Find ?

[saustin]

Good Morning,
Use RecordsetClone.Count to grab the count of records displayed in 4 subforms. Works perfectly after next, previous, first, and last record buttons are clicked ... BUT.. after the FIND button is pressed and the record you are searching for is displayed the RecordsetClone still displays the record that existed PRIOR to the FIND. Is there any way of refreshing or updating the RecordsetClone after a find occurs ?
Thanks ! Steve.

 

[VicM]

saustin:

Not quite sure to what extent you are using the clone.

Also when you say that it works after the next, previous, first and last buttons. Are you referring to the navigation arrows supplied by Access in the lower left corner of the form? Or did you create navigation buttons yourself? Is the FIND button something you created with code behind it?

If you are using the navigation buttons provided by Access, then the reason it works with them is because those buttons move to the respective records in the underlying recordset for your form.

If your FIND button is something you created with code behind it, and that code uses the recordset clone, then it won't refresh the record unless you set the source recordset's bookmark equal to the clone's bookmark and do a me.refresh before exiting the FIND code.

A clone is used to manipulate records in a record set without disturbing the underlying recordset. Or, to do a find like you've described. But the clone is not the underlying recordset controlling which records are displayed unless you tie the bookmarks together.

Hope this helps,

Vic

 

[saustin]

Hi Vic,
Many many thanks for the reply.
"then it won't refresh the record unless you set the source recordset's bookmark equal to the clone's bookmark and do a me.refresh before exiting the FIND code." Perfect !

Took a look at the FIND vba and only see....

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

This is vanilla vba from a cmd button - find record.
Understand that i should set the bookmark, but am not sure how.

Set rst = Me.RecordsetClone
Me.Bookmark = rst.Bookmark
me.refresh

This produces a type mismatch on the first line.

 

[VicM]

saustin:

Did you dimension the rst as a recordset? If not, that may be why you are getting a type mismatch

Take a look at the bookmark help example.

Vic

 

[saustin]

Hi Vic,
This was set in the General section. Just for kicks put it in the FIND section. Here is the vba.
txtPART.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70


Debug.Print "Steve, start of findpart values"
Debug.Print Forms!frmPriceAnalysis!frmSikorksyPOHistforsubform.Form!.RecordsetClone.RecordCount
Debug.Print Forms!frmPriceAnalysis!frmHSISDPOforGridsubform.Form!.RecordsetClone.RecordCount
Debug.Print Forms!frmPriceAnalysis!frmHSIInventoryDetailforGridsubform.Form!.RecordsetClone.RecordCount
Debug.Print Forms!frmPriceAnalysis!frmHSIPriceListforGridsubform.Form!.RecordsetClone.RecordCount

Dim rst As Recordset
Set rst = Me.RecordsetClone
Me.Bookmark = rst.Bookmark
Me.Refresh

It still gives me a type mismatch error on the
Set rst = Me.RecordsetClone line.

The debug statments are all showing me data for the record when the FIND button was clicked.

 

[saustin]

Hi All,
Update... what happens with the vanilla FIND vba ...

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

is that the FIND loses focus and any code after this (before the end of the sub) gets executed prior to the acEditMenu finishing ! Strange, talk about putting the cart before the horse.

Example.
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

The last two docmd's finish prior to the edit menu closing.
So how do you put any custom vba in the FIND click event ?

Thanks, Steve.