How to make form show record located in recordset?

[mekmike1]

I have located a record in a recordset. How do I make the form, tied to that
recordset, show that record?

 

[Stefan Hoffmann]

hi,

I have located a record in a recordset. How do I make the form, tied to that
recordset, show that record?

Use the Bookmark property:

http://msdn.microsoft.com/en-us/library/bb243795.aspx


mfG
--> stefan <--

 

[mekmike1]

Thank you for the response but I am using ADO. This has been extremely
frustrating because there are many resources for DAO but not as many for ADO.
I have found methods of locating records in recordsets that are tied to this
form but CANNOT find a way to make the form show that record. It seems absurd
that vba does not allow you to set me.bookmark = rs.bookmark but I keep
getting the Invalid Bookmark error.

Thanks for your help.

 

[Dirk Goldgar]

Thank you for the response but I am using ADO. This has been extremely
frustrating because there are many resources for DAO but not as many for
ADO.
I have found methods of locating records in recordsets that are tied to
this
form but CANNOT find a way to make the form show that record. It seems
absurd
that vba does not allow you to set me.bookmark = rs.bookmark but I keep
getting the Invalid Bookmark error.

Bookmarks are only compatible within a recordset and its clone. The form's
Bookmark property is passed through to the form's recordset, so only
bookmarks derived from that recordset or its clone can be used to set the
form's Bookmark.

What is the code you are using? You said the form is tied to the recordset
you are using, but it sounds as though this is not the case. Please post
your code.

 

[mekmike1]

Here is the code. Thank you for looking. Is there an answer to my question
because I have other posts related to this and nobody has any solution other
than using DAO code with FindFirst?

Public Sub Find_Button_Click()

'Open "PCM Interfaces (Main Table) recordset.
Dim rsMainData As New ADODB.Recordset
With rsMainData
.Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
.Index = "Search_Index"
.MoveFirst
End With

Dim rsClone As New ADODB.Recordset

Set rsClone = rsMainData.Clone

Me![Search_srl_box].SetFocus

'The filter criteria will eventually include the value of another text box
(AND)

rsClone.Filter = "[Serial Number] ='" & Search_srl_box.Text & "'"

If rsClone.EOF = False Then

Me.Bookmark = rsClone.Bookmark

End If

rsClone.Close
Set rsClone = Nothing

rsMainData.Close
Set rsMainData = Nothing

End Sub

 

[Stefan Hoffmann]

hi,

Here is the code. Thank you for looking. Is there an answer to my question
because I have other posts related to this and nobody has any solution other
than using DAO code with FindFirst?

Try this first:

http://support.microsoft.com/kb/195222
http://www.devguru.com/technologies/ado/quickref/recordset_find.html

Otherwise comments will follow inline...

Public Sub Find_Button_Click()

Don't use the As New syntax in VBA. Use an explicit

Set rsMainData = New ADODB.Recordset

This is due to this nice behaviour, which can also happen in some normal
situations:

Dim rs As New ADODB.Recordset
If Not rs Is Nothing Then MsgBox "rs is useable."
Set rs = Nothing
If Not rs Is Nothing Then MsgBox "rs is useable."

It can happen in this case that you use an object which is reinitialized
and thus carries different data.
The MoveFirst is not needed. You only need a

.MoveLast
.MoveFirst

when you want to use RecordCount.
Same as above.

Set rsClone = rsMainData.Clone
Me![Search_srl_box].SetFocus

As you're addressing a control, which you normally ever assign a name, use

SearchSrlBox.SetFocus

This makes code reading easier.

'The filter criteria will eventually include the value of another text box
(AND)

rsClone.Filter = "[Serial Number] ='" & Search_srl_box.Text & "'"
If rsClone.EOF = False Then
Me.Bookmark = rsClone.Bookmark
End If

Use the Find method:

http://msdn.microsoft.com/en-us/library/ms676117(VS.85).aspx



mfG
--> stefan <--

 

[mekmike1]

