Displaying a Message Box on save, if criteria isn't met

[ProvPC]

I'm building a database to assist in creating bids for trucking routes. The form provides input fields for various costs, makes default "bid" recommendations based on those costs, and calculates both the cost and the bid at the bottom of the form. One of the fields calculates the profit margin, based on a comparison of the cost and bid numbers entered by the terminal agent (the agents have some degree of flexibility to enable them to secure the job).

At the bottom of the form, there's an "Enter Bid" button to save the record.

What I'm looking for sounds simple - but I'm not bright enough to figure it out. If the Margin_Percentage field is less than 10%, I'd like it to pop up a message box that says, "Only a member of management can submit a bid with a profit margin lower than 10%" and return the user to the unsaved form.

I've tried doing this with a macro, which currently looks something like this:
OnError
Go to Next
Macro Name RejectLowMarginBid
If [MGMT TBL Bid Control]![Margin_Percentage] <10 Then
MessageBox
Message Only a member of management can submit a bid with a profit margin lower than 10%.
Beep Yes
Type Warning!


Title Low Profit Margin

RunMenuCommand
Command SaveRecord
End If

But what happens when I click the Enter Bid button is... nothing. At least nothing obvious. Even if my margin is 1.1%

Clearly, I'm going about this the wrong way. What should I be doing, or how can I fix this?

[davegri]

I think 10% is probably stored as a decimal. 10% displayed is stored as .1
so, try

If [MGMT TBL Bid Control]![Margin_Percentage] < .10 Then

[Minty]

Also what happens if the user closes the form without pressing your command button? I suspect the record gets saved anyway.
You may have to use the BeforeUpdate event of the form and use some VBA to achieve this properly.

[knarfreppep]

Would you want to go back to the unsaved work? Wouldn't that mean someone with the appropriate authority would have to do it all again? Wouldn't it be better to add a 'not approved' field to the record so you could at least save the record with a flag indicating it is not to go further until a person with authority removes the flag?

Your way -

Private Sub Form_BeforeUpdate(Cancel As Integer)
If sngMargin < 0.1 Then
MsgBox "Not enough profit."
Cancel = True
End If
End Sub

If you like my approach -

Private Sub Form_BeforeUpdate(Cancel As Integer)
If sngMargin < 0.1 Then
chkAwaitingRemovalOfLowMargin = True
MsgBox "Not enough profit ... record can be saved to await removal of hold by authorised person."
End If
End Sub

Private Sub chkAwaitingRemovalOfLowMargin_Click()
If chkAwaitingRemovalOfLowMargin = False AND intCurrentOperatorID <> gcMANAGER Then
chkAwaitingRemovalOfLowMargin = True
MsgBox "No authority to do this."
End If
End Sub

[ProvPC]

I think 10% is probably stored as a decimal. 10% displayed is stored as .1
so, try

If [MGMT TBL Bid Control]![Margin_Percentage] < .10 Then

Thanks for your reply!
This makes a lot of sense! But, unfortunately, when I tried it, it still didn't generate any error message, even though the percentage was .3%. I must be overlooking something obvious, but I've no idea what. I'll keep plugging away at it.

[ProvPC]

Also what happens if the user closes the form without pressing your command button? I suspect the record gets saved anyway.
You may have to use the BeforeUpdate event of the form and use some VBA to achieve this properly.

Thanks for your reply!
I suspect you are exactly right. And, sadly, I suspect that places it out of my depth.

[ProvPC]

Thanks for your reply!
I hadn't thought about that - I'll definitely give that a try! I like the notion of being able to flag those that require approval. I'll post back with the results. I hope you'll be agreeable to an additional question or two to clarify, if I get stuck.

[davegri]

Not too familiar with macros, but try this:

[Micron]

You want to lock the checkbox field if you're going to show it. No point in having all this prevention if I can (accidentally?) uncheck the box. Checkbox fields for this type of thing provide limited value IMHO. A table field [Status] might be more useful in that you can provide more than 2 possible values: UnApprvd (because it's an unfinished record - I went for lunch or suddenly need more info) Submit (has been sent for approval -assuming all or any had to be approved by a higher authority as a matter of course) Approved (obvious) Reject (obvious). If you took that approach, I'd have a tblStatus so I could choose the status from a combo and not allow mis-typed entries.

[ProvPC]

Would you want to go back to the unsaved work? Wouldn't that mean someone with the appropriate authority would have to do it all again? Wouldn't it be better to add a 'not approved' field to the record so you could at least save the record with a flag indicating it is not to go further until a person with authority removes the flag?

Your way -

Private Sub Form_BeforeUpdate(Cancel As Integer)
If sngMargin < 0.1 Then
MsgBox "Not enough profit."
Cancel = True
End If
End Sub

If you like my approach -

Private Sub Form_BeforeUpdate(Cancel As Integer)
If sngMargin < 0.1 Then
chkAwaitingRemovalOfLowMargin = True
MsgBox "Not enough profit ... record can be saved to await removal of hold by authorised person."
End If
End Sub

Private Sub chkAwaitingRemovalOfLowMargin_Click()
If chkAwaitingRemovalOfLowMargin = False AND intCurrentOperatorID <> gcMANAGER Then
chkAwaitingRemovalOfLowMargin = True
MsgBox "No authority to do this."
End If
End Sub

This worked brilliantly! Thank you very much! I'm sorry for the long delay in getting back to you. Health issues and whatnot.