Get a YEs/No resonse from a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following to open my report. The form frm_Period has dates
which i use to filter the report......

I want to add a Yes/No response to the form and have the macro wait for the
response.

How do I go about this? Do I add 2 command buttons or something? What else
is required in my code?


Function myMS()

a = "rpt_MS_Asia_Pacific"
DoCmd.OpenForm "frm_Period", acNormal, "", "", , acNormal

myPeriod2 = [Forms]![frm_Period]![cb_Period]
DoCmd.OpenReport a, acPreview, "", "[tbl_Period]![Period]=""" &
myPeriod2 & """" & " AND [tbl_Time]![Time]=""MAT"""

End Function
 
Bruce said:
I am using the following to open my report. The form frm_Period has
dates which i use to filter the report......

I want to add a Yes/No response to the form and have the macro wait
for the response.

How do I go about this? Do I add 2 command buttons or something? What
else is required in my code?


Function myMS()

a = "rpt_MS_Asia_Pacific"
DoCmd.OpenForm "frm_Period", acNormal, "", "", , acNormal

myPeriod2 = [Forms]![frm_Period]![cb_Period]
DoCmd.OpenReport a, acPreview, "", "[tbl_Period]![Period]=""" &
myPeriod2 & """" & " AND [tbl_Time]![Time]=""MAT"""

End Function
Click to expand...

The trick here is to open the form in dialog mode, but then instead of
closing it when one of the buttons is clicked, make it invisible instead
by setting its Visible property to False. You open the form in dialog
mode like this:

DoCmd.OpenForm "frm_Period", WindowMode:=acDialog

That will pause the code in the calling procedure until the form is
either closed *or made invisible*. So, if your Yes/No buttons on the
form set some hidden checkbox control to either True or False, and then
execute the line ...

Me.Visible = False

.... that will let the calling code continue while leaving the form
available for the retrieval of the information specified on it. When
you're done getting info from the form, close it.

One thing to watch out for in code like this is the possibility that the
form was closed by the user. It's best to check that the form is still
open before trying to read values from it. For example

If CurrentProject.AllForms("frm_Period").IsLoaded Then
' ... get info, open report, close the form ...
Else
' ... assume the user meant to cancel ...
End If
 
I am using the following to open my report. The form frm_Period has dates
which i use to filter the report......

I want to add a Yes/No response to the form and have the macro wait for the
response.

How do I go about this? Do I add 2 command buttons or something? What else
is required in my code?


Function myMS()

a = "rpt_MS_Asia_Pacific"
DoCmd.OpenForm "frm_Period", acNormal, "", "", , acNormal

myPeriod2 = [Forms]![frm_Period]![cb_Period]
DoCmd.OpenReport a, acPreview, "", "[tbl_Period]![Period]=""" &
myPeriod2 & """" & " AND [tbl_Time]![Time]=""MAT"""

End Function
Click to expand...

Use acDialog instead of acNormal as the last argument; the form will
open, and execution will stop until the form is closed *OR HIDDEN*.

Put a "Go" button on frm_Period which simply sets the form's Visible
property to False; the code will resume at that point. Be sure to
close the Form in the Report's Close event.

John W. Vinson[MVP]
 
Thanks Dirk,

I can follow most of what you say below, but can you clarify what object I
want to make invisable. I notice the Form does not have the visable property
but detail does. I can't seem to work out how to hide the wyhole form.

Bruce

Dirk Goldgar said:
Bruce said:
I am using the following to open my report. The form frm_Period has
dates which i use to filter the report......

I want to add a Yes/No response to the form and have the macro wait
for the response.

How do I go about this? Do I add 2 command buttons or something? What
else is required in my code?


Function myMS()

a = "rpt_MS_Asia_Pacific"
DoCmd.OpenForm "frm_Period", acNormal, "", "", , acNormal

myPeriod2 = [Forms]![frm_Period]![cb_Period]
DoCmd.OpenReport a, acPreview, "", "[tbl_Period]![Period]=""" &
myPeriod2 & """" & " AND [tbl_Time]![Time]=""MAT"""

End Function
Click to expand...

The trick here is to open the form in dialog mode, but then instead of
closing it when one of the buttons is clicked, make it invisible instead
by setting its Visible property to False. You open the form in dialog
mode like this:

DoCmd.OpenForm "frm_Period", WindowMode:=acDialog

That will pause the code in the calling procedure until the form is
either closed *or made invisible*. So, if your Yes/No buttons on the
form set some hidden checkbox control to either True or False, and then
execute the line ...

Me.Visible = False

.... that will let the calling code continue while leaving the form
available for the retrieval of the information specified on it. When
you're done getting info from the form, close it.

One thing to watch out for in code like this is the possibility that the
form was closed by the user. It's best to check that the form is still
open before trying to read values from it. For example

If CurrentProject.AllForms("frm_Period").IsLoaded Then
' ... get info, open report, close the form ...
Else
' ... assume the user meant to cancel ...
End If


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Bruce said:
Thanks Dirk,

I can follow most of what you say below, but can you clarify what
object I want to make invisable. I notice the Form does not have the
visable property but detail does. I can't seem to work out how to
hide the wyhole form.
Click to expand...

The form does indeed have a Visible property, though it isn't presented
for you on the form's design-view property sheet. Still, you can set it
to False or True, using code like this on the form:

Me.Visible = False

That will hide the form, and if code execution elsewhere was suspended
because the form was open in dialog mode, that code will now resume.
 
Back
Top