How to write this in VB

  • Thread starter Thread starter Asif Rehman
  • Start date Start date
A

Asif Rehman

Hello

I have a unit price field on my form and I would like to
have an If statement so that when the salesman tries to
sell the goods below cost price (which is located in the
products table) the following happens

If UnitPrice =< CostPrice Then
Error 'Are you sure you would want to sell this product
below cost price'
If Yes = True Then Allow Change
Else No = Trun Then Me.Undo

As you can see I would like a yes and no button to also
pop up with the error.

Any help will be much appreciated.

Kind Regards
Asif
 
Your mean like this:

if MsgBox("Are you sure you would like to sell this product below cost
price", vbYesNo or vbExclamation) = vbYes then
...
else
...
end if
 
Thats great Thomas but I have a problem I would like the
Unit Price to look at the Cost Price Field first,

For example if the cost price of the product is £100 then
if the salesman tries to sell the product at £99 the error
pop up if they select no then the unit price field to
clear else the £99 to stay.

Like the following
If Me.UnitPrice <= Forms!Products.CostPrice Then

Also how do you refer to a form when it is not opened.
The above code will only refer to the products form when
it is open but I would like it to be closed.

Thank You again for your help

Asif
 
Hello

Currently I have the following code:

Private Sub UnitPrice_AfterUpdate()

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If Me.UnitPrice <= Forms!Products.CostPrice Then
strMsg = "Are you sure you want to sell the
product below Cost Price"
strTitle = "Error attempt to sell below Cost Price"
intStyle = vbYesNo
MsgBox strMsg, intStyle, strTitle

Else

If intStyle = vbNo Then
Me!UnitPrice.Undo
Me.Undo
End If

End If

End Sub

There are a couple of things I need help with, Firstly the
second part of the If statement regarding the undo dosn't
work I don't know if that is the right way of doing it,
also refering to the Products form how do I do that
without having to have the form open as currently only
works when the form is open.

Thank You

Kind Regards
Asif
 
Hi,
You cannot refer to a form when it's closed.
You need to get the costprice of the product from your table.
You can use DLookup for this.
Now to do this, you need the ProductId which I assume is part
of your form's recordset.

If Me.UnitPrice <= DLookup("[CostPrice]","yourTable","ProductId =" &
Me.ProductId) Then
.............

Substitute your own field and table names in the above. Check out DLookup
in Help as well.

--
HTH
Dan Artuso, Access MVP


Thats great Thomas but I have a problem I would like the
Unit Price to look at the Cost Price Field first,

For example if the cost price of the product is £100 then
if the salesman tries to sell the product at £99 the error
pop up if they select no then the unit price field to
clear else the £99 to stay.

Like the following
If Me.UnitPrice <= Forms!Products.CostPrice Then

Also how do you refer to a form when it is not opened.
The above code will only refer to the products form when
it is open but I would like it to be closed.

Thank You again for your help

Asif
 
Thank You very much for that Dan, that part of the code
works now, only got the other part left to do;
Private Sub UnitPrice_AfterUpdate()

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If Me.UnitPrice <= DLookup
("[CostPrice]", "Products", "ProductId =" & Me.ProductID)
Then
strMsg = "Are you sure you want to sell the
product below Cost Price"
strTitle = "Error attempt to sell below Cost Price"
intStyle = vbYesNo
MsgBox strMsg, intStyle, strTitle

Else

If intStyle = vbNo Then
Me!UnitPrice.Undo
Me.Undo
End If

End If

End Sub

The second If statement dosn't work I don't know if this
the right way of doing it.

Thank You
Asif

-----Original Message-----
Hi,
You cannot refer to a form when it's closed.
You need to get the costprice of the product from your table.
You can use DLookup for this.
Now to do this, you need the ProductId which I assume is part
of your form's recordset.

If Me.UnitPrice <= DLookup
Click to expand...
("[CostPrice]","yourTable","ProductId =" &
 
Asif

Try this

If UnitPrice =< CostPrice Then
If MsgBox("Are you sure you wish to sell this Product at or below
cost price?, _
vbQuestion+vbYesNo,"Price Warning") = vbYes Then
'allow change code
Else
Me.Undo
End If
End If


HTH

Andy
 
Back
Top