Dlookup condition result

[Guest]

I have the following code on a form:
Form_Close
Link = "[DocId] = """ & Me.[DocId] & """"
DoCmd.OpenReport "Detail", acViewPreview, , Link, acNormal

And, this in its subform:
Code_Afterupdate
Me.Name = DLookup("Name", "Products", Me.[ProductCode] = [ProductCode])
Me.Price = DLookup("SalePrice", "SalePrice", ProductCode = Me. ProductCode)

The problem is that no matter which ProductCode I have on the subform the
function always returns the Name and SalePrice of the first record on
Products and SalePrice tables.

And the same goes for the form
No matter which DocId I have on the form it always opens the Report on the
first record on Documents tables.

There must be some problem in this code but I cant figure it out.

So, I appreciate any help you can give me.

 

[robbuonocore]

Raul:

I'm just taking a stab at this, but I think your problem is that the
code is in a subform. If the form is not open on its own and instead
is opened through a subform, I don't think you can reference fields
using the "Me.".

Did you try changing the code to reference each field as

Forms!["Parent Form Name"].["Sub form Name"].Form![ProductCode]

I think the key is the word Form after the sub form name.

Robby Buonocore

 

[Duane Hookom]

"Name" is not a good name for a field or anything else in Access since every
object has a name property.
Are you hoping to store a product name in a detail table when the
ProductCode is already stored in the table? Is there a good reason for this?

Normally the ProductCode would be selected from a combo box with a Row
Source that includes the ProductCode, [Name], and SalePrice. You would then
add a text box to the detail section of the subform with a control source
like:
=cboProductCode.Column(1)
Columns are numbered beginning with 0.

If you want to default the Price to SalePrice, add code to the after udpate
event of cboProductCode

Me.txtPrice = Me.cboProductCode.Column(2)

 

[J. Goddard]

The syntax in your DLookup conditions is wrong; you need a character string:

Me.Name = DLookup("Name", "Products", "[ProductCode] = " & Me.[ProductCode] )
Me.Price = DLookup("SalePrice", "SalePrice", "ProductCode = " &

me.ProductCode )

This assumes productcode is numeric - if it is character, you need:

Me.Name = DLookup("Name", "Products", "[ProductCode] = '" & Me.[ProductCode] & "'" )
Me.Price = DLookup("SalePrice", "SalePrice", "ProductCode = '" &

me.ProductCode & "'")

(I always use single quotes in cases like this - easier to read)

John

I have the following code on a form:
Form_Close
Link = "[DocId] = """ & Me.[DocId] & """"
DoCmd.OpenReport "Detail", acViewPreview, , Link, acNormal

And, this in its subform:
Code_Afterupdate
Me.Name = DLookup("Name", "Products", Me.[ProductCode] = [ProductCode])
Me.Price = DLookup("SalePrice", "SalePrice", ProductCode = Me. ProductCode)

The problem is that no matter which ProductCode I have on the subform the
function always returns the Name and SalePrice of the first record on
Products and SalePrice tables.

And the same goes for the form
No matter which DocId I have on the form it always opens the Report on the
first record on Documents tables.

There must be some problem in this code but I cant figure it out.

So, I appreciate any help you can give me.

 

[Guest]

Hello all, thank you for your reply.

I have tried the J. Goddard's idea. it solved my problem
I have also renamed the [Name] field.


"J. Goddard" escreveu:

The syntax in your DLookup conditions is wrong; you need a character string:

Me.Name = DLookup("Name", "Products", "[ProductCode] = " & Me.[ProductCode] )
Me.Price = DLookup("SalePrice", "SalePrice", "ProductCode = " &

me.ProductCode )

This assumes productcode is numeric - if it is character, you need:

Me.Name = DLookup("Name", "Products", "[ProductCode] = '" & Me.[ProductCode] & "'" )
Me.Price = DLookup("SalePrice", "SalePrice", "ProductCode = '" &

me.ProductCode & "'")

(I always use single quotes in cases like this - easier to read)

John

I have the following code on a form:
Form_Close
Link = "[DocId] = """ & Me.[DocId] & """"
DoCmd.OpenReport "Detail", acViewPreview, , Link, acNormal

And, this in its subform:
Code_Afterupdate
Me.Name = DLookup("Name", "Products", Me.[ProductCode] = [ProductCode])
Me.Price = DLookup("SalePrice", "SalePrice", ProductCode = Me. ProductCode)

The problem is that no matter which ProductCode I have on the subform the
function always returns the Name and SalePrice of the first record on
Products and SalePrice tables.

And the same goes for the form
No matter which DocId I have on the form it always opens the Report on the
first record on Documents tables.

There must be some problem in this code but I cant figure it out.

So, I appreciate any help you can give me.