Forms to run reports

[Guest]

Hi,

I've got a load of parameter queries in my database and I'm trying to
replace the parameter boxes with custom forms. However I've tried to do this
before and I can not seem to master the technique.

Now in another part of the database I've managed to run a parameter based
query from a staff details form using the Staff number on the current form.
This is as far as I could get until recently.

I've designed a form with a single combo box on it which allows users to
select which specialists they wish to see then they press ok which runs the
report. All works fine but the form stays open over the top of the report.
If I close the form somehow the report minimizes and the menu screen takes
its place.

Now you can just select the report again from the taskbar but I cant
understand why its happening in the first place. I'll explain how I'm doing
it at the mowment. Users select an option from the menu which runs a macro
to open the from users then select the required specialist and select ok
which runs a macro to open the report. Now the report itself is based on a
query, this query has the criteria to get the specialist users choice.

Theres got to be a better way but my VB skills are non existent.

Please help me if I can just crack this technique it would greatly improve
my database.

Ian

 

[Guest]

First of all open the form in Design mode and check the following properties:

- PopUp property should be set to False
- Modal property should be set to False

The above will probably help to keep the form behind the report.


If you have a button on the form which opens the report add the code to hide
the form:

Me.Visible = False

For the report OnClose event add the code to make the form visible again:

[Forms]![YourFormName].Visible = True

or code to close the form:

DoCmd.Close acForm, "YourFormName", acSaveNo

Hope it will help!

 

[Guest]

The PopUp Property of the form is probably set to yes.

So you can change it to No
Or, on the on click event of the button close the form, and on the OnOpen
event of the report run the code
docmd.Maximize

 

[Guest]

Had a variety of different reactions but its still not right. Closing the
form results in the report being blank.

Is this anything to do with the macro openning the form in Dialog.

First of all open the form in Design mode and check the following properties:

- PopUp property should be set to False
- Modal property should be set to False

The above will probably help to keep the form behind the report.


If you have a button on the form which opens the report add the code to hide
the form:

Me.Visible = False

For the report OnClose event add the code to make the form visible again:

[Forms]![YourFormName].Visible = True

or code to close the form:

DoCmd.Close acForm, "YourFormName", acSaveNo

Hope it will help!

Hi,

I've got a load of parameter queries in my database and I'm trying to
replace the parameter boxes with custom forms. However I've tried to do this
before and I can not seem to master the technique.

Now in another part of the database I've managed to run a parameter based
query from a staff details form using the Staff number on the current form.
This is as far as I could get until recently.

I've designed a form with a single combo box on it which allows users to
select which specialists they wish to see then they press ok which runs the
report. All works fine but the form stays open over the top of the report.
If I close the form somehow the report minimizes and the menu screen takes
its place.

Now you can just select the report again from the taskbar but I cant
understand why its happening in the first place. I'll explain how I'm doing
it at the mowment. Users select an option from the menu which runs a macro
to open the from users then select the required specialist and select ok
which runs a macro to open the report. Now the report itself is based on a
query, this query has the criteria to get the specialist users choice.

Theres got to be a better way but my VB skills are non existent.

Please help me if I can just crack this technique it would greatly improve
my database.

Ian

 

[Guest]

Let's make it step by step without using any macros.

In the following examples you should insert the form name instead of
"YourFormName"

1) set the OnOpen property of the report as follows:

DoCmd.OpenForm "YourFormName", acNormal, , , acFormEdit, acDialog
If IsLoaded("YourFormName") = False Then
DoCmd.CancelEvent
End If

2) set the OnClose event of the report as follows:

DoCmd.Close acForm, "YourFormName", acSaveNo

3) set the OnClick event of the "OK" button on the form as follows:

Me.Visible = False

4) set the OnClick event of the "Cancel" button as follows:

DoCmd.Close

Check the properties of the form:
- PopUp property should be set to "No"
- Modal property should be set to "No"

Had a variety of different reactions but its still not right. Closing the
form results in the report being blank.

Is this anything to do with the macro openning the form in Dialog.

First of all open the form in Design mode and check the following properties:

- PopUp property should be set to False
- Modal property should be set to False

The above will probably help to keep the form behind the report.


If you have a button on the form which opens the report add the code to hide
the form:

Me.Visible = False

For the report OnClose event add the code to make the form visible again:

[Forms]![YourFormName].Visible = True

or code to close the form:

DoCmd.Close acForm, "YourFormName", acSaveNo

Hope it will help!

Hi,

I've got a load of parameter queries in my database and I'm trying to
replace the parameter boxes with custom forms. However I've tried to do this
before and I can not seem to master the technique.

Now in another part of the database I've managed to run a parameter based
query from a staff details form using the Staff number on the current form.
This is as far as I could get until recently.

I've designed a form with a single combo box on it which allows users to
select which specialists they wish to see then they press ok which runs the
report. All works fine but the form stays open over the top of the report.
If I close the form somehow the report minimizes and the menu screen takes
its place.

Now you can just select the report again from the taskbar but I cant
understand why its happening in the first place. I'll explain how I'm doing
it at the mowment. Users select an option from the menu which runs a macro
to open the from users then select the required specialist and select ok
which runs a macro to open the report. Now the report itself is based on a
query, this query has the criteria to get the specialist users choice.

Theres got to be a better way but my VB skills are non existent.

Please help me if I can just crack this technique it would greatly improve
my database.

Ian

 

[Guest]

P.S. In order for the function IsLoaded() to work you need to create a new
module, to paste the following code there and to save the module.



Option Explicit

Function IsLoaded(ByVal strFormName As String) As Boolean

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function

 

[Guest]

That code sorts the original problem however the report is always blank now.
I have no idea way.