It looks like I am using a method that is identical or very similar to the
method described in the link you sent me regarding using a filter.
(http://support.microsoft.com/default.aspx/kb/195222)

My problem is that once i find the record in the rsClone and then set the
rs.bookmark = rsClone.boomark

The form STILL SHOWS THE FIRST RECORD IN THE DATA TABLE!! NOT THE RECORD
WHICH IS LOCATED AT THE BOKKMARK I SET.

How do I get the form to show the record that I set as the bookmark in the rs?

Is there a refresh, reprint, requery, update, or something that I need to do
to get the form to show the correct record? I have tried all of those with no
success.

Thanks for your help!
--
Mac

hi,

Here is the code. Thank you for looking. Is there an answer to my question
because I have other posts related to this and nobody has any solution other
than using DAO code with FindFirst?

Try this first:

http://support.microsoft.com/kb/195222
http://www.devguru.com/technologies/ado/quickref/recordset_find.html

Otherwise comments will follow inline...

Public Sub Find_Button_Click()

Don't use the As New syntax in VBA. Use an explicit

Set rsMainData = New ADODB.Recordset

This is due to this nice behaviour, which can also happen in some normal
situations:

Dim rs As New ADODB.Recordset
If Not rs Is Nothing Then MsgBox "rs is useable."
Set rs = Nothing
If Not rs Is Nothing Then MsgBox "rs is useable."

It can happen in this case that you use an object which is reinitialized
and thus carries different data.
The MoveFirst is not needed. You only need a

.MoveLast
.MoveFirst

when you want to use RecordCount.
Same as above.

Set rsClone = rsMainData.Clone
Me![Search_srl_box].SetFocus

As you're addressing a control, which you normally ever assign a name, use

SearchSrlBox.SetFocus

This makes code reading easier.

'The filter criteria will eventually include the value of another text box
(AND)

rsClone.Filter = "[Serial Number] ='" & Search_srl_box.Text & "'"
If rsClone.EOF = False Then
Me.Bookmark = rsClone.Bookmark
End If

Use the Find method:

http://msdn.microsoft.com/en-us/library/ms676117(VS.85).aspx



mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

My problem is that once i find the record in the rsClone and then set the
rs.bookmark = rsClone.boomark

Are you sure, that this line is executed? It normally works...



mfG
--> stefan <--

 

[mekmike1]

I am sure the line is executed.It is actually in my code as:

rsMainData.Bookmark = rsClone.Bookmark
rsClone.Close
Set rsClone = Nothing
rsMainData.Close
Set rsMainData = Nothing

And then nothing else happens. The original form which the recordset and the
recordsetClone were sourced from still is showing the very first record. It
is acting as though setting the recordset bookmark has no effect on which
record is visible in the form.

 

[John W. Vinson]

And then nothing else happens. The original form which the recordset and the
recordsetClone were sourced from still is showing the very first record. It
is acting as though setting the recordset bookmark has no effect on which
record is visible in the form.

It doesn't.

You need to set the *FORM'S* bookmark property to its recordsetclone's
bookmark:

Forms!yourform.Bookmark = rsClone.Bookmark

 

[mekmike1]

When I do that it still gives the "Not a valid Bookmark" error. I suspect
that it is due to the clone being a clone of a recordset and not a clone of
the form "Set rsClone = rsMainData.Clone " versus "rsClone =
me.recordsetClone" I need to determine if I can filter such that
me.recordsetClone.Filter= Criteria.... or go in a nother direction and use
DAO. I was trying to use all ADO to stay consistent but if ADO just does not
have a method of synchronizing a form to a recordset "me.bookmark =
rs.bookmark" or "Me.bookmark = rsClone.bookmark" then I must use DAO which
does allow it.

 

[John W. Vinson]

I was trying to use all ADO to stay consistent but if ADO just does not
have a method of synchronizing a form to a recordset "me.bookmark =
rs.bookmark" or "Me.bookmark = rsClone.bookmark" then I must use DAO which
does allow it.

I expect that's going to be what you'll need, then - a Form's RecordsetClone
property is a DAO recordset, and setting the form's Bookmark to the
recordsetclone's Bookmark *does* work. If there's a way to do it strictly in
ADO I don't know what it would be.