Question about LostFocus Private Sub

[exm206]

I have a database we are using in an auction. We have form where we will enter a bidders information such as name, address, phone number, email address. We then have a subform on that form that will show the lots that person bid on. This subform will also show the minimum bid for that lot. We want to make it so if the minimum bid on an item is $200, the data entry person cannot enter a bid of $175. here is an example:

Lot# BidAmount MinBid
001 $250.00 $200.00
002 $400.00 $550.00

We want to make it so if someone enters a bid less than a minimum bid, a message pops up, and the the BidAmount field clears so they can enter a new amount. We have it set right now so if the person does enter an amount lower then the minimum bid, the message will pop up and the field will clear. However, if the person again enters an amount lower than the minimum bid, this time the database will accept it. We can't figure out how to make it run through the code again, to stop it from happening a second time. here is the code we have behind the control on the subform:

Option Compare Database


Private Sub BidAmount_lostfocus()
If Not IsNull(Me.MinBid) Then
If Me.BidAmount < Me.MinBid Then
Me.BidAmount.SetFocus
Me.BidAmount = Null
Beep
MsgBox "Bid amount entered is less than minimum bid!" & vbNewLine & "Please enter a valid bid amount", , "Bid Error!"
BidAmount.SetFocus
End If
End If
End Sub

Thanks in advance for any help you can give me.

 

[Dan Aragon]

Why don't you just use the Data Validation property on the form text box, rather than building code on the Lost Focus event? It would make life much easier!

Just go to the Data tab on the Properties menu and enter >[MinBid] in the Validation Rule field. Then in the next field under Validation Text you can put in your error message. It won't let you save the record (or get out of the bid field box) unless you escape or enter a correct amount.

 

[bat17]

Also you should be using the Before Update event not the lost focus. this gives you the option to cancel changes and only fires when changes are made not when just stepping through. If you set the field to 'Required' and 'Allow Zero Length' to no, then the user wont be able to skip the field either.

Peter