Dlookup

[Ron]

I have products table (tblProducts) which have 2 columns:
Product,ProductCategory
The form has 2 fields: Product, ProductCategory

I want to automaticly fill the Category field after choosing the product.

I have this:
Private Sub Product_AfterUpdate()
Dim Cat As String
Cat = DLookup("Category", "tblProducts", [Product] = Me.Product.Value)
Me.Category = Cat
End Sub

no matter which product I choose, I get the same Category.

Can someone can tell me what's wrong?

Thanks,

Ron

 

[JK]

Ron,

If [Product Filed is Numeric:

Cat = Nz(DLookup("[Category]", "tblProducts", _
"[Product] = " & Me.Product.Value),"")


If it is a string:

Cat = Nz(DLookup("[Category]", "tblProducts", _
"[Product] =' " & Me.Product.Value & " ' "),"")
(Remove sapces in ' " and " ' " )

Regards/JK



|I have products table (tblProducts) which have 2 columns:
| Product,ProductCategory
| The form has 2 fields: Product, ProductCategory
|
| I want to automaticly fill the Category field after choosing the product.
|
| I have this:
| Private Sub Product_AfterUpdate()
| Dim Cat As String
| Cat = DLookup("Category", "tblProducts", [Product] = Me.Product.Value)
| Me.Category = Cat
| End Sub
|
| no matter which product I choose, I get the same Category.
|
| Can someone can tell me what's wrong?
|
| Thanks,
|
| Ron
|
|

 

[Ron]

Thanks JK. Works awesome

 

[JK]

My pleasure Ron

| Thanks JK. Works awesome
|
| | > Ron,
| >
| > If [Product Filed is Numeric:
| >
| > Cat = Nz(DLookup("[Category]", "tblProducts", _
| > "[Product] = " & Me.Product.Value),"")
| >
| >
| > If it is a string:
| >
| > Cat = Nz(DLookup("[Category]", "tblProducts", _
| > "[Product] =' " & Me.Product.Value & " ' "),"")
| > (Remove sapces in ' " and " ' " )
| >
| > Regards/JK
| >
| >
| >
| > | > |I have products table (tblProducts) which have 2 columns:
| > | Product,ProductCategory
| > | The form has 2 fields: Product, ProductCategory
| > |
| > | I want to automaticly fill the Category field after choosing the
| > product.
| > |
| > | I have this:
| > | Private Sub Product_AfterUpdate()
| > | Dim Cat As String
| > | Cat = DLookup("Category", "tblProducts", [Product] =
Me.Product.Value)
| > | Me.Category = Cat
| > | End Sub
| > |
| > | no matter which product I choose, I get the same Category.
| > |
| > | Can someone can tell me what's wrong?
| > |
| > | Thanks,
| > |
| > | Ron
| > |
| > |
| >
| >
|
|

 

[John W. Vinson]

I have products table (tblProducts) which have 2 columns:
Product,ProductCategory
The form has 2 fields: Product, ProductCategory

I want to automaticly fill the Category field after choosing the product.

If you're trying to store the Category field redundantly in a second table...
*DON'T*. Not only does it waste space, but what if the name of a category
changes in tblProducts, or a product gets assigned to a different category?
You'll now have lots of records with WRONG categories, and no easy way to
detect the fact!

Just *display* the category on your form, for example by using a textbox with
a control source like

=cboProduct.Column(1)

to display the second column (it's zero based) of the combo box cboProduct.

John W. Vinson [MVP]

 

[Tom]

Thanks John,
The reason I store the category is that I use it later for reports and
criteria in queries.
I know that storing data redundantly is not right, but I dont know how to
use references like that.

I'll be happy if you could elabourate on how to do it.

Thanks,

Ron

 

[John W. Vinson]

Thanks John,
The reason I store the category is that I use it later for reports and
criteria in queries.

You can base a Report on a Query joining the category table; and you can use a
Combo Box on a form for a criterion - displaying the category name but
searching on the category ID. It is NOT necessary to store the category
redundantly!


John W. Vinson [MVP]