Search for an autonumber

[ablatnik]

I have a Search form that works perfect (Thank you Allen Browne). What I'm
having trouble with is the Search for a ID Number.

The ID Number increments by 1 with every Work Order I add. I am using this
ID Number as the Master Tracking Number throughout my database. I do not
want to use the Microsoft's built in search feature at the bottom of the form
because the search filter searches all the fields and doesn't open the exact
record I want to be in.

When I open the correct form I use for editing, the ID Number field is locked.


My idea is to open a blank work request and in either a pop up form or the
blank edit form, I would like to type to type a known ID Number and retrieve
the record I am looking for, or incorporate the search ability into the
search form already made.

I am using the code from Allen's link: http://allenbrowne.com/ser-62.html

 

[Allen Browne]

On your work request form, you want to provide a text box so the user can
jump to a particular ID number?

Add an unbound text box to the form (header section?), and give it
properties like this:
Name txtGoTo
Format General Number
After Update [Event Procedure]

Click the Build button (...) beside the AfterUpdate property.
Access opens the code window.

Set up the code so it looks like this:

Sub txtGoTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.txtGoTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.txtGoTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

For an explanation of the code, see:
http://allenbrowne.com/ser-03.html
That article suggests using a combo, but you can use a text box instead (as
above.)

 

[ablatnik via AccessMonster.com]

Allen, this works and doesn't work. Prior to inserting this code, all the
records would appear and the search form works great. Now after adding the
text box and entering this code, all the records upon opening the search form
do not show and I am not able to search on a ID_Number.

If I enter search data in the other text/combo boxes and perform a search,
the data shows up. After that search am I able to search for an ID_Numbers,
but only when the data is populated.

Is there a way of having all the data show up on the screen like it was
before.

Thanks.

On your work request form, you want to provide a text box so the user can
jump to a particular ID number?

Add an unbound text box to the form (header section?), and give it
properties like this:
Name txtGoTo
Format General Number
After Update [Event Procedure]

Click the Build button (...) beside the AfterUpdate property.
Access opens the code window.

Set up the code so it looks like this:

Sub txtGoTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.txtGoTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.txtGoTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

For an explanation of the code, see:
http://allenbrowne.com/ser-03.html
That article suggests using a combo, but you can use a text box instead (as
above.)

I have a Search form that works perfect (Thank you Allen Browne). What I'm
having trouble with is the Search for a ID Number.

[quoted text clipped - 18 lines]

I am using the code from Allen's link: http://allenbrowne.com/ser-62.html

 

[Allen Browne]

You are trying to combine 2 things:
- filter
- jump to a record.
Both things work but there will affect each other.

For example, if you filter to a subset that does not include ID number 25,
then the code to jump to ID 25 won't find it unless that code removes the
filter. If you do remove the filter, then you no longer have the filtered
results.

I'm not sure how you want this to work. As it stands, the new code (to jump
to a record) should not change the filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, this works and doesn't work. Prior to inserting this code, all the
records would appear and the search form works great. Now after adding
the
text box and entering this code, all the records upon opening the search
form
do not show and I am not able to search on a ID_Number.

If I enter search data in the other text/combo boxes and perform a search,
the data shows up. After that search am I able to search for an
ID_Numbers,
but only when the data is populated.

Is there a way of having all the data show up on the screen like it was
before.

Thanks.

On your work request form, you want to provide a text box so the user can
jump to a particular ID number?

Add an unbound text box to the form (header section?), and give it
properties like this:
Name txtGoTo
Format General Number
After Update [Event Procedure]

Click the Build button (...) beside the AfterUpdate property.
Access opens the code window.

Set up the code so it looks like this:

Sub txtGoTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.txtGoTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.txtGoTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

For an explanation of the code, see:
http://allenbrowne.com/ser-03.html
That article suggests using a combo, but you can use a text box instead
(as
above.)

I have a Search form that works perfect (Thank you Allen Browne). What
I'm
having trouble with is the Search for a ID Number.

[quoted text clipped - 18 lines]

I am using the code from Allen's link:
http://allenbrowne.com/ser-62.html