By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Quick search in Access database

P: n/a
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??
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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??

Nov 12 '05 #2

P: n/a
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??


Nov 12 '05 #3

P: n/a
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


Nov 12 '05 #4

P: n/a
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??



Nov 12 '05 #5

P: n/a
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??



Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.