GOTO Indexed Record in Code

[ApexData]

Hello

How do I goto a specific record in a BOUND form when the form has a
primary
index of ID which is autonumbered. I need to visit 4 seperate records,
one at a time.

This works for RecNO but no good for Field search: DoCmd.GoToRecor d ,
, acGoTo, 19
And I know is NOT recommended.

I Also tried SQL in code, but keep getting syntax errors:
strSQL = "Select * From "T-Table" Where ID = 20"
Set Rst = CurrentDb.OpenR ecordset(strSQL , dbSnapshotOnly)

Please Help

[Allen Browne]

Use FindFirst on the RecordsetClone of the form.

Example:

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
With Me.RecordsetClo ne
.FindFirst "ID = 20"
If .NoMatch Then
MsgBox "not found"
Else
Me.Bookmark = .Bookmark
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<Ap******@gmail .com> wrote in message
news:11******** **************@ d71g2000cwd.goo glegroups.com.. .

Hello

How do I goto a specific record in a BOUND form when the form has a
primary
index of ID which is autonumbered. I need to visit 4 seperate records,
one at a time.

This works for RecNO but no good for Field search: DoCmd.GoToRecor d ,
, acGoTo, 19
And I know is NOT recommended.

I Also tried SQL in code, but keep getting syntax errors:
strSQL = "Select * From "T-Table" Where ID = 20"
Set Rst = CurrentDb.OpenR ecordset(strSQL , dbSnapshotOnly)

[Bob Quintal]

"Ap******@gmail .com" <Ap******@gmail .com> wrote in
news:11******** **************@ d71g2000cwd.goo glegroups.com:

Hello

How do I goto a specific record in a BOUND form when the form
has a primary
index of ID which is autonumbered. I need to visit 4 seperate
records, one at a time.

This works for RecNO but no good for Field search:
DoCmd.GoToRecor d , , acGoTo, 19
And I know is NOT recommended.

I Also tried SQL in code, but keep getting syntax errors:
strSQL = "Select * From "T-Table" Where ID = 20"
Set Rst = CurrentDb.OpenR ecordset(strSQL , dbSnapshotOnly)

Please Help

you can use

rst.findfirst "fieldname = " & criteria
if not rst. nomatch then .
...... whatever.
That works on everything, and may use indexes, even though the
help says it doesn't.

You can try the .seek method, check the help for that, but it
only works with .openrecordset( sql,dbOpenTable )

iirc dbSnapshotOnly should be dbOpenSnapshot, which might
explain your error messages.

--
Bob Quintal

PA is y I've altered my email address.

[ApexData]

ThankYou for the responses.

I tried the FindFirst method that Allen suggested, and it works fine.
But, having read some posts over the last 2-hours, am concerned that it
is "Obsolete" and slow and may cause me problems when I split the DB
and run it over the network. Those posts mention Seek and SQL as the
prefered methods, however, I cannot seem to get the SQL in code to
work.

AnyThoughts?

[ApexData]

FINALLY

I finally managed to get SQL to work at finding each specific record I
was looking for.
The code below does it. Is this method advisable? Will it satisfy the
concerns of my previous post?

Private Sub DBTEST()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT * FROM [T-Table] Where ID = 20" 'Id is indexed
autonumber
Set db = CurrentDb
Set rs = db.OpenRecordse t(strSQL, dbOpenForwardOn ly)
MsgBox rs!MyField
strSQL = "SELECT * FROM [T-Table] Where ID = 19"
Set rs = db.OpenRecordse t(strSQL, dbOpenForwardOn ly)
MsgBox rs!MyField
End Sub

[Bob Quintal]

"Ap******@gmail .com" <Ap******@gmail .com> wrote in
news:11******** **************@ y43g2000cwc.goo glegroups.com:

ThankYou for the responses.

I tried the FindFirst method that Allen suggested, and it
works fine. But, having read some posts over the last 2-hours,
am concerned that it is "Obsolete" and slow and may cause me
problems when I split the DB and run it over the network.
Larry is "obsolete". The .seek method is faster, when it works.
But it often doesn't work. And if you split the database, and move
the back end into SQL server, the seek method definitely will not
work reliably.
Those posts mention Seek and SQL as the prefered methods,
however, I cannot seem to get the SQL in code to work.

