Filter a subform combo with a text box value on the main form

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;
 
B

Beetle

Looks like you need to add a where clause to the row source of your
ProductsCombo;

SELECT tblProducts.ProductID,tblProducts.ProductDescription,
tblProducts.UnitPrice,tblProducts.CustomerID FROM tblProducts WHERE
tblProducts.CustomerID = [Forms]![frmDeliveries]![CustomerCombo] ORDER BY
tblProducts.CustomerID;


Then in the AfterUdate event of your CustomerCombo on the main form, you
need to requery the ProductsCombo on the subform;

Me![NameOfYourSubformControl].Form![ProductsCombo].Requery

Keep in mind with the above syntax that you refer to the name of the control
that contains the subform (which may or may not be the same as the name of
the subform itself), then the control on the subform.

You may all so want to requery the ProductsCombo in the OnCurrent event of
your main form also.

(modify field and control names to fit your db)

HTH
 
J

Jim Brooks

Hi Beetle thanks for the quick response can you please clarify part of your
answer
in the statement "Me![NameOfYourSubformControl].Form!ProductsCombo].Requery"
is "NameOfYourSubformControl" the name of the subform Delivery Details
subform?
I have tried to get the ProductsCombo to show filtered Products but
Productscombo is blank and all my previous records are blank on the subform
but all correct on the Delivery Details table. sorry for being a pain any
other suggestions to solve this would be greatly appreciated.
thanks in advance
jim brooks

--
Don''''''''t put off till tomorrow something you could do today because if
you do it today and enjoy it you can do it again tomorrow


Beetle said:
Looks like you need to add a where clause to the row source of your
ProductsCombo;

SELECT tblProducts.ProductID,tblProducts.ProductDescription,
tblProducts.UnitPrice,tblProducts.CustomerID FROM tblProducts WHERE
tblProducts.CustomerID = [Forms]![frmDeliveries]![CustomerCombo] ORDER BY
tblProducts.CustomerID;


Then in the AfterUdate event of your CustomerCombo on the main form, you
need to requery the ProductsCombo on the subform;

Me![NameOfYourSubformControl].Form![ProductsCombo].Requery

Keep in mind with the above syntax that you refer to the name of the control
that contains the subform (which may or may not be the same as the name of
the subform itself), then the control on the subform.

You may all so want to requery the ProductsCombo in the OnCurrent event of
your main form also.

(modify field and control names to fit your db)

HTH

--
_________

Sean Bailey


Jim Brooks said:
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;
 
B

Beetle

When you add a subform to a form, it is contained within a subform control
(basically the box, or window, that the subform appears in). The name of the
subform control is usually, but not necessarily, the same as the name of the
subform itself. Objects on forms (text boxes, combo boxes, command buttons,
etc.) are called controls (not fields, fields are in tables). When you want
to refer to a control on a subform, you first refer to the subform control,
then the form within the control (hence the .Form! after the subform control
name), then the control on the subform (hopefully this is making sense). So,
let's say you have a subform called sfrmDeliveryDetails with a combo box
named ProductsCombo. Assuming that the subform control that holds it has the
same name, then the syntax would be;

Me![sfrmDeliveryDetails].Form![ProductsCombo]

However, that is not what is causing your products combo box to be blank. I
would say you have a problem with the rowsource SQL, or one of the objects
that the SQL refers to. Open the properties sheet for your ProductsCombo and
click the elipse next to the row source. This will open the query design
grid. Switch to SQL view, then copy and paste the SQL statement here. Myself,
or someone else here, should be able to help you determine the problem.

--
_________

Sean Bailey


Jim Brooks said:
Hi Beetle thanks for the quick response can you please clarify part of your
answer
in the statement "Me![NameOfYourSubformControl].Form!ProductsCombo].Requery"
is "NameOfYourSubformControl" the name of the subform Delivery Details
subform?
I have tried to get the ProductsCombo to show filtered Products but
Productscombo is blank and all my previous records are blank on the subform
but all correct on the Delivery Details table. sorry for being a pain any
other suggestions to solve this would be greatly appreciated.
thanks in advance
jim brooks

--
Don''''''''t put off till tomorrow something you could do today because if
you do it today and enjoy it you can do it again tomorrow


Beetle said:
Looks like you need to add a where clause to the row source of your
ProductsCombo;

SELECT tblProducts.ProductID,tblProducts.ProductDescription,
tblProducts.UnitPrice,tblProducts.CustomerID FROM tblProducts WHERE
tblProducts.CustomerID = [Forms]![frmDeliveries]![CustomerCombo] ORDER BY
tblProducts.CustomerID;


Then in the AfterUdate event of your CustomerCombo on the main form, you
need to requery the ProductsCombo on the subform;

Me![NameOfYourSubformControl].Form![ProductsCombo].Requery

Keep in mind with the above syntax that you refer to the name of the control
that contains the subform (which may or may not be the same as the name of
the subform itself), then the control on the subform.

You may all so want to requery the ProductsCombo in the OnCurrent event of
your main form also.

(modify field and control names to fit your db)

HTH

--
_________

Sean Bailey


Jim Brooks said:
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;
 
J

Jim Brooks

Hi Beetle followed all your advice and found a problem with spelling mistake
but it did not solve the problem so as I said in the original question could
I use the textbox containing the CustomerID. I changed the CustomerCombo in
the "where" part of the statement to CustomerID and it works perfectly on new
records .When I scroll through previous records the ProductsCombo is blank
but if I select the CustomersCombo and reselect the Customer name then the
details appear in the ProductsCombo is there somewhere a requery can be
inserted to solve this problem?
Thanks for spending time to explain the syntax I'm slowly getting there.
jim brooks
 
B

Beetle

Try adding a line to requery the products combo in the OnCurrent event of
your form also. This will fire whenever you change records.

Private Sub Form_Current()

Me![sfrmDeliveryDetails].Form![ProductsCombo].Requery

End Sub

HTH
 
J

Jim Brooks

Hi Beetle works perfect many thanks for your excellent information all the
best for the new year
jim brooks
--
Don''''''''t put off till tomorrow something you could do today because if
you do it today and enjoy it you can do it again tomorrow


Beetle said:
Try adding a line to requery the products combo in the OnCurrent event of
your form also. This will fire whenever you change records.

Private Sub Form_Current()

Me![sfrmDeliveryDetails].Form![ProductsCombo].Requery

End Sub

HTH


--
_________

Sean Bailey


Jim Brooks said:
Hi Beetle followed all your advice and found a problem with spelling mistake
but it did not solve the problem so as I said in the original question could
I use the textbox containing the CustomerID. I changed the CustomerCombo in
the "where" part of the statement to CustomerID and it works perfectly on new
records .When I scroll through previous records the ProductsCombo is blank
but if I select the CustomersCombo and reselect the Customer name then the
details appear in the ProductsCombo is there somewhere a requery can be
inserted to solve this problem?
Thanks for spending time to explain the syntax I'm slowly getting there.
jim brooks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top