If statements

[Asif Rehman]

Hello

I have an error I am trying to change the the control
sorce of RRP (Recommend Retail Price) depending on where
the customer is based. I have tried inputting the code
into AfterUpdate event but nothing seems to happen, so I
inputted to code in to RRP_click and I get an error can't
find the form products.

The spelling is correct and when the customer is in the
price band warehouse no error occurs and nothing happens
in the RRP field.

I would like this to be automated so the user dosn't have
to click on the field (which is in a datasheet).

Private Sub RRP_Click()

If Forms!Orders![PriceBand].ControlSource
= "warehouse" Then
[RRP].ControlSource = Forms!Products!
[UnitPriceWarehouse].ControlSource

End If

If Forms!Orders![PriceBand] = "local" Then
[RRP].ControlSource = Forms!Products!
[UnitPriceLocal].ControlSource

End If

If Forms!Orders![PriceBand] = "national" Then
[RRP].ControlSource = Forms!Products!
[UnitPriceNational].ControlSource

End If

End Sub

I Hope you have understood my question and again any help
will be much appreciated.

Kindest Regards
Asif

 

[Asif Rehman]

Thank You Allen for your reply.

I just have a few question to make my self understand.

The field RRP is in a subform called orders subform which
is a datasheet. It is located in the orders form. The
price band of the customer is displayed on the orders form
when the customer is selected. Then in the subform you
can select the products they require.

Also where am I to put the price from the products table
in my orders table and can it be changed in the products
table without it being displayed in the orders form.

Thank You again for your help.

Kind Regards
Asif

-----Original Message-----
Can I suggest you copy the price from the Products table into your Orders
table. That way if you ever change the price of a product in the future, the
existing orders can retain their correct prices.

To copy the price, set the AfterUpdate property of the PriceBand control to:
[Event Procedure]
Click the build button beside this. Access opens the code window.
Paste in the code below.

Note that the code uses DLookup() to read the price directly from the table,
so it still works whether the Products form is open or not.

Private Sub PriceBand_AfterUpdate()
Dim strField As String
Dim strWhere As String

If (Not IsNull(Me.ProductID) OR IsNull(Me.PriceBand)) then
Select Case Me.PriceBand
Case "warehouse"
strField = "UnitPriceWarehouse"
Case "local"
strField = "UnitPriceLocal"
Case "national"
strField = "UnitPriceNational"
End Select
If Len(strField) > 0 Then
strWhere = "ProductID = " & Me.ProductID
Me.RRP = DLookup(strField, "Products", strWhere)
End If
end If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hello

I have an error I am trying to change the the control
sorce of RRP (Recommend Retail Price) depending on where
the customer is based. I have tried inputting the code
into AfterUpdate event but nothing seems to happen, so I
inputted to code in to RRP_click and I get an error can't
find the form products.

The spelling is correct and when the customer is in the
price band warehouse no error occurs and nothing happens
in the RRP field.

I would like this to be automated so the user dosn't have
to click on the field (which is in a datasheet).

Private Sub RRP_Click()

If Forms!Orders![PriceBand].ControlSource
= "warehouse" Then
[RRP].ControlSource = Forms!Products!
[UnitPriceWarehouse].ControlSource

End If

If Forms!Orders![PriceBand] = "local" Then
[RRP].ControlSource = Forms!Products!
[UnitPriceLocal].ControlSource

End If

If Forms!Orders![PriceBand] = "national" Then
[RRP].ControlSource = Forms!Products!
[UnitPriceNational].ControlSource

End If

End Sub

I Hope you have understood my question and again any help
will be much appreciated.

Kindest Regards
Asif

.