Msg Box with Conditions and display a value from form on message

  • Thread starter Thread starter Nurse Nancy
  • Start date Start date
N

Nurse Nancy

Hi,
I have a Control button on a form that opens another form, I want to display
a message pop up on the new form if a calculated value on that form is
greater than 0, then the user can choose to close the new form or ignore the
warning.


If it's less than Zero, then i would disply a different pop up message.

I have 3 problems,, and if anyone can help with any one of them,, i would be
ever so greatful!

1. Where do i put the VB for the popup message,, so I can see the form open
and make a decision on whether to close it.
When i put it on the 2nd form property sheet,,,
On current - It displays after i hit the control-button, while I am still on
the first form so I can't see the new form open
On Focus - It displays while i am still on the first form so i can't see the
new form
On Click - Nothing happens, I don't get a message

I ended up putting it on the detail section of the new form and now whenever
I click on that, i get the message,, i only want it once after the form opens.
ANy suggestions?


2. I only want the pop up message to display if the value of a calculated
field is greater than zero and I want to display the value in the message.

The field is called Diff TB on the form and is calculated as
Sum([Prime Rate]*[Prime Spots]+[Rot Rate]*[Rot Spots]+[Other Rate]*[Other
Spots])
I don't know VB and when i tried to use the calc as is,, i get a syntax error


3. How do i code it so if it's greater than zero, i display a different
message?



Here's the code without the calc, which also doesn't work

Private Sub Detail_Click()


If [Diff TB] > 0 Then


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Weekly Budget is Greater Than Total Perspective Buys, Do you want to
Add More Stations? Weekly Budget = $$$$$$, Diff = $$$$$$$"
Style = vbYesNo + vbDefaultButton2
Title = "MsgBox Demonstration"
Help = "DEMO.HLP"
Ctxt = 1000
End If
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close acForm, "Weekly Buy Table Form", acSaveYes

Else ' User chose No.
MyString = "No" ' Do Nothing
End If
End Sub
 
Nancy,

First, try placing your code in the On Click event of your command button on
the first form.

Do your calculation in this code first. You did not indicate just what
methed you are trying to use to do the calculation. Calculations can be done
with a query or you can use VBA code to open a record set, read values and
perform the calculation, saving the results to a variable.

Then use the resulting value in your message box to communicate with the
user, letting them make the decision.

My suggestion is that you move your code to the On Click event of your
command button. Then get the calculation working by what ever means you need
to.

Just do one thing at a time, making each step work. At the end you will
have the whole thing working.

Post back when you need assistance.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm


Nurse Nancy said:
Hi,
I have a Control button on a form that opens another form, I want to display
a message pop up on the new form if a calculated value on that form is
greater than 0, then the user can choose to close the new form or ignore the
warning.


If it's less than Zero, then i would disply a different pop up message.

I have 3 problems,, and if anyone can help with any one of them,, i would be
ever so greatful!

1. Where do i put the VB for the popup message,, so I can see the form open
and make a decision on whether to close it.
When i put it on the 2nd form property sheet,,,
On current - It displays after i hit the control-button, while I am still on
the first form so I can't see the new form open
On Focus - It displays while i am still on the first form so i can't see the
new form
On Click - Nothing happens, I don't get a message

I ended up putting it on the detail section of the new form and now whenever
I click on that, i get the message,, i only want it once after the form opens.
ANy suggestions?


2. I only want the pop up message to display if the value of a calculated
field is greater than zero and I want to display the value in the message.

The field is called Diff TB on the form and is calculated as
Sum([Prime Rate]*[Prime Spots]+[Rot Rate]*[Rot Spots]+[Other Rate]*[Other
Spots])
I don't know VB and when i tried to use the calc as is,, i get a syntax error


3. How do i code it so if it's greater than zero, i display a different
message?



Here's the code without the calc, which also doesn't work

Private Sub Detail_Click()


If [Diff TB] > 0 Then


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Weekly Budget is Greater Than Total Perspective Buys, Do you want to
Add More Stations? Weekly Budget = $$$$$$, Diff = $$$$$$$"
Style = vbYesNo + vbDefaultButton2
Title = "MsgBox Demonstration"
Help = "DEMO.HLP"
Ctxt = 1000
End If
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close acForm, "Weekly Buy Table Form", acSaveYes

Else ' User chose No.
MyString = "No" ' Do Nothing
End If
End Sub
Click to expand...
 
1. On Open?

2. Refer to the field on the form.
If txtCalcField > 0 then
msgbox ...
end if

3. If txtCalcField = 0 then
msgbox ...
else
msgbox ...
end if
 
uggh, this is sooooo frustrating,,
I kind of have the calculation working


Basically the button I have opens another form which also has a subform,,
The form runs a totals query on all the records matching the where clause of
the macro that opens it
If I put the msgbox on the button that opens the form,, the msg pops up
before the user can see the new form/subform which shows the detail and the
totals calc,,

i want the new form to fully open and load,, and then the msg to pop up if
the value in [Diff TB] > 0.

Right now I put this in the On Enter of the subform,, this gets me close,,
but user has to click in the subform to get the message.

Also I wanted to put the actual value of [Diff TB] displayed in the
message,, can you help me with that?


If [Diff TB] > 0 Then


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Budget is Greater Than Total Perspective Buys, Do you want to Add
More Stations? 'Diff TB'"
Style = vbYesNo + vbDefaultButton2
Title = "MsgBox Demonstration"
Help = "DEMO.HLP"
Ctxt = 1000
End If
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close acForm, "Weekly Buy Table Form", acSaveYes

