DLookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Funny thing happening I have these 2 fields in a sub form I want to update
one is getting its info from a table and the other from the main form.Am
using DLookup for the first field using the ProductId after update event
because its a unit price field the code am using is

Dim s as Currency
s=DLookup("[UnitPrice]","Products", "[ProductCode]=" & Me.ProductCode)
Me.UnitPrice=s
NB ProductCode is a text field . Now the funny thing is am getting an error
with the message "You cancelled the previous operation" but when I use the
same code with a ProductCode field which is a number its working where am I
going wrong?
The second field is a date which should lookup the date in the main form and
update itself
The code am using is
Me.Date = Me.Parent([Date])
its not working any help is appreciated
 
Muriukis,
Re: Unit Price...
Try using a combo box in your subform to select the ProductCode,
(ex name cboProductCode), with columns ProductCode and UnitPrice.
Bind that combo to your ProductCode field.
Using the AfterUpdate event of cboProductCode...
update the UnitPrice field of the subform...
UnitPrice = cboProductCode.Column(1)
(combo columns are numbered 0,1,2,3, etc...)

Re: ProductDate...
First, don't name your date field "Date". It's a "reserved" word in
Access. Call it ProductDate or something like that.
Since the subform is associated/related to the Main form (via some
key Parent/Child field), it is not necessary (or advisable) to capture
the Main form's Date value for each ProductCode subform record.

The ProductDate can always be re-derived "on the fly" in any form,
query, or report.
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."
 
Al Campagna said:
Muriukis,
Re: Unit Price...
Try using a combo box in your subform to select the ProductCode,
(ex name cboProductCode), with columns ProductCode and UnitPrice.
Bind that combo to your ProductCode field.
Using the AfterUpdate event of cboProductCode...
update the UnitPrice field of the subform...
UnitPrice = cboProductCode.Column(1)
(combo columns are numbered 0,1,2,3, etc...)

Re: ProductDate...
First, don't name your date field "Date". It's a "reserved" word in
Access. Call it ProductDate or something like that.
Since the subform is associated/related to the Main form (via some
key Parent/Child field), it is not necessary (or advisable) to capture
the Main form's Date value for each ProductCode subform record.

The ProductDate can always be re-derived "on the fly" in any form,
query, or report.
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

Muriukis said:
Funny thing happening I have these 2 fields in a sub form I want to update
one is getting its info from a table and the other from the main form.Am
using DLookup for the first field using the ProductId after update event
because its a unit price field the code am using is

Dim s as Currency
s=DLookup("[UnitPrice]","Products", "[ProductCode]=" & Me.ProductCode)
Me.UnitPrice=s
NB ProductCode is a text field . Now the funny thing is am getting an
error
with the message "You cancelled the previous operation" but when I use the
same code with a ProductCode field which is a number its working where am
I
going wrong?
The second field is a date which should lookup the date in the main form
and
update itself
The code am using is
Me.Date = Me.Parent([Date])
its not working any help is appreciated
Click to expand...
Click to expand...
Thanks but the ProductCode is already a combo box which looks up the Product
Code but shows the name and also the date is a must to show when that product
was sold
 
Al's already gave you a better solution to what you're trying to do.

I just thought I'd explain the error you're getting. That misleading error
message typically means something was incorrect in the DLookup statement. In
your case, the error is that ProductCode is a text field, and you haven't
put the value you're checking for in quotes. Consequently, Access sees
something like [ProductCode] = xyz and doesn't know what xyz is.

If the ProductCode doesn't have apostrophes in it, use:

s=DLookup("[UnitPrice]","Products", "[ProductCode]='" & Me.ProductCode &
"'")

Exagerated for clarity, that's

s=DLookup("[UnitPrice]","Products", "[ProductCode]= ' " & Me.ProductCode & "
' ")

If there are apostrophes, try

s=DLookup("[UnitPrice]","Products", "[ProductCode]=""" & Me.ProductCode &
"""")

That's 3 double quotes before, and 4 double quotes after.
 
Douglas J. Steele said:
Al's already gave you a better solution to what you're trying to do.

I just thought I'd explain the error you're getting. That misleading error
message typically means something was incorrect in the DLookup statement. In
your case, the error is that ProductCode is a text field, and you haven't
put the value you're checking for in quotes. Consequently, Access sees
something like [ProductCode] = xyz and doesn't know what xyz is.

If the ProductCode doesn't have apostrophes in it, use:

s=DLookup("[UnitPrice]","Products", "[ProductCode]='" & Me.ProductCode &
"'")

Exagerated for clarity, that's

s=DLookup("[UnitPrice]","Products", "[ProductCode]= ' " & Me.ProductCode & "
' ")

If there are apostrophes, try

s=DLookup("[UnitPrice]","Products", "[ProductCode]=""" & Me.ProductCode &
"""")

That's 3 double quotes before, and 4 double quotes after.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Muriukis said:
Funny thing happening I have these 2 fields in a sub form I want to update
one is getting its info from a table and the other from the main form.Am
using DLookup for the first field using the ProductId after update event
because its a unit price field the code am using is

Dim s as Currency
s=DLookup("[UnitPrice]","Products", "[ProductCode]=" & Me.ProductCode)
Me.UnitPrice=s
NB ProductCode is a text field . Now the funny thing is am getting an
error
with the message "You cancelled the previous operation" but when I use the
same code with a ProductCode field which is a number its working where am
I
going wrong?
The second field is a date which should lookup the date in the main form
and
update itself
The code am using is
Me.Date = Me.Parent([Date])
its not working any help is appreciated
Click to expand...
Click to expand...
Worked so well thanks guys thanks very much
 
Back
Top