Find Record

[Joe]

Hello,

I would like to create a custom search button on a form. The way it would
work, is that the user will enter the account # on a text box and the code or
macro will search for it. If it finds the record, then the form will open
and the users can begin working. If it doesn't find the record then it will
prompt an error.

Can I do this using Macro's only or do I need coding?

That's my plan but don't know where to begin. Any help would be grateful.

Thanks

 

[Arvin Meyer [MVP]]

Have a look at the third option in the combobox wizard.

 

[Joe]

Hi Arvin,

That was my initial set up. I had macro linked to the combo box to open the
form. However, there are around 98K loans in the main table. They are all
unique and the users began complaining about the the drop down being to slow.
The request came from the user to have a search button instead of a drop
down.

Thanks

 

[Joe]

Hi Tom,

The IT Dept was working on creating the database in SQL since it got so big
in a matter of months. However, the project was put on hold since we been
"downsizing".

I'll look into your links and see what I can do in the meantime.

Thanks

 

[Tom Wickerath]

Hi Joe,

Not to worry....you can still move the data to SQL Server (or Oracle, or
Sybase, etc.) and have your "front-end" application in Microsoft Access. In
fact, such a setup is very common.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Joe,

With 98 K records in your main table, it's time for you to consider an
alternate strategy. Instead of opening a form bound to such a large
recordset, a more network friendly method (ie. less data transfer over the
network) is to open an unbound search form. This form can include a text box
for the user to enter an account number, along with various other controls to
enter additional search criteria. Additional search criteria might include
text boxes to enter a range of dates, a combo box for a lookup table (but not
for hundreds of records), perhaps a multi-select list box, etc.

Implementing such a technique will require that you learn some VBA code, but
if you are willing to dive in and try, I think you'll be amazed at how
powerful of a search form you are able to create. Yes, it can seem a bit
daunting at first to get knee deep into code, but it is do-able, if you take
it one step at a time (or, as another frequent poster to this newsgroup likes
to say, "Build a little, Test a little"). Here are some QBF samples that you
can download and play with:

http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip

http://www.accessmvp.com/TWickerath/downloads/elements.zip

http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

The first one, Custom Dialog box, allows one to use a form to supply
parameters to a query. This is the easiest to build, but not as powerful as
the other two. The second example, Elements, demonstrates the use of a
multi-select list box that allows the user to select one or more elements
from the periodic table (hey, I'm a Chemist, so this seemed like a fitting
example). However, this sample does not include functionality that allows one
to open a particular record for editing. I purposely kept this sample as
simple as possible, so it only demonstrates how to use VBA code to iterate
the .ItemsSelected property of a listbox.

The Chap08QBF sample is an enhanced version of a QBF (Query by Form) sample
that is published in Chapter 8 of Access 2000 Power Programming, written by
F. Scott Barker. This example builds on the Elements example, and allows one
to open a record for editing, by double-clicking the record in the subform.

Finally, I have one more sample currently available on the Seattle Access
web site. This sample includes a Word document and a modified copy of the
Northwind sample database, to demonstrate the QBF technique:

http://www.seattleaccess.org/downloads.htm
(Look for the Feb. 12, 2008 download).

To help get your VBA skills kick-started, you can try out the download for
Jan/Feb 2007 "DAO - Back To Basics Compilation" from the same page. Also,
Access MVP Crystal has a great set of tutorials available:

http://www.accessmvp.com/Strive4Peace/Index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Klatuu]

Joe,
Here is a link that deals specifically with your problem.

http://allenbrowne.com/ser-32.html

Also, be sure you have an idex on the field you are wanting to search on.