Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-10-05
14
280 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

839 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