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
    Else
        'Clear the text box and suppress the Enter key.
	txtNavRecord.value = ""
	window.event.returnValue = False
    End If
End If

Open in new window

LVL 2
mmoore500Asked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
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.



0
 
rockiroadsCommented:
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

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

SkipRows
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.

SearchDirection
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.

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


0
 
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?

0
 
RobSampsonConnect With a Mentor Commented:
somevalue would be txtNavRecord.value, so that line of code would be

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

Regards,

Rob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.