=> cboSupplierName to determine cboCustomerName

[Rhonda Fischer]

Hello,

I have a continuous form based on my Deliveries Table;
containing the fields: supplierID, customerID, date, qty.

I have a combo list of Suppliers which is to determine
the list of Customers displayed in a combo box on the
same line. For the first entry all works as intended the
user selects a Supplier and then a Customer based on this
supplier. However for subsequent entries the list of
Customers is still based on the very first Supplier
selected on the line above.

Following are the property values of the two combo
boxes. Do I need to somehow set the focus on the last
selected cboSupplierName or call an event from it's
lost focus routine to somehow refresh?

Any ideas would be terrific.

Thank you very much.
Rhonda



cboSupplierName:
===============
Control Source: SupplierID
Row Source: qryListSuppliers
SELECT DISTINCT [tblSupplier].
[supplierName], [tblSupplier].[ID]
FROM tblSupplier
ORDER BY [tblSupplier].[supplierName];
Column Bound: 2
Column Widths: 1cm;0cm

cboCustomerName:
===============
Control Source: CustomerID
Row Source: qryCustomerForSupplierSelected
SELECT tblCustomer.customerName,
tblCustomer.ID
FROM (tblSupplierCustomer INNER JOIN tblSupplier ON
tblSupplierCustomer.supplierID = tblSupplier.ID) INNER
JOIN tblCustomer ON tblSupplierCustomer.customerID =
tblCustomer.ID
WHERE (((tblSupplier.ID)=[Forms]![frmDeliveries]!
[cboSupplierName]))
ORDER BY tblCustomer.customerName;

Column Bound: 2
Column Widths: 1cm;0cm

 

[John Viescas]

Well, the simple answer is you need to Requery the second combo box in both
the Form Current event and After Update of the first combo box. But when
you do that, you'll find that the CustomerID combo box goes blank on rows
where the customer isn't in the list for the supplier on the current row.
One solution is to include the Customers table in the underlying record
source and add a text box that overlays the display area of the CustomerID
combo box to display the customer info regardless of the filter on the combo
box. Lock the text box so the user can't update it, take it out of the tab
order, and in the Enter event of the text box, set focus on the underlying
combo box.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)