search form

[Guest]

I'm using Allen Brown's search form with two tables:

1) "productTable"
2) a linked "referenceIndex" table.

The "referenceIndex" table can have many records with a 'reference number'
linked to one record in the "productTable".

When I do the search with Allen Brown's search form, the form shows
duplications on the 'product number', as many as the number of references in
the "referenceIndex" table.

How can I display only one (1) instance of the same record (from
"productTable") and only the 'reference number' (from the "referenceIndex") I
typed in the criteria instead of this duplications?

Thanks!

 

[Allen Browne]

You need to create a query that gives you the kind of results you want to
see, and make that query the RecordSource for your form.

In query design, open the Properties box (View menu.)
In the properties of the query, set Unique Values to Yes.
This will generate only one of each record (as long as you don't include
fields where the values are different.)

 

[Guest]

The RecordSource is a query.

I set the uniqueValue to Yes.

I have to include the "reference" index because there are many "names" for
the same product number.

The "reference" index is going to have different values all the time.

Is there a way to go around this?

Thank you!

 

[Allen Browne]

What results are you expecting to see in the form?

You want it to show the different "reference" index values on each row in
the search results? But you don't want to see the product repeating on
different rows? But it's not possible to show the reference number on each
row without repeating the product number?

 

[Guest]

I don't need to see the rows showing the 'reference' on the results.
I just want to be able to search for the products that have that 'reference'.
I need to see the product number, description, quantity and location.
Thank you!

 

[Allen Browne]

Ah, okay. Couple of options.

A. Reassign the RecordSource of the form
================================
If the user does not enter anything into the box for reference, just use the
main table as the form's RecordSource. (That's easy.)

If the user does enter a value to search for the reference, set the search
form's RecordSource to an INNER JOIN statement that uses both tables.
Include DISTINCT if there could be multiple entries in the related table
with that reference value. Now you won't see duplicates.

There is an example of how to do that in this article:
http://allenbrowne.com/ser-28.html
The article refers to a subform (which you don't have), but it filters the
main form by setting its RecordSource, so that's the technique.

B. Use a subquery in the Filter
=======================
You could leave the form's RecordSource as just the main table, and use a
subquery in the Filter string, e.g.:

strWhere = "EXISTS (SELECT SubID FROM ReferenceIndex WHERE
(ReferenceIndex.ProductID = ProductTable.ProductID) AND
(ReferenceIndex.[Reference number] = " & Me.txtFilterRef & "))"

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066