search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an inventory database. The main form I will be working on is
"Products" with a subform "Inventory Transactions". I want to be able to be
on a form of product "412411" and be able to navigate to a different product.
How do I include a search box on my form to navigate from one product with
it's transactions to a certain product and it's transactions?
 
Add an unbound combo box, cboFindProduct say, to the Products form with a
RowSource property like this:

SELECT ProductNumber FROM Products ORDER BY ProductNumber;

Put code along these lines in its AfterUpdate event procedure:

Dim rst As Object

Set rst = Me.Recordset.Clone

rst.FindFirst "ProductNumber = " & cboFindProduct
Me.Bookmark = rst.Bookmark

This assumes ProductNumber is a number data type. If it were text you'd
wrap the value in quotes characters:

rst.FindFirst "ProductNumber = """ & cboFindProduct & """"

To keep the combo box in sync with the form's current record if you navigate
via the navigation buttons or keyboard put the following code in the form's
Current event procedure:

Me.cboFindProduct = Me.ProductNumber

However, if there is a text description of the product in the Products table
you might prefer to search on the description rather than the number, in
which case you'd set the combo box up like so:

RowSource:

SELECT ProductNumber, ProductDescription FROM Products ORDER BY
ProductDescription;

Set up the combo box's other properties like so:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, ProductNumber, column, so only the
name shows)

Ken Sheridan
Stafford, England
 
Back
Top