467,145 Members | 996 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,145 developers. It's quick & easy.

Using DLookup for different customer types and treatment

100+
Hi can anybody help me with this problem?

I have a customer order form with a sales order line subform. The subform contains products ordered by customers, each (wooden) product can come in two different treatment types; golden brown or pressure treated of which are of two different prices. The prices are also determined by the customer type; pub, garden centre or public.

Currenlty on the treatment combo box, i have a dlookup in the code builder which looks up the price for the product dependant on whether GB (golden brown) or PT (pressure treated) is selected, this works fine for customer types pub (1) and garden centres (2) as the dlookup looks up the customertype and treatment type in a customertypeprice table, and then displays the price in the unit price field on the subform, however for the public customer type, no prices are set in the customertypeprice table as these need to be manually entered in.
I need to able to put this somewhere within the following code,something like 'if customer type = 3 (public) then set focus to the unit price field to allow me to enter a manual price. Does anybody have any ideas on how i can do this? The customer type field isnt within the subform but stored on the main form (frmcustomer3). Currently when i select customer type 3 on the main form and i select the treatment combo box an error occurs because the dlookup cant find customertype 3 in the customertypeprice table.

Here is my current code:
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. strFilter = "ProductID = " & Me.ProductID & " And CustomerTypeID = " & Forms![frmCustomer3]![CustomerTypeID]
  4.  
  5.     If Treatment.Value = "GB" Then
  6.         Me.SOUnitPrice.Value = DLookup("[GoldenBrownUnitPrice]", "tblCustomerTypePrice", strFilter)
  7.     ElseIf Treatment.Value = "PT" Then
  8.         Me.SOUnitPrice.Value = DLookup("[PressureTreatedUnitPrice]", "tblCustomerTypePrice", strFilter)
  9.  
  10.     End If
Any suggestions would be great because ive been trying to do this for weeks!
Mar 30 '08 #1
  • viewed: 2003
Share:
3 Replies
Expert 256MB
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. strFilter = "ProductID = " & Me.ProductID & " And CustomerTypeID = " & Forms![frmCustomer3]![CustomerTypeID]
  4.  
  5. If Treatment.Value = "GB" Then
  6. Me.SOUnitPrice.Value = DLookup("[GoldenBrownUnitPrice]", "tblCustomerTypePrice", strFilter)
  7. ElseIf Treatment.Value = "PT" Then
  8. Me.SOUnitPrice.Value = DLookup("[PressureTreatedUnitPrice]", "tblCustomerTypePrice", strFilter)
  9. End If
  10. If Forms![frmCustomer3]![CustomerTypeID] = 3 Then
  11. docmd.Gotocontrol "frmCustomer3"
  12. docmd.gotocontrol "UnitPrice" 'Name of the control that you want to change. 
  13. End if
  14.  
  15.  
Apr 1 '08 #2
100+
Hi, thank you for your reply, I changed my code to the following but i keep getting an error message of 'Run-Time error 2108' You must save the field before you execute the GoToControl action, the GoToControl method or SetFocus method

this error message highlights the line DoCmd.GoToControl "SOUnitPrice"

Do you have any ideas to why this is or what i can do?

Dim strFilter As String

strFilter = "ProductID = " & Me.ProductID & " And CustomerTypeID = " & Forms![frmCustomer3]![CustomerTypeID]

If Forms![frmCustomer3]![CustomerTypeID] = 3 Then
DoCmd.GoToControl "sfrmSOLine3"
DoCmd.GoToControl "SOUnitPrice"
End If

If Treatment.Value = "GB" Then
Me.SOUnitPrice.Value = DLookup("[GoldenBrownUnitPrice]", "tblCustomerTypePrice", strFilter)
ElseIf Treatment.Value = "PT" Then
Me.SOUnitPrice.Value = DLookup("[PressureTreatedUnitPrice]", "tblCustomerTypePrice", strFilter)
End If

End Sub
Apr 2 '08 #3
Expert 256MB
Hi, thank you for your reply, I changed my code to the following but i keep getting an error message of 'Run-Time error 2108' You must save the field before you execute the GoToControl action, the GoToControl method or SetFocus method

this error message highlights the line DoCmd.GoToControl "SOUnitPrice"

Do you have any ideas to why this is or what i can do?

Dim strFilter As String

strFilter = "ProductID = " & Me.ProductID & " And CustomerTypeID = " & Forms![frmCustomer3]![CustomerTypeID]

If Forms![frmCustomer3]![CustomerTypeID] = 3 Then
DoCmd.GoToControl "sfrmSOLine3"
DoCmd.GoToControl "SOUnitPrice"
End If

If Treatment.Value = "GB" Then
Me.SOUnitPrice.Value = DLookup("[GoldenBrownUnitPrice]", "tblCustomerTypePrice", strFilter)
ElseIf Treatment.Value = "PT" Then
Me.SOUnitPrice.Value = DLookup("[PressureTreatedUnitPrice]", "tblCustomerTypePrice", strFilter)
End If

End Sub
So you have the The control CustomerTypeID Bound to a Field in a table?

Then you need to save the record before you move to the subform.

insert a:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.RunCommand acCmdSaveRecord
  3.  
  4.  
where you normally lose focus of the main form and get focus to the subform.

This will save the record.

But also you need to give me a brief layout of what the main screen looks like and what is bound on the main form and what is bound on the subform?

It may also be that you have some relationship issues that need to be resolved before we can even save the record depending on how the CustomerTypeID is selected?

Let me know.

Joe P.
Apr 3 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Kalvin Schroder | last post: by
5 posts views Thread by Iain Miller | last post: by
6 posts views Thread by Ron | last post: by
4 posts views Thread by =?Utf-8?B?Sm9l?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.