[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Record Navigation with VB script.

Posted on 2008-11-06
Medium Priority
Last Modified: 2013-11-27
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

Question by:mmoore500
  • 2
LVL 65

Expert Comment

ID: 22899345
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.


Author Comment

ID: 22901312
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?

LVL 65

Assisted Solution

RobSampson earned 400 total points
ID: 22901435
somevalue would be txtNavRecord.value, so that line of code would be

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


LVL 65

Accepted Solution

rockiroads earned 1600 total points
ID: 22902301
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.


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question