criteria

[Christina]

I have a report and a subreport run from queries which each require the
input of a beginning date and an ending date. I would like to be requested
to input the criteria once instead of twice.
Thanks

 

[fredg]

I have a report and a subreport run from queries which each require the
input of a beginning date and an ending date. I would like to be requested
to input the criteria once instead of twice.
Thanks

You will need a form for this.
Create a form. Add 2 unbound text controls.
Set their format property to a valid date format.
Name one control "StartDate'.
Name the other control "EndDate".
Add a command button to the form.
Code it's click event:

DoCmd.OpenReport "ReportName", acViewPreview
Me.Visible = False

Name this form "ParamForm".

As criteria for the main report query, and also for the sub-report
query, enter, on the Date field's criteria line:
Between Forms!ParamForm!StartDate and Forms!ParamForm!EndDate

Code the Main Report's Close event:
DoCmd.Close acForm, "ParamForm"

Then when you are ready to run the report, open the form.
Enter the starting and ending dates wanted.
Click the command button.
The report and it's sub report will run.
When the report closes, it will also close the form.

 

[Christina]

Thanks you. I am not sure how to add the code to the click event or code
the main report close event. Please for detail instructions.

Thanks

 

[fredg]

Thanks you. I am not sure how to add the code to the click event or code
the main report close event. Please for detail instructions.

Thanks

For the Form, select the Command Button.
Display the button's property sheet.
Click on the Event tab.
Click on the event line you wish to code, i.e. the Click event.
Write
[Event Procedure]
on that line.
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those lines write your code for the command button.

DoCmd.OpenReport "ReportName", acViewPreview
Me.Visible = False

For the Report, display the Report's property sheet.
Click on the Close evnet line and follow the same instructions as for
the Form Command button.
Write the code:

DoCmd.Close acForm, "ParamForm"

 

[Christina]

Small question. WHen I am create the command button, do I follow all the
instructions and link the report to the button? Otherwise I need to know what
to do when I create the button so that I can put the code. Hope you
understand my question.

Thanks

Thanks you. I am not sure how to add the code to the click event or code
the main report close event. Please for detail instructions.

Thanks

For the Form, select the Command Button.
Display the button's property sheet.
Click on the Event tab.
Click on the event line you wish to code, i.e. the Click event.
Write
[Event Procedure]
on that line.
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those lines write your code for the command button.

DoCmd.OpenReport "ReportName", acViewPreview
Me.Visible = False

For the Report, display the Report's property sheet.
Click on the Close evnet line and follow the same instructions as for
the Form Command button.
Write the code:

DoCmd.Close acForm, "ParamForm"

 

[Christina]

Hi, The report does not return any data. I created the form with the two
text Boxes as instructed. I added a command button with this code in the
click event.
Private Sub Command21_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False
There were some other code in there before the End Sub. Should I delete
that? I tried it both ways anyhow, and it did not run. Don't know what I am
doing wrong.

Small question. WHen I am create the command button, do I follow all the
instructions and link the report to the button? Otherwise I need to know what
to do when I create the button so that I can put the code. Hope you
understand my question.

Thanks

Thanks you. I am not sure how to add the code to the click event or code
the main report close event. Please for detail instructions.

Thanks

:

On Tue, 24 Feb 2009 13:34:01 -0800, Christina wrote:

I have a report and a subreport run from queries which each require the
input of a beginning date and an ending date. I would like to be requested
to input the criteria once instead of twice.
Thanks

You will need a form for this.
Create a form. Add 2 unbound text controls.
Set their format property to a valid date format.
Name one control "StartDate'.
Name the other control "EndDate".
Add a command button to the form.
Code it's click event:

DoCmd.OpenReport "ReportName", acViewPreview
Me.Visible = False

Name this form "ParamForm".

As criteria for the main report query, and also for the sub-report
query, enter, on the Date field's criteria line:
Between Forms!ParamForm!StartDate and Forms!ParamForm!EndDate

Code the Main Report's Close event:
DoCmd.Close acForm, "ParamForm"

Then when you are ready to run the report, open the form.
Enter the starting and ending dates wanted.
Click the command button.
The report and it's sub report will run.
When the report closes, it will also close the form.

For the Form, select the Command Button.
Display the button's property sheet.
Click on the Event tab.
Click on the event line you wish to code, i.e. the Click event.
Write
[Event Procedure]
on that line.
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those lines write your code for the command button.

DoCmd.OpenReport "ReportName", acViewPreview
Me.Visible = False

For the Report, display the Report's property sheet.
Click on the Close evnet line and follow the same instructions as for
the Form Command button.
Write the code:

DoCmd.Close acForm, "ParamForm"

 

[fredg]

