Simple search functionality

[davidraw]

I have looked all over for this but cannot find it. It should be
very easy!



I have a products table that contains just barcode (primary key) and
description.



I want to create a form that allows a user to enter barcode and/or
description, press a 'Find' button and perform a search of the table to
display all matches.



I.e perform SELECT * FROM PRODUCTS WHERE BARCODE LIKE 'contents of
field1' AND DESCRIPTION LIKE 'contents for field2'. A blank form field
will be treated as a wildcard.



There are 3 outomes:

1. One record is foun - display it

2. More than 1 record is found, display list and allow the user to
select the best match.

3. No record is found, display a form to allow it to be added.



The user will be non-techie, so only the only options available should
be a form with 2 empty fields and the search button.



Easy, huh? I have *18* years programming RDB experience, but no VB or
other Microsoft programming experience. I have done this 100's of times
with Oracle, mySQL, Informix, Access under JDBC control etc etc.



Where do I start to look? It seems that uSoft help is too simple, and
other posting are too complex for someone who has no idea how to link an
action to a button and use FindRecord etc.



Thanks,



- David

 

[Matt]

Hey David,

Took me ages too...

Try this:

- Have a search form, with 2 unbound text boxes (BarCode and
Description) and a list box.

- And a "Find" button...

- The list box is based on a query.

- The query selects both fields in the relevant table, each field with
criteria something like this:
Like [Forms]![SearchForm]![BarCodeTextBox] & "*"

- Put a LISTBOXNAME.REQUERY statement in the ON_OPEN subroutine of the
form to populate the list box with all records when the form opens.

- Put the same statement in the code behind a "Find Now" button to
narrow down the search results based on what the user has entered in
either (or both) field.

- The DBLCLICK sub of the list box can then open a "Detail" form or
such, using a DOCMD.OPENFORM ... Use a WHERE clause in the appropriate
place there to base the key field of the form on the value of the list
box (ie, the record that was double-clicked...).

- To make the last bit work, make sure that the BOUND COLUMN of the
list box references the field that is also the key field of the detail
form - this is the value you want to pass...

HTH

Matt