At a Loss - Need some code help please

[Philip Martin]

Hi,

I'm trying to modify the standard template Inventory Control. Basically I'm
setting up my own little business and this will do the job of ordering and
stock control, if I can get it to behave the way I want it to. I've already
UK'ised it and removed the fields and code I don't need, but am stuck on a
orders modification.

I've added the fields ProductDescription and UnitPrice to the Product table
and similarly added these fields to the Inventory Transactions table.

I've added the code:


Private Sub Product_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
Me![ProductDescription] = Me![ProductID].Column(3)

End Sub

to the Purchase Orders Subform, so that after using the Combo Box tied to
ProductID the UnitPrice field and the ProductDescription field are
automatically filled in. At least that is the plan! The Unit price field is
working fine but the other isn't. I've tried moving the fields in the table
around and also in the SQL lookup statement but just cant get it to work.
The fields from 0 to 2 will copy into the data but anything greater than
column 2 just wont work!!

Also, is there a way to modify the Combo box. At the moment it only shows
one selected field, the ProductName. Although there is a field for the
ProductDescription I would like to have this item displayed in the drop down
as well.

This may seem a bit cack handed, but the ProductName fields will be part
numbers for the items to be ordered while the ProductDescription will be a
text description and I need these both on the sub form and on the physical
order.

TIA,

Philip Martin.

 

[Arvin Meyer]

Hi Philip,

It is probably as simple as not having changed the column count in the
property sheet. The column count property must be the same as the number of
columns you want returned to the combo box. When your done with that, make
sure you have set a column width (even if it is 0) for each column you'd
like to reference.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I'm trying to modify the standard template Inventory Control. Basically I'm
setting up my own little business and this will do the job of ordering and
stock control, if I can get it to behave the way I want it to. I've already
UK'ised it and removed the fields and code I don't need, but am stuck on a
orders modification.

I've added the fields ProductDescription and UnitPrice to the Product table
and similarly added these fields to the Inventory Transactions table.

I've added the code:


Private Sub Product_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
Me![ProductDescription] = Me![ProductID].Column(3)

End Sub

to the Purchase Orders Subform, so that after using the Combo Box tied to
ProductID the UnitPrice field and the ProductDescription field are
automatically filled in. At least that is the plan! The Unit price field is
working fine but the other isn't. I've tried moving the fields in the table
around and also in the SQL lookup statement but just cant get it to work.
The fields from 0 to 2 will copy into the data but anything greater than
column 2 just wont work!!

Also, is there a way to modify the Combo box. At the moment it only shows
one selected field, the ProductName. Although there is a field for the
ProductDescription I would like to have this item displayed in the drop down
as well.

This may seem a bit cack handed, but the ProductName fields will be part
numbers for the items to be ordered while the ProductDescription will be a
text description and I need these both on the sub form and on the physical
order.

TIA,

Philip Martin.

 

[Philip Martin]

Hi Philip,

It is probably as simple as not having changed the column count in the
property sheet. The column count property must be the same as the number of
columns you want returned to the combo box. When your done with that, make
sure you have set a column width (even if it is 0) for each column you'd
like to reference.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Thank you Arvin,

Set the column count in the property sheet and the widths and all works
fine, and I understand why!

))

Philip Martin.

 

[Van T. Dinh]

"I've added the fields ProductDescription and UnitPrice to
the Product table and similarly added these fields to the
Inventory Transactions table."

I haven't got the full picture of your database but it
seems to me that you should store the ProductID (assuming
it is the PrimaryKey of your Product Table) as the
ForeignKey in your Inventory Table rather than the
ProductDescription (likely to be a free-text Field). This
way, the Tables are linked correctly using PK-FK and if
you need the ProductDescription, you can always work it
out from the value of the ForeignKey.

HTH
Van T. Dinh
MVP (Access)