Hi, The report does not return any data. I created the form with the two
text Boxes as instructed. I added a command button with this code in the
click event.
Private Sub Command21_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False
There were some other code in there before the End Sub. Should I delete
that? I tried it both ways anyhow, and it did not run. Don't know what I am
doing wrong.

*** snipped ***

I don't know what you are doing wrong either, as I am here and you are
there. :-(>

Don't use the Wizard when you know the code you wish to use.
So yes, delete any other code in that command button's click event.
The complete code (in that event) should look like this:

Private Sub Command12_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False
End Sub

The same goes for the Report Close event. Don't use the Command Button
wizard.

 

[Christina]

Thanks again. Problem, the only way I k now to create a command button is
with the wizard . Can you please walk me through.

 

[Christina]

Hi, I got to create the botton without the wizard. When I run the report it
gives me Compile error Invalid Outside procedure.

Thanks

Hi, The report does not return any data. I created the form with the two
text Boxes as instructed. I added a command button with this code in the
click event.
Private Sub Command21_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False
There were some other code in there before the End Sub. Should I delete
that? I tried it both ways anyhow, and it did not run. Don't know what I am
doing wrong.

Small question. WHen I am create the command button, do I follow all the
instructions and link the report to the button? Otherwise I need to know what
to do when I create the button so that I can put the code. Hope you
understand my question.

Thanks

On Tue, 24 Feb 2009 19:51:01 -0800, Christina wrote:

Thanks you. I am not sure how to add the code to the click event or code
the main report close event. Please for detail instructions.

Thanks

:

On Tue, 24 Feb 2009 13:34:01 -0800, Christina wrote:

I have a report and a subreport run from queries which each require the
input of a beginning date and an ending date. I would like to be requested
to input the criteria once instead of twice.
Thanks

You will need a form for this.
Create a form. Add 2 unbound text controls.
Set their format property to a valid date format.
Name one control "StartDate'.
Name the other control "EndDate".
Add a command button to the form.
Code it's click event:

DoCmd.OpenReport "ReportName", acViewPreview
Me.Visible = False

Name this form "ParamForm".

As criteria for the main report query, and also for the sub-report
query, enter, on the Date field's criteria line:
Between Forms!ParamForm!StartDate and Forms!ParamForm!EndDate

Code the Main Report's Close event:
DoCmd.Close acForm, "ParamForm"

Then when you are ready to run the report, open the form.
Enter the starting and ending dates wanted.
Click the command button.
The report and it's sub report will run.
When the report closes, it will also close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail



For the Form, select the Command Button.
Display the button's property sheet.
Click on the Event tab.
Click on the event line you wish to code, i.e. the Click event.
Write
[Event Procedure]
on that line.
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those lines write your code for the command button.

DoCmd.OpenReport "ReportName", acViewPreview
Me.Visible = False

For the Report, display the Report's property sheet.
Click on the Close evnet line and follow the same instructions as for
the Form Command button.
Write the code:

DoCmd.Close acForm, "ParamForm"

 

[fredg]

Thanks again. Problem, the only way I k now to create a command button is
with the wizard . Can you please walk me through.

In Design View, click on the Toolbox tool button.
If the Wizard button is down (the button with the wand and falling
stars), click on it to raise it.
Now add your command button. Since the wizard is not turned on, you
can now simply add code to the button's click event using the method I
gave you previously. Don't forget to use the Command button's Caption
property to write whatever button caption you wish.

If you need to use the wizard again in the future, simply open the
toolbox and depress the wizard (the wand with the stars) button.

 

[John W. Vinson]

Hi, I got to create the botton without the wizard. When I run the report it
gives me Compile error Invalid Outside procedure.

That suggests that you have some sort of code that is NOT between a

Private / Public Sub subname...

End Sub

or

Private / Public Function functionname...

End Function

Either a Sub for a Function is a type of Procedure, and most code (except for
global variable definitions or Option settings) needs to be between those
lines.

If you need help figuring out what you have that's misplaced please open the
Module, type Ctrl-A to select it ALL, and paste it to a message here.

 

[Christina]

That suggests that you have some sort of code that is NOT between a

Private / Public Sub subname...

End Sub

or

Private / Public Function functionname...

End Function

Either a Sub for a Function is a type of Procedure, and most code (except for
global variable definitions or Option settings) needs to be between those
lines.

If you need help figuring out what you have that's misplaced please open the
Module, type Ctrl-A to select it ALL, and paste it to a message here.

 

[Christina]

This is for the command button

Option Compare Database

Private Sub BtnRptMonthlyReturnes_Click()
On Error GoTo Err_BtnRptMonthlyReturnes_Click

Dim stDocName As String

stDocName = "MONTHLY RETURNS"
DoCmd.OpenReport stDocName, acPreview

Exit_BtnRptMonthlyReturnes_Click:
Exit Sub

Err_BtnRptMonthlyReturnes_Click:
MsgBox Err.Description
Resume Exit_BtnRptMonthlyReturnes_Click

End Sub
Private Sub Reconciliation_Report_Click()
On Error GoTo Err_Reconciliation_Report_Click

Dim stDocName As String

stDocName = "Reconciliation"
DoCmd.OpenReport stDocName, acPreview

Exit_Reconciliation_Report_Click:
Exit Sub

Err_Reconciliation_Report_Click:
MsgBox Err.Description
Resume Exit_Reconciliation_Report_Click

End Sub
Private Sub Command18_Click()
On Error GoTo Err_Command18_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
Resume Exit_Command18_Click

End Sub
Private Sub Reconciliation_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False
End Sub
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String

stDocName = "Uncashed Checks"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub
Private Sub Command21_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False


Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox Err.Description
Resume Exit_Command21_Click

End Sub
Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

Dim stDocName As String

stDocName = "Uncashed Total"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command22_Click:
Exit Sub

Err_Command22_Click:
MsgBox Err.Description
Resume Exit_Command22_Click

End Sub

This is the one for the report

Private Sub Report_Close()

End Sub

 

[John W. Vinson]

This is the one for the report

Private Sub Report_Close()

End Sub

"John W. Vinson" wrote:

Well, since that sub does absolutely nothing, I'd say just delete it. Just to
clarify: under what circumstances do you get the error message? Only when you
open the report from the command button? What happens if you open the report
from the database Reports window? If you type Ctrl-G to open the VBA editor
and select Debug... Compile <my project>, do you get any error messages? If
so, what line is highlighted?

 

[Christina]

Ok, please let's start again. Right now when I run the report either from
the button or directly from the reports, the report is blank.
This is the code I put in the code builder next to the Event procedure.
Hope I did that right, as I never did it before.

Private Sub Command24_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False

End Sub
When I press CtrG...Compile Db1, codes show up, but nothing highlighted.



This is the criteria in the quer
Between [Forms]![ParamForm]![StartDate] And [Forms]![ParamForm]![EndDate]

 

[John W. Vinson]

Ok, please let's start again. Right now when I run the report either from
the button or directly from the reports, the report is blank.
This is the code I put in the code builder next to the Event procedure.
Hope I did that right, as I never did it before.

Private Sub Command24_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False

End Sub
When I press CtrG...Compile Db1, codes show up, but nothing highlighted.



This is the criteria in the quer
Between [Forms]![ParamForm]![StartDate] And [Forms]![ParamForm]![EndDate]

ParamForm is open at the time you click the button, I hope? What's in
StartDate and EndDate? What's in the table? Perhaps you could post the
complete SQL view of the report's recordsource query.

 

[Christina]

Hi, I have been at this thing for so long and still cant get it. Would you
agree for me to send you the db. I so want to finish this project. It is
1256 KB. All my queries and reports are set....

PLEASE


Thanks

Ok, please let's start again. Right now when I run the report either from
the button or directly from the reports, the report is blank.
This is the code I put in the code builder next to the Event procedure.
Hope I did that right, as I never did it before.

Private Sub Command24_Click()
DoCmd.OpenReport "Reconciliation", acViewPreview
Me.Visible = False

End Sub
When I press CtrG...Compile Db1, codes show up, but nothing highlighted.



This is the criteria in the quer
Between [Forms]![ParamForm]![StartDate] And [Forms]![ParamForm]![EndDate]

Well, since that sub does absolutely nothing, I'd say just delete it. Just to
clarify: under what circumstances do you get the error message? Only when you
open the report from the command button? What happens if you open the report
from the database Reports window? If you type Ctrl-G to open the VBA editor
and select Debug... Compile <my project>, do you get any error messages? If
so, what line is highlighted?

 

[John W. Vinson]

Hi, I have been at this thing for so long and still cant get it. Would you
agree for me to send you the db. I so want to finish this project. It is
1256 KB. All my queries and reports are set....

Well... ordinarily I would only do so with a consulting contract, but it
sounds like just some simple thing that we're just not getting communicated!

Make a copy of the database. Delete any confidential data if any (I won't
spread it around but doesn't hurt to be safe); use Tools... Database
Utilities... Compact and Repair to shrink it down and clean it out. Then zip
it (right click the file in Windows Explorer and choose Send To... Compressed
(zipped) Folder.

Email the zipped file to jvinson <at> wysard of info <dot> com - edit out the
blanks and the obvious punctuation.

It would help me a lot if you would clearly describe just what you're trying
to accomplish and what specific problems you're having. Sure, you've done so
again and again but it gets hard to pull all the bits and pieces together.