Kristin:
Firstly the following assumes that you have a table Suppliers with numeric
primary key SupplierID, a table Products with numeric primary key ProductID
and text column Product, and a table ProductSuppliers with two numeric
foreign key columns SuupplierID and ProductID.
To search by product I'd suggest using a multi-select list box, so you can
select one or more products at a time. The results would then be those
suppliers who supply any one of the products selected. You could either put
the list box in the bound suppliers form or in a separate unbound dialogue
form. The following is for the latter, which I think will be less confusing
to the user than trying to combine the search facility with the data display
and editing facilities of a bound form.
First create a form based on the Suppliers table and embed a subform based
on ProductSuppliers or a query joining ProductSuppliers and products as I
described in my first reply. I've assumed below that the parent form is
called frmSuppliers.
Create an unbound dialogue form and add a list box to it. Set the list
box's properties up like so:
Name: lstProducts
RowSource: SELECT ProductID, Product FROM Products ORDER BY Product;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
MultiSelect: Simple
If you are using inches rather than metric units Access will automatically
convert the above ColumnWidths values to inches.
Add a command button to the form for opening the suppliers form, and out the
following code in its Click event procedure:
Const conMESSAGE = "No products selected."
Dim ctrl As Control
Dim varItem As Variant
Dim strProductIDList As String
Dim strCriteria As String
Set ctrl = Me.lstProducts
' loop through list box's ItemsSelected
' collection and build list of ProductIDs
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strProductIDList = strProductIDList & _
"," & ctrl.ItemData(varItem)
Next varItem
' remove leading comma
strProductIDList = Mid(strProductIDList, 2)
' build criteria string for opening form
' with subquery to return all SupplierIDs
' of suppliers who supply any of selected products
strCriteria = "SupplierID IN(" & _
"SELECT Suppliers.SupplierID " & _
"FROM Suppliers INNER JOIN ProductSuppliers " & _
"ON Suppliers.SupplierID=ProductSuppliers.SupplierID " & _
"WHERE ProductID IN (" & strProductIDList & "))"
' open form filtered to relevant suppliers
DoCmd.OpenForm "frmSuppliers", WhereCondition:=strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
Add another button to the form for clearing the selections from the list,
and out the following in its Click event procedure:
Dim ctrl As Control
Dim n As Integer
Set ctrl = Me.lstProducts
' loop through all items in list box
' and de-select if selected
For n = 0 To ctrl.ListCount - 1
ctrl.Selected(n) = False
Next n
You can select any number of items from the list box simply by clicking them
in succession. When the button is then clicked the frmSuppliers form will
open, filtered to show only those suppliers who supply any of the selected
products. The subform will still show all products selected by each
supplier, included in which will be at least one of those you selected in the
dialogue form.
If you implement the above carefully it should work without any trouble, but
if you do have any problems feel free to mail me at:
kenwsheridan<at>yahoo<dot>co<dot>uk
and I can send it to you as a simple little .mdb file.
Good luck,
Ken Sheridan
Stafford, England
