Quick search in Access database

[Peter]

I have a form with a field "RecNo". In stead if using "Ctrl F" and
type a number I would like to use a seperate field on the form where
you just type in the number and push enter. You should then jump
directly to this record.

Can anyone tell me how I can do this??

[Allen Browne]

Assuming that RecNo is a Number type field (not Text), put text box on your
form and give it these properties:
ControlSource {leave this blank!}
Format General Number
Name txtFindRecNo
After Update [Event Procedure]

Then click the Build button (...) beside After Update.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter this:

Dim strWhere As String
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select a record."
Else
strWhere = "[RecNo] = " & Nz(Me.RecNo, 0)
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = .Bookmark
End If
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" <pi*************@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...

I have a form with a field "RecNo". In stead if using "Ctrl F" and
type a number I would like to use a seperate field on the form where
you just type in the number and push enter. You should then jump
directly to this record.

Can anyone tell me how I can do this??

[Phil Stanton]

That looks a bit complicated Allen, why not a simple unbound combo box
called "FindIt" with the rowsource something like
SELECT RecNo FROM MyTable ORDER BY RecNo
and the AfterUpdate event as

Private Sub FindIt_AfterUpdate()

DoCmd.GoToControl "RecNo"
DoCmd.FindRecord FindIt

End Sub

Phil

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

Assuming that RecNo is a Number type field (not Text), put text box on your form and give it these properties:
ControlSource {leave this blank!}
Format General Number
Name txtFindRecNo
After Update [Event Procedure]

Then click the Build button (...) beside After Update.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter this:

Dim strWhere As String
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select a record."
Else
strWhere = "[RecNo] = " & Nz(Me.RecNo, 0)
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = .Bookmark
End If
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" <pi*************@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...

I have a form with a field "RecNo". In stead if using "Ctrl F" and
type a number I would like to use a seperate field on the form where
you just type in the number and push enter. You should then jump
directly to this record.

Can anyone tell me how I can do this??

[Salad]

Allen Browne wrote:

Assuming that RecNo is a Number type field (not Text), put text box on your
form and give it these properties:
ControlSource {leave this blank!}
Format General Number
Name txtFindRecNo
After Update [Event Procedure]

Then click the Build button (...) beside After Update.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter this:

Dim strWhere As String
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select a record."
Else
strWhere = "[RecNo] = " & Nz(Me.RecNo, 0)
'minor correction. I think it should be
strWhere = "[RecNo] = " & Nz(txtFindRecNo, 0)
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = .Bookmark
End If
End With
End If

[Allen Browne]

1. FindRecord cannot notify you whether it succeeded or not.

2. Before it can move, Access must save the current record. We have found
that doing that explicity avoids a range of issues.

3. What did you expect your code to do if the user updates FindIt to a blank
(e.g. entering something and backspacing it out)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:40***********************@mercury.nildram.net ...

That looks a bit complicated Allen, why not a simple unbound combo box
called "FindIt" with the rowsource something like
SELECT RecNo FROM MyTable ORDER BY RecNo
and the AfterUpdate event as

Private Sub FindIt_AfterUpdate()

DoCmd.GoToControl "RecNo"
DoCmd.FindRecord FindIt

End Sub

Phil

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

Assuming that RecNo is a Number type field (not Text), put text box on

your

form and give it these properties:
ControlSource {leave this blank!}
Format General Number
Name txtFindRecNo
After Update [Event Procedure]

Then click the Build button (...) beside After Update.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter this:

Dim strWhere As String
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select a record."
Else
strWhere = "[RecNo] = " & Nz(Me.RecNo, 0)
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = .Bookmark
End If
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" <pi*************@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...

I have a form with a field "RecNo". In stead if using "Ctrl F" and
type a number I would like to use a seperate field on the form where
you just type in the number and push enter. You should then jump
directly to this record.

Can anyone tell me how I can do this??

[Phil Stanton]

Whoops!!
Humble Pie
Phil
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

1. FindRecord cannot notify you whether it succeeded or not.

2. Before it can move, Access must save the current record. We have found
that doing that explicity avoids a range of issues.

3. What did you expect your code to do if the user updates FindIt to a blank (e.g. entering something and backspacing it out)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:40***********************@mercury.nildram.net ...

That looks a bit complicated Allen, why not a simple unbound combo box
called "FindIt" with the rowsource something like
SELECT RecNo FROM MyTable ORDER BY RecNo
and the AfterUpdate event as

Private Sub FindIt_AfterUpdate()

DoCmd.GoToControl "RecNo"
DoCmd.FindRecord FindIt

End Sub

Phil

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

Assuming that RecNo is a Number type field (not Text), put text box on

your

form and give it these properties:
ControlSource {leave this blank!}
Format General Number
Name txtFindRecNo
After Update [Event Procedure]

Then click the Build button (...) beside After Update.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter this:

Dim strWhere As String
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select a record."
Else
strWhere = "[RecNo] = " & Nz(Me.RecNo, 0)
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = .Bookmark
End If
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" <pi*************@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...
> I have a form with a field "RecNo". In stead if using "Ctrl F" and
> type a number I would like to use a seperate field on the form where
> you just type in the number and push enter. You should then jump
> directly to this record.
>
> Can anyone tell me how I can do this??