Using DLookup for different customer types and treatment

[Constantine AI]

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:

Dim strFilter As String
 
strFilter = "ProductID = " & Me.ProductID & " And CustomerTypeID = " & Forms![frmCustomer3]![CustomerTypeID]
 
    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

Any suggestions would be great because ive been trying to do this for weeks!

[PianoMan64]

Dim strFilter As String
 
strFilter = "ProductID = " & Me.ProductID & " And CustomerTypeID = " & Forms![frmCustomer3]![CustomerTypeID]
 
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
If Forms![frmCustomer3]![CustomerTypeID] = 3 Then
docmd.Gotocontrol "frmCustomer3"
docmd.gotocontrol "UnitPrice" 'Name of the control that you want to change. 
End if
 
 

[Constantine AI]

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

[PianoMan64]

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:

 
DoCmd.RunCommand acCmdSaveRecord
 
 

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.