Help with Lookup field

[Troogroove]

Hi all,

It is along since i have used access for anything and I am having problmes getting it to do what i want it to do.

I have 2 tables one which hold the data needed and the other is being used to hold a list of products with prices.

In my main talbe in which the data will be entered into I have a drop down so the product can be selected, once it is selcted i want the next field filled in automaticaly with the price. The look up looks at my products table which hold the price too. I tried with setting the look up based on a query but it get errors about using the same table

Anyone help me???

Thankyou for looking

 

[alastair69]

can you post a sample and i will look at it for you.

 

[Troogroove]

Here are my two tables (cut down a Little)

Table1

PK: Record number
Case number
Product
Cost

Table2
PK: Product ID
Cost

Product ID and Product are linked (one to many)
I use a drop down in table1 on product to select the product and once it has been selected i then want the cost from table2 to be enter into Cost on table1.

I am also creating a Form to aid the data input.

If this is not enough info please let me know

Many Thanks

 

[neileg]

Table level lookups are more trouble than they are worth. Do the lookup in you form or the underlying query and you'll manage a lot better.

 

[Troogroove]

Table level lookups are more trouble than they are worth. Do the lookup in you form or the underlying query and you'll manage a lot better.

Ive had tried it in the form but it either gives me errors or returns 0.00

heres the SQL
"SELECT tbl_Master_Records.[Product-Lookup], tbl_MI_Products.[Cost (Inc Vat)]
FROM tbl_MI_Products INNER JOIN tbl_Master_Records ON (tbl_MI_Products.[Product Identifier] = tbl_Master_Records.[Product-Lookup]) AND (tbl_MI_Products.[Cost (Inc Vat)] = tbl_Master_Records.Cost);"

As to doing it another way i cannot remember but any examples, tutorials etc are always welcome.

Thanks

 

[RuralGuy]

Here's an article you might want to read. The Evils of Lookup Fields in Tables

 

[Troogroove]

Here's an article you might want to read. The Evils of Lookup Fields in Tables

I have read that and digested it, so now what alternatives are there to achive the same result?

 

[RuralGuy]

As Neil stated, put your lookup's on a form rather than in a table. It has been my experience that the Lookup fields just confuse the programmer by hiding what is really in the field. Once you have converted the LookUp field to a TextBox and know what is in the field, then using a join in your query should work just fine.

 

[Troogroove]

As Neil stated, put your lookup's on a form rather than in a table. It has been my experience that the Lookup fields just confuse the programmer by hiding what is really in the field. Once you have converted the LookUp field to a TextBox and know what is in the field, then using a join in your query should work just fine.

This is the SQL i am using at present but it will not function as i think it should. Again this is another skill i have not used for many years and I cannot remember my join function. Be it an inner or outer.

SELECT tbl_MI_Products.[Cost (Inc Vat)] FROM tbl_MI_Products WHERE tbl_Master_Records.[Product-Lookup] = tbl_MI_Products.[Product Identifier];

 

[RuralGuy]

Have you tried using the query builder to refine your query?

 

[Pat Hartman]

You have two tablenames in the where clause but only one in the from clause. Add the second table to the from clause and specify the proper join type.

The easiest way to build SQL, is by using the QBE. Just add both tables, graphically draw the join line and select the columns you want from each table.

 

[Troogroove]

I resolved the problem by using some VB, it does exactly what i need it to do. It may not be the best solution in the world but its a start.

Here's what I did:

Dim varCost As Variant
    ' Lookup the Cost of an item from the table MI_Products based on what is selected in Product_Lookup field
    varCost = DLookup("[Cost (Inc Vat)]", "tbl_MI_Products", "Product_Lookup =[Product Identifier]  ")
    ' If the value is not NULL then put the returned value into the Cost field
    If (Not IsNull(varCost)) Then Me![Cost] = varCost