If statement help please

[bgeorge12]

I have the following code:



Private Sub Unit_Price_GotFocus()
If Forms![Order Details]![Line] = "[Oakhill Cath]" Then
Me![Unit Price] = DLookup("[Price]", "[Oakhill Cath]", "[Product ID]='" & Forms![Order Details]![sbfOrderDetails].Form![Product ID] & "'")
Else
Me![Unit Price] = 500#
End If
End Sub

My structure is a main form named Orders Details with a subform sbfOrderDetails. On the main form is a combo box named Line. The subform contains the fields Product ID (which is a combo box) and Price.

When I run this it returns 500 even if the Line field says Oakhill Cath. It seems to not be recognizing it. What have I done wrong?

[boblarson]

Where is this code? Is it on the main form? If so, it would be:

Private Sub Unit_Price_GotFocus()
If Me.[Line] = "[Oakhill Cath]" Then
   Me![Unit Price] = DLookup("[Price]", "[Oakhill Cath]", "[Product ID]='" & Me.sbfOrderDetails.Form![Product ID] & "'")
Else
   Me![Unit Price] = 500
End If
End Sub

But that really depends on if the name of the subform CONTROL (control on the parent form which HOUSES the subform) is named sbfOrderDetails. You use the subform control name and not the subform name unless they are named the same. So, let's say the control is really named sbfOrderDetails subform, then you would use

   Me![Unit Price] = DLookup("[Price]", "[Oakhill Cath]", "[Product ID]='" & Me.[sbfOrderDetails subform].Form![Product ID] & "'")

[pbaldy]

Does the value in the combo really contain the brackets? Or a numeric ID value? Set a breakpoint and step through the code so you can see what the combo is actually returning.

[bgeorge12]

The combo box Line (on the main form) is tied to a table that lists the names of different lines of cabinets. The field is text. I am putting this code on the subform as a get focus event for Unit Price.

[boblarson]

If the code is on the subform, then you would reference Product ID by using

Me.[Product ID]

[bgeorge12]

Okay I think I have the code as you suggested:
Private Sub Unit_Price_GotFocus()
If Forms![Order Details]![Line] = "[Oakhill Cath]" Then
Me![Unit Price] = DLookup("[Price]", "[Oakhill Cath]", "[Product ID]='" & Me![Product ID] & "'")
Else
Me![Unit Price] = 500
End If
End Sub

It still returns 500 even if Line has the text Oakhill Cath in it.

[June7]

Maybe this line:
If Forms![Order Details]![Line] = "[Oakhill Cath]" Then

Should be:
If Forms![Order Details]![Line] = "Oakhill Cath" Then

[bgeorge12]

Just tryed that. Still returns 500.

[pbaldy]

You need to validate that the combo is returning what you think it is:

Set a breakpoint and step through the code so you can see what the combo is actually returning.

[bgeorge12]

Sorry pbaldy I do not understand "set a breakpoint". The combo box just shows what is in the only field of a table.

[pbaldy]

See if Jason's FAQ helps:

http://www.baldyweb.com/Debugging.htm

[bgeorge12]

Thank all of you! Got it to work!

[pbaldy]

What was the problem?