J
Jim Brooks
Help required to filter combo from a textbox value?
I am new to Access and I really need some expert advice. I have created a
Deliveries database which contains a Customers table, Products table,
Deliveries table and a Delivery Details table. Each customer has products
that are specific to that customer only. I have created a Deliveries form
based on the Deliveries table with a sub form Delivery Details sub form based
on the Delivery details table. On the Deliveries form is a combo box called
CustomersCombo that contains CustomerID and CompanyName and I have a text box
called CustomerID which shows the selected CustomerID (which is the primary
key) for the selection made in CustomersCombo.
The Deliveries form contains Delivery Details subform which contains a
Combo box named ProductsCombo which draws 4 columns of information from the
Products Table those being ProductID, ProductDescription, UnitPrice and
CompanyID ,the ProductID being the primary key for the Products table. The
two forms are linked by a DeliveryID in the master and child properties for
the form. The ProductsCombo works great but my problem is that it shows every
product for all customers so is it possible to filter the results so that
only products for the customer in the CustomerID textbox on the Deliveries
form are displayed. I have tried to utilise several examples that are on this
site but to no avail, the nearest I came to solving my problem was to filter
the ProductsCombo but all it showed was the products for the first customer
in the Deliveries Table. I am only just learning Access so any help given in
the simplest of terms would be greatly appreciated. Below are the table
details and the combo boxes properties.
Many thanks in advance
Jim Brooks
Customer Table
CustomerID Primary Key AutoNumber
CompanyName
Address
City
PostCode
Etc.
Products Table
ProductID Primary Key AutoNumber
ProductDescription
UnitPrice
CustomerID
Deliveries Table
DeliveryID Primary Key AutoNumber
CustomerID
DeliveryDate
PurchaseOrderNumber
Delivery Address
Delivery Details Table
OrderLineNumber Primary Key AutoNumber
DeliveryID
ProductID
Quantity
UnitPrice
The CustomersCombo’s properties are: Control Source CustomerID
Row Source Select tblCustomers.CustomerID,
tblCustomers.CompanyName FROM tblCustomers ORDER BY tblCustomers.CompanyName;
The ProductsCombo Control Source ProductID
Row Source Select
tblProducts.ProductID,tblProducts.ProductDescription,
tblProducts.UnitPrice,tblProducts.CustomerID From tblProducts ORDER BY
tblProducts.CustomerID;
I am new to Access and I really need some expert advice. I have created a
Deliveries database which contains a Customers table, Products table,
Deliveries table and a Delivery Details table. Each customer has products
that are specific to that customer only. I have created a Deliveries form
based on the Deliveries table with a sub form Delivery Details sub form based
on the Delivery details table. On the Deliveries form is a combo box called
CustomersCombo that contains CustomerID and CompanyName and I have a text box
called CustomerID which shows the selected CustomerID (which is the primary
key) for the selection made in CustomersCombo.
The Deliveries form contains Delivery Details subform which contains a
Combo box named ProductsCombo which draws 4 columns of information from the
Products Table those being ProductID, ProductDescription, UnitPrice and
CompanyID ,the ProductID being the primary key for the Products table. The
two forms are linked by a DeliveryID in the master and child properties for
the form. The ProductsCombo works great but my problem is that it shows every
product for all customers so is it possible to filter the results so that
only products for the customer in the CustomerID textbox on the Deliveries
form are displayed. I have tried to utilise several examples that are on this
site but to no avail, the nearest I came to solving my problem was to filter
the ProductsCombo but all it showed was the products for the first customer
in the Deliveries Table. I am only just learning Access so any help given in
the simplest of terms would be greatly appreciated. Below are the table
details and the combo boxes properties.
Many thanks in advance
Jim Brooks
Customer Table
CustomerID Primary Key AutoNumber
CompanyName
Address
City
PostCode
Etc.
Products Table
ProductID Primary Key AutoNumber
ProductDescription
UnitPrice
CustomerID
Deliveries Table
DeliveryID Primary Key AutoNumber
CustomerID
DeliveryDate
PurchaseOrderNumber
Delivery Address
Delivery Details Table
OrderLineNumber Primary Key AutoNumber
DeliveryID
ProductID
Quantity
UnitPrice
The CustomersCombo’s properties are: Control Source CustomerID
Row Source Select tblCustomers.CustomerID,
tblCustomers.CompanyName FROM tblCustomers ORDER BY tblCustomers.CompanyName;
The ProductsCombo Control Source ProductID
Row Source Select
tblProducts.ProductID,tblProducts.ProductDescription,
tblProducts.UnitPrice,tblProducts.CustomerID From tblProducts ORDER BY
tblProducts.CustomerID;