AnyThoughts?

Glad you got it to work (I saw your other post)

--
Bob Quintal

PA is y I've altered my email address.

[Lyle Fairfield]

SQL is a good idea in most cases. You've used it to find a record and
determine the value of "MyField". How will you use that information to
move the record pointer on the bound form?
Usually we use a Bookmark for that as Allen and Bob have suggested. Can
you get a meaningful bookmark from your SQL?
If not, perhaps you should reconsider and use their ideas. Could
FindFirst be very slow? Perhaps, if the Form has a HUGE recordset, it
could be. But if that's the case then there may be other problems too.
Seek is unlikely to be significantly faster than SQL execution (some
SQL might be written a bit more efficiently than yours). As Bob has
pointed out, Seek works on TableType Recordsets only. You have to open
linked tables directly to use Seek.
Why mess with this and open a recordset of many records and then Seek
one, when you can just as efficiently use SQL to open a recordset of
just that one record (as you have done)? I used to be a great champion
of Seek. I haven't even considered using it for years.
I suggest you follow Allen's suggestion which is very clear and see how
it works. If it's too slow then repost with that new speed problem.

[David W. Fenton]

"Ap******@gmail .com" <Ap******@gmail .com> wrote in
news:11******** **************@ y43g2000cwc.goo glegroups.com:

I tried the FindFirst method that Allen suggested, and it works
fine. But, having read some posts over the last 2-hours, am
concerned that it is "Obsolete" and slow and may cause me problems
when I split the DB and run it over the network. Those posts
mention Seek and SQL as the prefered methods, however, I cannot
seem to get the SQL in code to work.

It depends on what you're attempting to do.

If you're moving the current record in a set of records in a form,
then Allen's method (with .FindFirst on the RecordsetClone) is the
best.

If you're trying to open a recordset with a selected set of records
then SQL is the right way to go.

SEEK plays no role in either of those scenarios, and is not really
going to be useful in very many situations, as it's limited to
table-type recordsets and to a single table. And it's useful only in
the situations where you need to open a large table and jump around
that table extensively.

Finally, in cases where you need to jump around a non-table-type
recordset, FindFirst/FindNext/FindPrevious operations can be
optimized if your recordset is sorted in the order of the field
you're matching on. In that case, you'd cache the value of the last
find operation and decide whether to use FindNext or FindPrevious,
only using FindFirst on the initial find. The result of that
optimization is that you end up moving the minimum distance through
the searched column.

It's still not as efficient as SEEK, but when SEEK is not an option,
it's a great improvement over multiple FindFirst operations.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[ApexData]

>SQL is a good idea in most cases. You've used it to find a record and

determine the value of "MyField". How will you use that information to
move the record pointer on the bound form?

I'm happy with the SQL, however I need to BookMark 1-Record and I can't
seem to
make it happen. I want to use Allen & Bob's suggestions (ie FindFirst)
since it
works ideally for my situation and the speed is not an issue. If I go
this route will the operation be reliable on the NETWORK and will the
FindFirst statement likely remain
compatible with future environments?

ThankYou ALL
Greg

[Bob Quintal]

"Ap******@gmail .com" <Ap******@gmail .com> wrote in
news:11******** **************@ i39g2000cwa.goo glegroups.com:

SQL is a good idea in most cases. You've used it to find a
record and determine the value of "MyField". How will you use
that information to move the record pointer on the bound form?

I'm happy with the SQL, however I need to BookMark 1-Record
and I can't seem to
make it happen. I want to use Allen & Bob's suggestions (ie
FindFirst) since it
works ideally for my situation and the speed is not an issue.
If I go this route will the operation be reliable on the
NETWORK and will the FindFirst statement likely remain
compatible with future environments?

ThankYou ALL
Greg

I use .findFirst/last/next/previous across the network all the
time, with data stored in SQL Server, other Access .mdbs, and
occasionally linked Excel spreadsheets.
--
Bob Quintal

PA is y I've altered my email address.