Linked fields in Forms

[Kathie1101]

Hi

Im hoping somebody can help me. I am new at using Access (using Access 2000) and I am setting up a database but I am having a problem with a form which shows the materials and cost to produce a product. The form consists of the following fields: Card Code, Card Name, Card Type, Card Occassion, then 10 fields named Materials 1-10, with 10 fields for the Unit Price of each material. I also have fields named: cost to produce, selling price & profit. I have used combo boxes so that I can select the materials used from a drop down list, but I need the form to automatically insert the unit price for the corrosponding material used. I have no idea how to do this - help please!

The way I have my database set up so far is:

A table (named stock) showing the materials, stock, unit price before discount, and discount.

I then have a query (named stock details extended) which calculates the unit price after discount.

I then produced my form (named Cards Made) based on the information in the above query.

So, basically I need to know how when a material is selected in my form, do I get it so it enters in the next field the corrosponding unit price for that material.

I hope somebody can understand what I am asking, and I apologise if I have given too much/too little information. I would really appreciate a response if possible that explains it simply, as like I say I am new to using Access.

TIA

Kathie

 

[jgoodman00]

You can use a DLookup to do this:

In the control source of the UnitPrice control, set it as the following:
= DLookup("[UnitPrice]","[Stock]","[Material] = " & Forms![Cards Made]![cboMaterial])

This should display the Unit price for the selected unit. You will have to check the field names I used are correct & adjust to suit.

James Goodman

 

[Kathie1101]

Thanks for the help, but unfortunately I can't get it to work, no matter what I try.

I have now made my database simpler and tried it again, but still no joy. I have no idea what I am doing wrong. Originally my form was run from a query, but I now have it run from a table. This is what I have in my database now:

Table
Name:Suppliers
Field: SupplierID which is an auto number and primary key
Field:Company Name which is a text box

Table
Nameroducts
Field: ProductID which is an auto number and primary key
Field: ProductCode which is a text box
Field: ProductName which is a text box
Field: SupplierID which is a number box
Field: UnitPrice which is a currency box

Table
Name:CardStyle
Field: CardID which is an auto number and primary key
Field: CardType which is a text box
Field: CardOccassion which is a text box

The ProductID field in table Products is linked to the SupplierID field in table Suppliers with a relationship of one to many.

I then have the the following form:
Name:Cards
Field: CardCode Unbound text box to manually enter the code
Field: CardType Combobox which is taking the info from the CardStyle table
Field: CardOccassion Combobox which is taking the info from the CardStyle table
Field: ProductName Combobox which is taking its info from the Products table (bound to column 1 and Column Count is 2, so in drop down list it shows the productname and the unitprice)
Field: UnitPrice Textbox ??????????????

I can get all the combo boxes in the form to bring up the information I need, but it is just the UnitPrice field that I cannot do - It needs to autofill when the ProductName is selected. This is what I had typed into the control source of the text box:
=DLookUp("[UnitPrice]","[ProductsCrafts]","[ProductName] = " & [Forms]![Cards]![cboProductName])

I even tried replacing the last bit with [cboUnitPrice])

I am really stuck, so any help would be appreciated, and I am sorry to be a pain.

Can anybody suggest a really good book that gives clear step by step instructions for using Access? I have borrowed 4 from the library but they all just mention basic stuff, none of them cover things like this.

TIA

Kathie

 

[rajeessh]

=DLookUp("[UnitPrice]","[Products]","[ProductName] = '" & [Forms]![Cards]![cboProductName] & "'&quot

Is the bound column of the cboProductName is ProductCode
or ProductName.
If that is ProductName above is ok else try lower one

=DLookUp("[UnitPrice]","[Products]","[ProductCode] = '" & [Forms]![Cards]![cboProductName] & "'&quot


Get to me if i am wrong

 

[Kathie1101]

Thanks for the help, I couldn't get it to work though. However after reading other posts and refering to the Microsoft Access help I managed to do it this way:

In the control source of the text box UnitPrice I put:
=[Forms]![Cards]![ProductName].[Column](1) And this seemed to do the trick. I don't know what I was doing wrong for your suggestions not to work, but at least it is O.K now. Thanks again for taking the time to help.

Kathie

 

[rajeessh]

The Fact is that the bound column was not ProdcutName but
some othere
The first column was product name

 

[rajeessh]

The Fact is that the bound column was not ProdcutName but
some othere
The second column was product name