Solved

How can I search for an "Auto ID" number?

Posted on 2004-10-05
14
275 Views
Last Modified: 2006-11-17
Hello,

We need to be able to search for a particular Auto ID number.  For this project, the office uses the automatic ID number assigned by Access as the ID number.  We realize that the number will not be repeated even if it is deleted.  We have over 4,000 records in this table and they are referenced by ID number rather than name, Social Security Number, etc.

We also need to have a search button that is better than the one that the wizard creates in Access.  It needs to look only in FieldA and FieldD.  It should look anywhere in these two fields and should be non-case sensitive.  A find next and/or clear would be helpful but not required.

Any assistance that you can give will be appreciated by all of us.

WRS
0
Comment
Question by:sherman6789
  • 7
  • 3
  • 3
14 Comments
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 200 total points
ID: 12232454
Would these search fields be on the form that you want to find the data in? Do you want the search button to take you to the matched record? If so, you can use some code like this in the OnClick event of the search button:

Dim strFilter As String

If (Me!txtFieldA & "")<>"" Then strFilter=strFilter & "[FieldA] Like '*" & Me!txtFieldA & "*' AND "
If (Me!txtFieldB & "")<>"" Then strFilter=strFilter & "[FieldB] Like '*" & Me!txtFieldB & "*' AND "
If strFilter<>"" Then
   Me.RecordsetClone.FindFirst strFilter
   If Not Me.RecordsetClone.NoMatch Then
      Me.Bookmark=Me.RecordsetClone.Bookmark
   End If
End If
0
 

Author Comment

by:sherman6789
ID: 12232490
Thanks for your swift response, as usual:  shanesuebsahakarn

Yes, the search fields will be on the current screen.  Lately, I have been designing all-in-one screens that have most of the information on one screen.  This helps the user avoid switching back and forth from menu (switchboard) to result screens.  The exceptions are print menu screen and certain merge functions.

Your question reminded me that I may need the ability to search from one form and then press a button to have the resultant screen pop up.

Thanks again.

-William Sherman
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12232509
Let me know if you have any problems with it and I will do my best to help.
0
 

Author Comment

by:sherman6789
ID: 12232519
Thanks,

I'll work on it tonight.
0
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 200 total points
ID: 12232873
I would create in this situation a main form with a field txtID for the ID search (although showing the rows sorted by ID is normally enough to get the ID fast) and a field for the text to be searched e.g. named txtSearch.

The two field search can be done by placing this code behind the button:

me.subformname.form.filter = "[fieldA] like '*" & me.txtSearch & "*' or [fieldB] like '*" & me.txtSearch & "*'"
me.subformname.form.filteron = true

to reset this you can use another button with:

me.txtSearch = ""
me.subformname.form.filteron = false


Nic;o)

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12232896
It very much depends on whether you want the user to be able to navigate away from the matched record (using default or your own navigation buttons). A filter will pare down the recordset to only the matched record(s), and a find will move to the (first) matched record within the existing recordset.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:sherman6789
ID: 12252493
Thanks shanesuebsahakarn and nico5038,

I believe that I confused the issue by asking two questions at the same time.  I thought that they were related.

How do I add a feature to my form that allows me to type an ID number and that record immediately appears on the screen?  The form shows all needed information for the current record.  Navigation and search buttons are at the bottom of the form.  We are using the Autonumber feature as the item ID.

You both seem to have offered suggestions on the second question.  I am working on it now.

Thanks for your help.

WRSherman
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
ID: 12257713
When using the filter method the AutoID is done identical like:

me.subformname.form.filter = "[AutoID]=" & me.txtAutoIDSearch
me.subformname.form.filteron = true

Nic;o)
0
 

Author Comment

by:sherman6789
ID: 12258682
Thanks nico5038 for the information about searching autoID numbers.  I am keeping this post open a little longer incase I have additional questions about searching the two fields.

Before I close it, I plan to increase the points from 200 to 300 and split them between nico5038 and shanesuebsahakarn.

Thank you both.

WRSherman
0
 

Author Comment

by:sherman6789
ID: 12258707
Points have just been increased to 400.

WRSherman
0
 

Author Comment

by:sherman6789
ID: 12260005
Good morning,

I've had time to think about my requests  and to evaluate your replys.  I am still confused because I don't know exactly where to put some of the code.  Let me restate that there are two requests.

1.  I have about 3000 records in a database.  Records in the main table (tblCommunityAware) are used on the main form  (frmCommunityAware).

2.  The form displays items such as: Organization, ContactName, eMail, Costs, Type, etc.  At the bottom of the form are navigation buttons: (First, previous, next, last); plus other buttons: (Add, Delete, Save, and Exit).

3.  The normal MS search button is used to find records based on the information placed in the pop-up dialog box.  This Access generated search box is not very easy for users because they must remember to change the criteria to "any part of field", select case sensitivity, etc.  Also, if the user wants only a certain field searched, he or she must click in that field first or select from the list to find it.  That is why my original 2nd question was to develop a special search button which would automatically only look in two specified fields for search purposes.

4.  Frequently, the user knows the ID number of the item needed for an update.  My first questions was for the user to be able to enter an ID number somewhere on the form then hit Enter or click a button and the current form will immediately contain the information associated with the ID entered.  The ID is an auto field.  The items are presented in ID number order.  If the user wants to enter "412", I want item ID 412 to appear on the form.

You may have already understood these requests from the beginning.  I restated it because I wanted to be sure that I understood what I wanted as well as to give you all of the data that I think you needed at this point.  Please forgive me, if I was too redundant.

Thanks!

WRSherman
0
 
LVL 54

Expert Comment

by:nico5038
ID: 12260298
Normally I create a main form with a datasheet subform holding the table rows.
Now the Add/Update/Delete buttons are placed on the mainform and can act on the selected row from the datasheet.
As the datasheet subform can be sorted by ID number, the ID number selection isn't that much needed as the user can scroll to the row easily.
The right-click mechanism will also allow another sort and/or filtering. I made a small instruction document for my users so I don't have to code sort/filter buttons. (Drop me a line and I'll mail it, see my profile for the address)

The filtering code for the two fields can be placed behind a button and will make the datasheet subform to show only rows with "hits".

Clear ?

Nic;o)
0
 

Author Comment

by:sherman6789
ID: 12261213
Thanks again for clarifying.  This information will come in handy for one of my databases and I will be able to use it in many of the future tables for searching.  The current project has all of the information about a record on the screen at one time and the user does not have to scroll through numbers that are close to each other.  As an example:  The user may be working on record 17 and may need to skip to record 725 or 1218.  Scrolling may not be as convenient as just typing the need record number in a box, pressing a button and the record's information fills the form which is already on the screen.  I do like the idea of the subform on some of my other projects but not this one.

What is a good way to be able to type the number and go directly to that record.  Remember that the number in question is an autonumber and it is the key field.  I don't know if autonumbers are considered number fields, text fields or some other special fields.

Can the information above be used to accomplish this?

and YES! the previous information was presented very clear.

I will respond and request your instruction document that you mentioned above.

Thanks again.

WRSherman
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now