Record Navigation with VB script.

Posted on 2008-11-06
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
    LVL 65

    Expert Comment

    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.

    LVL 2

    Author Comment

    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

    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

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now