"Go To" Reference Number

W

weircolin

Hi

Is it possible, and if so, how would I go about doing it, to have a
text box on a form where I can type in the reference number of the
record that I want to display and it will display that record?

Thanks

Colin
 
D

Douglas J. Steele

Add a text box (call it txtReference for the sake of argument).

In the AfterUpdate event for that text box, add code like:

Private Sub txtReference_AfterUpdate()
Me.RecordsetClone.FindFirst _
"ReferenceNumber = " & Me.txtReference
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

This assumes that ReferenceNumber is a numeric field. If it's text, you'll
need

Me.RecordsetClone.FindFirst _
Chr$(34) & "ReferenceNumber = " & Me.txtReference & Chr$(34)
 
K

Klatuu

Is Reference Number a field in your table or are you asking how to go to a
record number?

If you are asking how to go to a record number, you cannot reliably do that.
Record numbers really have no meaning in Access. Changing the filtering, the
sort order, or adding or deleting records changes that number. Record
numbers in Access are relative only to their position in the current record
set.

If you want to use a specific field in a table, you first have to have a
control in which you enter the value you want to search on. This is most
often done with a Combo Box, but it will work with a text box. In either
case, you will want to use the After Update event of the control:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[ReferenceNumber] = " & Me.txtRefNo
If rst.NoMatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
 
W

weircolin

Hi

Thanks both for your replies. I may give it a try. I have created a
search function with a combo box for the Reference Number which allows
me to view the record which seems to be working alright.

Would you recommend the other method istead for any reasons?

Thanks again

Colin said:
Is Reference Number a field in your table or are you asking how to go to a
record number?

If you are asking how to go to a record number, you cannot reliably do that.
Record numbers really have no meaning in Access. Changing the filtering, the
sort order, or adding or deleting records changes that number. Record
numbers in Access are relative only to their position in the current record
set.

If you want to use a specific field in a table, you first have to have a
control in which you enter the value you want to search on. This is most
often done with a Combo Box, but it will work with a text box. In either
case, you will want to use the After Update event of the control:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[ReferenceNumber] = " & Me.txtRefNo
If rst.NoMatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

[email protected] said:
Hi

Is it possible, and if so, how would I go about doing it, to have a
text box on a form where I can type in the reference number of the
record that I want to display and it will display that record?

Thanks

Colin
Click to expand...
Click to expand...
 
Top