Record Navigation with VB script.

I have an access database that I view through IE. I wanted to add a field that allows the user to navigate to a specific record in the database using the record "ID."

I found this code on the Microsoft website and it works, sort of. When the user enters a number, it navigates to absolute record number (ie. If I enter 10 into the field, the returned page may be the record with the "ID" 17, because some other records in between 1 and 10 have been deleted).

How can I get this script to navigate to the record "ID #" and not its absolute place in the record set?
'Trap the ENTER key.
If window.event.keyCode = 13 Then
    'Make sure the number entered is greater than zero.
    If txtNavRecord.value > 0 Then
	'Move to the specified record.
	MSODSC.DefaultRecordset.AbsolutePosition = txtNavRecord.value
	'Clear the text box.
	txtNavRecord.value = ""
	'Suppress the Enter key to avoid moving
        'to a new line in the text box.
	window.event.returnValue = False
        'Clear the text box and suppress the Enter key.
	txtNavRecord.value = ""
	window.event.returnValue = False
    End If
End If

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Not used this before but looks like how you would treat a recordset in vba

You could the find command, eg

MSODSC.DefaultRecordset.Find "MyIDField = " & somevalue, 0, 1, 1

the numbers on the end represent the arguments to the find command

Taken from help

A String value that contains a statement specifying the column name, comparison operator, and value to use in the search.

Optional. A Long value, whose default value is zero, that specifies the row offset from the current row or Start bookmark to begin the search. By default, the search will start on the current row.

Optional. A SearchDirectionEnum value that specifies whether the search should begin on the current row or the next available row in the direction of the search. An unsuccessful search stops at the end of the Recordset if the value is adSearchForward. An unsuccessful search stops at the start of the Recordset if the value is adSearchBackward.

Optional. A Variant bookmark that functions as the starting position for the search.

mmoore500Author Commented:
When you say: "MYIDField=" & somevalue

what should I type exactly?

I understand that for "MyIDField" I should replace it with "ID" because that is the column I need to search.

what does "& somevalue" mean?

somevalue would be txtNavRecord.value, so that line of code would be

MSODSC.DefaultRecordset.Find "MyIDField = " & txtNavRecord.value, 0, 1, 1


mmoore500, yes that is right. You are basically making the filter.

If your column is called ID then we use that. You already got the value in your code and Rob has expanded on that also.
Is this a string or a numeric field?

if numeric you can just do this

MSODSC.DefaultRecordset.Find "ID = " & txtNavRecord.value, 0, 1, 1

for info only so you know if next time you want to code it but search on a text field. If text you must wrap with single quotes eg

MSODSC.DefaultRecordset.Find "SomeTextField = '" & SomeTextValue & "'", 0, 1, 1

Next thing you need to add (once you get this working) is to add validation. Im assuming txtNavRecord is something the user enters? If so they may enter a wrong value so validation is a must. If not and is selected from a valid list, you can get away with it so as long as nothing else wrong happens.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.