Else ' User chose No.
MyString = "No" ' Perform some action.
End If





--
Nancy


Nurse Nancy said:
Hi,
I have a Control button on a form that opens another form, I want to display
a message pop up on the new form if a calculated value on that form is
greater than 0, then the user can choose to close the new form or ignore the
warning.


If it's less than Zero, then i would disply a different pop up message.

I have 3 problems,, and if anyone can help with any one of them,, i would be
ever so greatful!

1. Where do i put the VB for the popup message,, so I can see the form open
and make a decision on whether to close it.
When i put it on the 2nd form property sheet,,,
On current - It displays after i hit the control-button, while I am still on
the first form so I can't see the new form open
On Focus - It displays while i am still on the first form so i can't see the
new form
On Click - Nothing happens, I don't get a message

I ended up putting it on the detail section of the new form and now whenever
I click on that, i get the message,, i only want it once after the form opens.
ANy suggestions?


2. I only want the pop up message to display if the value of a calculated
field is greater than zero and I want to display the value in the message.

The field is called Diff TB on the form and is calculated as
Sum([Prime Rate]*[Prime Spots]+[Rot Rate]*[Rot Spots]+[Other Rate]*[Other
Spots])
I don't know VB and when i tried to use the calc as is,, i get a syntax error


3. How do i code it so if it's greater than zero, i display a different
message?



Here's the code without the calc, which also doesn't work

Private Sub Detail_Click()


If [Diff TB] > 0 Then


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Weekly Budget is Greater Than Total Perspective Buys, Do you want to
Add More Stations? Weekly Budget = $$$$$$, Diff = $$$$$$$"
Style = vbYesNo + vbDefaultButton2
Title = "MsgBox Demonstration"
Help = "DEMO.HLP"
Ctxt = 1000
End If
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close acForm, "Weekly Buy Table Form", acSaveYes

Else ' User chose No.
MyString = "No" ' Do Nothing
End If
End Sub
Click to expand...
 
This is not going to work in the On Enter of the Subform after all. It gets
activated every time the user enters the subform and I only want the message
once after it's loaded

I have now put it in the 'On Load' of the Main Form that the command button
opens. The message still displays after I hit the command button from the
first form, and before the new form opens, do i have to live with this?

My other problem is that if the [Diff TB] is not > 0, i get a popup box that
just has the OK button

and I don't want anything to happen for this scenario.

Also i am still looking for the syntax to include the value of [Diff TB] in
the message

Appreciate any help that you can offer,, thanks so much

--
Nancy


Nurse Nancy said:
uggh, this is sooooo frustrating,,
I kind of have the calculation working


Basically the button I have opens another form which also has a subform,,
The form runs a totals query on all the records matching the where clause of
the macro that opens it
If I put the msgbox on the button that opens the form,, the msg pops up
before the user can see the new form/subform which shows the detail and the
totals calc,,

i want the new form to fully open and load,, and then the msg to pop up if
the value in [Diff TB] > 0.

Right now I put this in the On Enter of the subform,, this gets me close,,
but user has to click in the subform to get the message.

Also I wanted to put the actual value of [Diff TB] displayed in the
message,, can you help me with that?


If [Diff TB] > 0 Then


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Budget is Greater Than Total Perspective Buys, Do you want to Add
More Stations? 'Diff TB'"
Style = vbYesNo + vbDefaultButton2
Title = "MsgBox Demonstration"
Help = "DEMO.HLP"
Ctxt = 1000
End If
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close acForm, "Weekly Buy Table Form", acSaveYes

Else ' User chose No.
MyString = "No" ' Perform some action.
End If





--
Nancy


Nurse Nancy said:
Hi,
I have a Control button on a form that opens another form, I want to display
a message pop up on the new form if a calculated value on that form is
greater than 0, then the user can choose to close the new form or ignore the
warning.


If it's less than Zero, then i would disply a different pop up message.

I have 3 problems,, and if anyone can help with any one of them,, i would be
ever so greatful!

1. Where do i put the VB for the popup message,, so I can see the form open
and make a decision on whether to close it.
When i put it on the 2nd form property sheet,,,
On current - It displays after i hit the control-button, while I am still on
the first form so I can't see the new form open
On Focus - It displays while i am still on the first form so i can't see the
new form
On Click - Nothing happens, I don't get a message

I ended up putting it on the detail section of the new form and now whenever
I click on that, i get the message,, i only want it once after the form opens.
ANy suggestions?


2. I only want the pop up message to display if the value of a calculated
field is greater than zero and I want to display the value in the message.

The field is called Diff TB on the form and is calculated as
Sum([Prime Rate]*[Prime Spots]+[Rot Rate]*[Rot Spots]+[Other Rate]*[Other
Spots])
I don't know VB and when i tried to use the calc as is,, i get a syntax error


3. How do i code it so if it's greater than zero, i display a different
message?



Here's the code without the calc, which also doesn't work

Private Sub Detail_Click()


If [Diff TB] > 0 Then


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Weekly Budget is Greater Than Total Perspective Buys, Do you want to
Add More Stations? Weekly Budget = $$$$$$, Diff = $$$$$$$"
Style = vbYesNo + vbDefaultButton2
Title = "MsgBox Demonstration"
Help = "DEMO.HLP"
Ctxt = 1000
End If
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close acForm, "Weekly Buy Table Form", acSaveYes

Else ' User chose No.
MyString = "No" ' Do Nothing
End If
End Sub
Click to expand...
Click to expand...
 
Back
Top