Autofill information from a table

[laviecharmee]

I am having a hard time with my database (Access 2003) and I hope someone can help. What I doing is creating an order form, and what I would like to have happen is when a product ID is entered, the relating information to that product will self populate. I did some looking around online and in various books and was able to come up with a script. I manipulated it and tried it and it is close but not quite. Instead of populating the information that goes along with the specific product number, it just brings up the information relating to the first product in the product table.
(the product name, cost and weight comes up for the first product in my product table regardless of what product number i type in)

This is the code that I found and I have placed it in the "after update" field of the "ProdID" field in my form.

 
Private Sub ProdID_AfterUpdate()
Dim varProdName, varProdCost, varWeight As Variant
    varProdName = DLookup("ProdName", "Products", "ProdID =[ProdID] ")
    varProdCost = DLookup("ProdCost", "Products", "ProdID =[ProdID] ")
    varWeight = DLookup("weight", "Products", "ProdID =[ProdID] ")
 
    If (Not IsNull(varProdName)) Then Me![ProdName] = varProdName
    If (Not IsNull(varProdCost)) Then Me![ProdCost] = varProdCost
    If (Not IsNull(varWeight)) Then Me![Weight] = varWeight

[MMcCarthy]

Change code to ...

 
Private Sub ProdID_AfterUpdate()
Dim varProdName, varProdCost, varWeight As Variant
 
    varProdName = DLookup("ProdName", "Products", "ProdID=" & Me.ProdID)
    varProdCost = DLookup("ProdCost", "Products", "ProdID=" & Me.ProdID)
    varWeight = DLookup("weight", "Products", "ProdID=" & Me.ProdID)
 
    If (Not IsNull(varProdName)) Then Me![ProdName] = varProdName
    If (Not IsNull(varProdCost)) Then Me![ProdCost] = varProdCost
    If (Not IsNull(varWeight)) Then Me![Weight] = varWeight