Beginner @Access 2000 - Need Help with a subform

[TIMER]

I'm trying to develop a simple company price list. I have created a suppliers products table. But I want to use the suppliers products to make up our coded goods (A Bill of Quantities simply). But I'm caught in a certain area that I dont have any knowledge on.

I want to create a form / subform whereby I can create my company codes, add up the cost price and put mark on it.

I have attached a image of what I'm trying to do in excel. In excel all I do is enter the code and the descrition and price comes up in the cells automatically. I just enter the quantity I require and total (sum) it all up.

In the subform itself. I can't get the the rest of the text boxes to automatically update once I have entered the code in the combo box. I rekon I have to enter a event procedure. Can some
one help

 

[TIMER]

Attached is the excel image of what I'm tring to accomplish

 

[TIMER]

excel image

 

[edtab]

Create a combobox in your subform. The data source
should be the table that contains all your product info.
In the after update event, place the following code:

Private Sub cboProductCode_AfterUpdate()
Me.Description = Me.cboProductCode.Column(1)
Me.UnitCost = Me.cboProductCode.Column(2)
Me.Extension = Me.Quantity * Me.UnitCost
End Sub

This assumes that your combo box displays your fields
in the following order: Product Code, Description, UnitCost.

Try it and post back.

 

[apr pillai]

Put DLookup Functions in Description & Cost Price Text Boxe's Control Source Property.

=Dlookup("Description","TableName","Code = " & [cboCode])

=Dlookup("CostPrice","TableName","Code = " & [cboCode])

which will get the values directly from the underlying Table. If the extended price formula shows error try modifying it with = [Qty]*Val([CostPrice]).

[cboCode] is the ComboBox Name from which the Code is picked for Lookup.

visity my site http://msaccessblog.blogspot.com/ for tips and tricks on Designing Forms.