Prev: Combo Box
Next: What is the extra column added to a form datasheet by a control
From: alanb on 19 May 2010 05:14
Hi, I am trying to create a database for ordering materials. I have a form
with two tabs. The first tab is for order details and the second tab is for
order items (with a subfrm to list products for that order).
On tab one I select the supplier for the order. When I go to tab two, to fill
out the order items I have a combo box for 'Product code' and I want the
combo box to only show products for the supplier I selected on tab one.
The products are all in the same table and the column for 'supplier name' is
linked to 'supplier name' in another table for suppliers details.
In the combo box I have tried to create a query to only show products for
that supplier but it does not work for each record, It only works for one
record and does not seem to change when I change the supplier on tab one. I
also tried to set the criteria as 'please enter supplier name' (this is not
ideal put I did not know what else to try), but this works only once. It
works for the first product I enter and that's it, it does not change until I
close and re open.

I hope this makes sense, and hope somebody can help.
Many Thanks, Alan.

From: alanb on 19 May 2010 06:58
My main tables/fields are;
TblOrders
AB ref. Number , Order date, contract number, Supplier Name, job details, etc.

TblSuppliers
Supplier Name, address, tel, fax, email, contact name, etc.
TblPriceList
Supplier Name, Product Code, Item description, unit, Price, cost code, etc.
TblWorkItems
Work item ID, AB ref. Number, product code, item description, quantity, unit,
price, extended price.

My forms are as follows;
Frmorders
Tab one, Order Details, included button to print order (report)
Tab two, subfrmWorkItems (Linked with AB ref number), user to enter products
required.

Frm Suppliers
Tab one, Supplier Details
Tab two, subfrmPriceList (linked to supplierName)
Tab three, subfrmOrderHistory

From: BruceM via AccessMonster.com on 19 May 2010 08:38
I would use an unchanging number for SupplierID. Names change.

Anyhow, in the After Update event of the supplier combo box, something like:

Dim strSQL as String

strSQL = "SELECT * FROM tblPriceList " & _
"WHERE [Supplier Name] = " " " & Me.SupplierName & " " " "

Me.sfrmItems.Form.cboProduct.RowSource = strSQL

This assumes a subform control (the "box" containing the subform) named
sfrmItems, and that the subform control's source object (form) has a combo
box named cboProduct.

I have used generic SQL, with an asterisk, but you probably want to use the
fields in your current Row Source query.

You probably want similar code in the form's Current event, after testing
whether a supplier has been selected, so that the combo box list shows a
filtered list of products when you revisit an existing record.


alanb wrote:
>My main tables/fields are;
>TblOrders
>AB ref. Number , Order date, contract number, Supplier Name, job details, etc.
>
>TblSuppliers
>Supplier Name, address, tel, fax, email, contact name, etc.
>TblPriceList
>Supplier Name, Product Code, Item description, unit, Price, cost code, etc.
>TblWorkItems
>Work item ID, AB ref. Number, product code, item description, quantity, unit,
>price, extended price.
>
>My forms are as follows;
>Frmorders
>Tab one, Order Details, included button to print order (report)
>Tab two, subfrmWorkItems (Linked with AB ref number), user to enter products
>required.
>
>Frm Suppliers
>Tab one, Supplier Details
>Tab two, subfrmPriceList (linked to supplierName)
>Tab three, subfrmOrderHistory

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1

From: Daryl S on 19 May 2010 09:19
Alanb -

You need to set the row source for the combo box on tab 2 to be a query like
this (use your subform name that is on the first tab, or if not in a subform,
just reference the field):

SELECT [Product Code], [Item description]
FROM TblPriceList
WHERE [Supplier Name] = Forms![Frm
Suppliers].[<SubformNameOnTab1>].Form.[Supplier Name]

You may need to requery this combobox in the AfterUpdate event of the
Supplier Name on tab 1.

If you have problems, post your SQL or the code that is not working.

--
Daryl S


"alanb" wrote:

> My main tables/fields are;
> TblOrders
> AB ref. Number , Order date, contract number, Supplier Name, job details, etc.
>
> TblSuppliers
> Supplier Name, address, tel, fax, email, contact name, etc.
> TblPriceList
> Supplier Name, Product Code, Item description, unit, Price, cost code, etc.
> TblWorkItems
> Work item ID, AB ref. Number, product code, item description, quantity, unit,
> price, extended price.
>
> My forms are as follows;
> Frmorders
> Tab one, Order Details, included button to print order (report)
> Tab two, subfrmWorkItems (Linked with AB ref number), user to enter products
> required.
>
> Frm Suppliers
> Tab one, Supplier Details
> Tab two, subfrmPriceList (linked to supplierName)
> Tab three, subfrmOrderHistory
>
> .
>
From: BruceM via AccessMonster.com on 19 May 2010 10:57
That is another way to do it, but "need to" implies it is the only way, which
is not the case. With your approach, requerying the combo box is the only
code, but when I can I prefer to avoid form and control references in queries
if for no other reason than that the query is specific to the form, and can't
be worked on unless the criteria are temporarily changed.

Also, for reasons I mentioned it would probably be best to use the same code
(whichever approach is used) in the form's Current event.

Daryl S wrote:
>Alanb -
>
>You need to set the row source for the combo box on tab 2 to be a query like
>this (use your subform name that is on the first tab, or if not in a subform,
>just reference the field):
>
>SELECT [Product Code], [Item description]
>FROM TblPriceList
>WHERE [Supplier Name] = Forms![Frm
>Suppliers].[<SubformNameOnTab1>].Form.[Supplier Name]
>
>You may need to requery this combobox in the AfterUpdate event of the
>Supplier Name on tab 1.
>
>If you have problems, post your SQL or the code that is not working.
>
>> My main tables/fields are;
>> TblOrders
>[quoted text clipped - 20 lines]
>>
>> .

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1

 |  Next  |  Last
Pages: 1 2
Prev: Combo Box
Next: What is the extra column added to a form datasheet by a control