How to get form record to equal recordset record?

[mekmike1]

I have opened a recordset and located a record that has multiple-column
criteria. Now I need my form to show that record. How do I get the data table
to show the record I located in the recordset or recordset clone?

This line:

Me.Bookmark = rsClone.Bookmark


gives and error '3159'

NOT A VALID BOOKMARK!

Help!!!

 

[John Spencer (MVP)]

It would be best if you included ALL the code instead of just the line that
failed.

As a GUESS
Me.Bookmark = Me.RecordsetClone.Bookmark

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mekmike1]

Here is all of the code (using ADO). I have found many examples of how to set
the Me.Bookmark = Me.RecordsetClone.Bookmark using the FIND method. I have
multiple criteria so I can't use the FIND method. I have tried everything
from SEEK to FILTER. I can locate the record using the main table recordset
and in a cloned recordset but in no way have I been successful at making the
form show that record once I find it in the recordset.

How can I synchronize the FORM to show the record that is currently selected
in the recordset??? I have found methods of synchronizing the recordset to
what is on the form but not likewise.

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

Aside from my attempt can you recommend another way of searching for a
single record in a table with multiple-criteria and then make a form show
that record?

Thanks for your help.

 

[John Spencer (MVP)]

Hopefully someone else will step in and answer your question. I don't work
enough with ADO to feel that I can give you a competent answer.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have
multiple criteria so I can't use the FIND method.

But you can use the FindFirst and FindNext methods on a DAO recordset:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "<any valid SQL WHERE clause>"
If rs.NoMatch Then
<handle nomatch condition>
Else
Me.Bookmark = rs.Bookmark
End If

 

[David W. Fenton]

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "<any valid SQL WHERE clause>"
If rs.NoMatch Then
<handle nomatch condition>
Else
Me.Bookmark = rs.Bookmark
End If

Aiyee! Why set a recordset variable? You just have to clean up after
using it and it's not at all simpler than this:

With Me.RecordsetClone
.FindFirst "<any valid SQL WHERE clause>"
If .NoMatch Then
<handle nomatch condition>
Else
Me.Bookmark = .Bookmark
End If
End With

No cleanup involved. No extra memory allocated for the recordset
pointer.

 

[mekmike1]

And the SQL WHERE clause can have more then one variable? I need to locate a
record using "Type" and "Serial Number" There will only be one record with
the combination. Do you happen to have a snippet of code that demaonstrates
the WHERE using two variables? If not thanks for your help anyway.

Sincerely,

 

[mekmike1]

Can I use ADO and DAO in code bhind the same form? I thought I read somewhere
that you can mix the two. I already have a bunch of ADO code so I would
prefer sticking with that. Do you have any solutions or suggestions to find a
record using two fields of criteria and then make the form show that record?'

Thanks for your help.

 

[David W. Fenton]

And the SQL WHERE clause can have more then one variable? I need
to locate a record using "Type" and "Serial Number" There will
only be one record with the combination. Do you happen to have a
snippet of code that demaonstrates the WHERE using two variables?

.FindFirst "[Type]='Type1' And [Serial Number]=12455"

(if Type is a numeric field, you don't need the single quotes)

 

[David W. Fenton]

Can I use ADO and DAO in code bhind the same form? I thought I
read somewhere that you can mix the two. I already have a bunch of
ADO code so I would prefer sticking with that.

You can, but I'm not sure why you'd want to. If you have both
references set, you do need to disambiguate for the overlapping data
types, e.g., DAO.Recordset vs. ADO.Recordset.

 

[John W. Vinson]

You can, but I'm not sure why you'd want to. If you have both
references set, you do need to disambiguate for the overlapping data
types, e.g., DAO.Recordset vs. ADO.Recordset.

David, isn't the problem that the Form's recordsetclone - which has a bookmark
coordinated with the form's bookmark - is a DAO recordset? If there's an easy
way to synch a form with an ADO recordset I'd be interested to know it.

 

[David W. Fenton]

David, isn't the problem that the Form's recordsetclone - which
has a bookmark coordinated with the form's bookmark - is a DAO
recordset? If there's an easy way to synch a form with an ADO
recordset I'd be interested to know it.

I don't know. I don't know why anyone would be using and ADO
recordset in Access that needed to be coordinated with a form's
RecordsetClone.

 

[mekmike1]

When I started coding this database months ago I used many resources to learn
including (3) books, blogs, discussion groups, Microsoft Visual Basic Help,
etc... Many of those resources quoted "DOA is an older data access technology
that Access still uses" and that "ADO is the current technology that will be
supported in the future." My need to show a specific record that was found
using multi-column criteria on a form based on the same data seems like a
completely legitimate action. Why does needing to do that in ADO puzzle you?
Do you have any suggestions as to why i should not use ADO other than the
fact that it seems to lack certain functionality that is found using DAO. Any
suggestions are appreciated.

Keep in mind my goal is to located a record in the data table using two
criteria and then show that record in the current form that is tied to a data
table. Thanks!

 

[Douglas J. Steele]

The authors of those books were mislead by bad marketing decisions from
Microsoft.

Microsoft may have been hoping to move everyone from DAO to ADO, but the
attempt failed. ADO has been supplanted by ADO.Net, which is quite different
(and I don't believe actually works with Access)

DAO, on the other hand, lives on as part of ACE, the new database engine
built for Access 2007.

Since DAO was developed specifically for Jet (and now ACE), it's more
efficient than going through the extra layers of abstraction introduced by
the more generic ADO model.

 

[David W. Fenton]

Keep in mind my goal is to located a record in the data table
using two criteria and then show that record in the current form
that is tied to a data table.

In addition to what Doug said, I don't understand why you need any
recordsets to do what you describe, DAO or ADO. Perhaps I've lost
the thread of the discussion, but it seems like you should start
with a form and learn how to filter its contents. That doesn't
require any recordsets at all. I just posted responding to someone
else asking a similar question in the thread titled "Some questions
about Recordset?". I gave detailed instructions on how to implement
filtering of a form based on user-chosen criteria. Reading that
might be a starting point.

 

[mekmike1]

Your suggestion to just use DAO is the path I took. Everything seems to work
fine after converting my ADO code and using DAO and .Findfirst for locating a
record with multiple criteria. My only problem is after I modify a record in
the recordset and then close the recordset I do a Me.Requery which gives me a
Write Conflict error and asks If I want to save copy cancel or something like
that. The Me.Requery used to work fine in ADO but now it errors.
I do the Me.Requery to show changes if a user cancels their updates, as a
result of a dialog box asking them, and if they click "Canel" then I use
OldValue to put the controls back to their previous state. The Me.Requery
allows the OldValue changes to show. Any suggestions on how to properly do a
Me.Requery without getting the Write Conflict error? Thanks again for your
help.

 

[John W. Vinson]

Your suggestion to just use DAO is the path I took. Everything seems to work
fine after converting my ADO code and using DAO and .Findfirst for locating a
record with multiple criteria. My only problem is after I modify a record in
the recordset and then close the recordset I do a Me.Requery which gives me a
Write Conflict error and asks If I want to save copy cancel or something like
that. The Me.Requery used to work fine in ADO but now it errors.
I do the Me.Requery to show changes if a user cancels their updates, as a
result of a dialog box asking them, and if they click "Canel" then I use
OldValue to put the controls back to their previous state. The Me.Requery
allows the OldValue changes to show. Any suggestions on how to properly do a
Me.Requery without getting the Write Conflict error? Thanks again for your
help.

If you just want to display the data on the form, it's not necessary to
requery it. Just set the Form's Bookmark property to the recordsetclone's
Bookmark to show the changes.