Pricebands revisited

[Pat]

Hi,

Recently I was given help with setting up pricebands and was given the
following advise:

The code should be fired by the on change event of your
quantity field, and should look something like this:

Sub get_unit_price()
Dim db As DAO.Database
Dim rst As DAO.Recordset

prod = Forms("FormName").Controls("Product_ID")
qty = Forms("FormName").Controls("Quantity")
strSQL = "SELECT * FROM tblPrices WHERE Prod_ID = '" & prod
strSQL = strSQL & "' ORDER BY BreakPoint DESC"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
prc = rst.Fields("BandUnitPrice")
Do
If qty > rst.Fields("BreakPoint") Then Exit Do
rst.MoveNext
If rst.EOF Then Exit Do
prc = rst.Fields("BandUnitPrice")
Loop
rst.Close
Forms("FormName").Controls("UnitPrice") = prc
End Sub

Notes:
1.You'll need to add the DAO 3.6 reference (in the VB
editor, menu Tools > References).
2.The SQL string above assumes your product_ID field is
type text. If it is number, you'll need to remove the
single quotes insede the double ones on either side of the
prod variable.
3.Not having the database I have not tested the code, but
shouldn't be far off.

End of advise.

I have followed the above advise as best I could. The problem I am
experiencing is when a value is entered in the quantity field the following
error message appears:

"The expression On Change you entered as the event property produced the
following error: Member already exists in an object module from which this
object module derives"

Anyone know why I am getting this message?

I can provide additional information if necessary.

Thanking you
Pat