Dlookup on Continuous Sub

[CJ]

Hi Groupies:

I have a dlookup on a continuous subform. It appears to be searching with
the correct value but it is returning the lookup value from the first record
in the table.

What I am getting is this:

1111 Product 1 $25
3333 Product 2 $25 (Product 2 should be $17.50, not $25)
8888 Product 3 $25 (Product 3 should be $45, not $25)

My code is as follows:

Private Sub SKU_Number_AfterUpdate()
On Error GoTo Err_SKU_Number_AfterUpdate

Dim strFilter As String

strFilter = "[SKU_Number]=""" & Me.[SKU_Number] & """"
Debug.Print strFilter (I added this so I could confirm that it is
finding the correct SKU_Number, it is.)
Me.Price = DLookup("curRate", "tblInventory", strFilter)

Exit_SKU_Number_AfterUpdate:
Exit Sub

Err_SKU_Number_AfterUpdate:
MsgBox ("Problem")
Resume Exit_SKU_Number_AfterUpdate

End Sub

Can somebody please help me solve this problem!!

 

[Graham Mandeno]

Hi CJ

Because an unbound control is not associated in any way with the current
record, you cannot make it display different values in different rows of a
continuous form.

I'm just guessing that this is some kind of order entry form, in which case
you should include your products table (tblInventory?) in your RecordSource
query, joined to your order details table on SKU_Number.

Then you can simply bind your Price textbox to the curRate field.

If you want to store the price in the order detail record (in case it
changes next week) then bind your Price textbox to the field in that table
and copy curRate into the price field when the SKU is selected. Because the
textbox is bound, it will now show different values in each record.

 

[CJ]

OK, I get it. Nothing a little sleep couldn't fix!!

I didn't realize that you were talking about 2 different scenarios.
For me, I need the second scenario that ensures the price stays the same.

So, I made the control source for my Price text box = Price from the Order
details table. Then, in the Before Update event for SKU_Number, I have:
Me.txtPrice = Me.curRate
If a new SKU is selected the current rate is shown.

Please correct me if I'm wrong but so far it looks like it is working the
way I wanted.

Graham, feel free to put your feet up and have a cold one!

--
Thanks for the brainwaves!

CJ
I blame the parents........

Hi Graham:

Thanks for popping in. You are correct, this is an order entry form.

I understand what you mean up to a point. I need a little clarification:

"...simply bind your Price textbox to the curRate field."
I made the control source of my Price textbox = curRate. Is that correct?

Since I don't want the price to change, the next part is REALLY
important.....

"If you want to store the price in the order detail record (in case it
changes next week) then bind your Price textbox to the field in that table
and copy curRate into the price field when the SKU is selected...."

I'm not sure what you mean here:
1.What field am I now binding Price to? It is already curRate.
2. How and why would I copy curRate into the Price field? Isn't it already
there?

I guess I need a little more detail....
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

Hi CJ

Because an unbound control is not associated in any way with the current
record, you cannot make it display different values in different rows of a
continuous form.

I'm just guessing that this is some kind of order entry form, in which
case you should include your products table (tblInventory?) in your
RecordSource query, joined to your order details table on SKU_Number.

Then you can simply bind your Price textbox to the curRate field.

If you want to store the price in the order detail record (in case it
changes next week) then bind your Price textbox to the field in that table
and copy curRate into the price field when the SKU is selected. Because
the textbox is bound, it will now show different values in each record.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Groupies:

I have a dlookup on a continuous subform. It appears to be searching with
the correct value but it is returning the lookup value from the first
record in the table.

What I am getting is this:

1111 Product 1 $25
3333 Product 2 $25 (Product 2 should be $17.50, not $25)
8888 Product 3 $25 (Product 3 should be $45, not $25)

My code is as follows:

Private Sub SKU_Number_AfterUpdate()
On Error GoTo Err_SKU_Number_AfterUpdate

Dim strFilter As String

strFilter = "[SKU_Number]=""" & Me.[SKU_Number] & """"
Debug.Print strFilter (I added this so I could confirm that it is
finding the correct SKU_Number, it is.)
Me.Price = DLookup("curRate", "tblInventory", strFilter)

Exit_SKU_Number_AfterUpdate:
Exit Sub

Err_SKU_Number_AfterUpdate:
MsgBox ("Problem")
Resume Exit_SKU_Number_AfterUpdate

End Sub

Can somebody please help me solve this problem!!

 

[Graham Mandeno]

Hi CJ

My apologies - your last message never appeared in my newsreader, so I
couldn't reply to it. Blame Bill G.!

I'm glad you have got it all working. However, I would caution you against
a design that does not store the UnitPrice in the OrderDetails record.

Consider this scenario:
Fred orders 10 widgets at $20 each today. No problem - that will cost him
$200. You generate the order and print the invoice and everything is fine.
Next month the price of widgets goes up (blame the oil!) and you have to
charge $25. Fred orders another 10 widgets and this time he gets charged
$250. So far, so good.
Now, at the end of the year you want to gat a summary of sales and revenue
for Fred. The report states, correctly that Fred has purchased 20 widgets,
but since the only stored unit price you have is the *current* price ($25)
the report has to lie and say that your revenue from these sales was $500,
not $450.

Therefore you should have a UnitPrice field in your OrderDetails table which
is bound to your txtPrice textbox. The code you already have
(Me.txtPrice=Me.curRate) will ensure that the price at the time of the sale
is correctly stored there, and it will be available later for historical
reports even if the current price has changed.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Your code

OK, I get it. Nothing a little sleep couldn't fix!!

I didn't realize that you were talking about 2 different scenarios.
For me, I need the second scenario that ensures the price stays the same.

So, I made the control source for my Price text box = Price from the Order
details table. Then, in the Before Update event for SKU_Number, I have:
Me.txtPrice = Me.curRate
If a new SKU is selected the current rate is shown.

Please correct me if I'm wrong but so far it looks like it is working the
way I wanted.

Graham, feel free to put your feet up and have a cold one!

--
Thanks for the brainwaves!

CJ
I blame the parents........

Hi Graham:

Thanks for popping in. You are correct, this is an order entry form.

I understand what you mean up to a point. I need a little clarification:

"...simply bind your Price textbox to the curRate field."
I made the control source of my Price textbox = curRate. Is that correct?

Since I don't want the price to change, the next part is REALLY
important.....

"If you want to store the price in the order detail record (in case it
changes next week) then bind your Price textbox to the field in that
table
and copy curRate into the price field when the SKU is selected...."

I'm not sure what you mean here:
1.What field am I now binding Price to? It is already curRate.
2. How and why would I copy curRate into the Price field? Isn't it
already
there?

I guess I need a little more detail....
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

Hi CJ

Because an unbound control is not associated in any way with the
current
record, you cannot make it display different values in different rows
of a
continuous form.

I'm just guessing that this is some kind of order entry form, in which
case you should include your products table (tblInventory?) in your
RecordSource query, joined to your order details table on SKU_Number.

Then you can simply bind your Price textbox to the curRate field.

If you want to store the price in the order detail record (in case it
changes next week) then bind your Price textbox to the field in that
table
and copy curRate into the price field when the SKU is selected.
Because
the textbox is bound, it will now show different values in each record.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Groupies:

I have a dlookup on a continuous subform. It appears to be searching
with
the correct value but it is returning the lookup value from the first
record in the table.

What I am getting is this:

1111 Product 1 $25
3333 Product 2 $25 (Product 2 should be $17.50, not $25)
8888 Product 3 $25 (Product 3 should be $45, not $25)

My code is as follows:

Private Sub SKU_Number_AfterUpdate()
On Error GoTo Err_SKU_Number_AfterUpdate

Dim strFilter As String

strFilter = "[SKU_Number]=""" & Me.[SKU_Number] & """"
Debug.Print strFilter (I added this so I could confirm that it is
finding the correct SKU_Number, it is.)
Me.Price = DLookup("curRate", "tblInventory", strFilter)

Exit_SKU_Number_AfterUpdate:
Exit Sub

Err_SKU_Number_AfterUpdate:
MsgBox ("Problem")
Resume Exit_SKU_Number_AfterUpdate

End Sub

Can somebody please help me solve this problem!!

 

[CJ]

Hi Graham:

No worries, I am having the same problem with Windows Mail....hhhhhmmmmm...


Anyway, I think I have the data covered.

The subform is getting it's data from a query. The field curRate comes from
tblInventory, Price comes from tblOrderDetails, so the correct value is
stored for each record in tblOrderDetails.

--
Thanks for the brainwaves!

CJ
I blame the parents........

Hi CJ

My apologies - your last message never appeared in my newsreader, so I
couldn't reply to it. Blame Bill G.!

I'm glad you have got it all working. However, I would caution you against
a design that does not store the UnitPrice in the OrderDetails record.

Consider this scenario:
Fred orders 10 widgets at $20 each today. No problem - that will cost him
$200. You generate the order and print the invoice and everything is fine.
Next month the price of widgets goes up (blame the oil!) and you have to
charge $25. Fred orders another 10 widgets and this time he gets charged
$250. So far, so good.
Now, at the end of the year you want to gat a summary of sales and revenue
for Fred. The report states, correctly that Fred has purchased 20 widgets,
but since the only stored unit price you have is the *current* price ($25)
the report has to lie and say that your revenue from these sales was $500,
not $450.

Therefore you should have a UnitPrice field in your OrderDetails table which
is bound to your txtPrice textbox. The code you already have
(Me.txtPrice=Me.curRate) will ensure that the price at the time of the sale
is correctly stored there, and it will be available later for historical
reports even if the current price has changed.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Your code

OK, I get it. Nothing a little sleep couldn't fix!!

I didn't realize that you were talking about 2 different scenarios.
For me, I need the second scenario that ensures the price stays the same.

So, I made the control source for my Price text box = Price from the Order
details table. Then, in the Before Update event for SKU_Number, I have:
Me.txtPrice = Me.curRate
If a new SKU is selected the current rate is shown.

Please correct me if I'm wrong but so far it looks like it is working the
way I wanted.

Graham, feel free to put your feet up and have a cold one!

--
Thanks for the brainwaves!

CJ
I blame the parents........

Hi Graham:

Thanks for popping in. You are correct, this is an order entry form.

I understand what you mean up to a point. I need a little clarification:

"...simply bind your Price textbox to the curRate field."
I made the control source of my Price textbox = curRate. Is that correct?

Since I don't want the price to change, the next part is REALLY
important.....

"If you want to store the price in the order detail record (in case it
changes next week) then bind your Price textbox to the field in that
table
and copy curRate into the price field when the SKU is selected...."

I'm not sure what you mean here:
1.What field am I now binding Price to? It is already curRate.
2. How and why would I copy curRate into the Price field? Isn't it
already
there?

I guess I need a little more detail....
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Hi CJ

Because an unbound control is not associated in any way with the
current
record, you cannot make it display different values in different rows
of a
continuous form.

I'm just guessing that this is some kind of order entry form, in which
case you should include your products table (tblInventory?) in your
RecordSource query, joined to your order details table on SKU_Number.

Then you can simply bind your Price textbox to the curRate field.

If you want to store the price in the order detail record (in case it
changes next week) then bind your Price textbox to the field in that
table
and copy curRate into the price field when the SKU is selected.
Because
the textbox is bound, it will now show different values in each record.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Groupies:

I have a dlookup on a continuous subform. It appears to be searching
with
the correct value but it is returning the lookup value from the first
record in the table.

What I am getting is this:

1111 Product 1 $25
3333 Product 2 $25 (Product 2 should be $17.50, not $25)
8888 Product 3 $25 (Product 3 should be $45, not $25)

My code is as follows:

Private Sub SKU_Number_AfterUpdate()
On Error GoTo Err_SKU_Number_AfterUpdate

Dim strFilter As String

strFilter = "[SKU_Number]=""" & Me.[SKU_Number] & """"
Debug.Print strFilter (I added this so I could confirm that it is
finding the correct SKU_Number, it is.)
Me.Price = DLookup("curRate", "tblInventory", strFilter)

Exit_SKU_Number_AfterUpdate:
Exit Sub

Err_SKU_Number_AfterUpdate:
MsgBox ("Problem")
Resume Exit_SKU_Number_AfterUpdate

End Sub

Can somebody please help me solve this problem!!