A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Allen Browne’s date range vba for more reports



 
 
Thread Tools Display Modes
  #1  
Old May 1st, 2010, 05:44 PM posted to microsoft.public.access.forms
AccessKay
external usenet poster
 
Posts: 106
Default Allen Browne’s date range vba for more reports

I created an unbound form and used Allen Browne’s vba for “Limiting Report to
Date Range”. This works nicely. But I want to use this vba to pull two
other reports. I could make three command buttons and put the report label
name beside them but I don’t think I’ll like how this looks. I created an
option group for my reports that previews the reports on click but it’s not
connected to the date range. So my question is, how can I have an option
group of reports for my date range? I thought I might take a stab at doing
it myself, and post again later with either my success or failure. But I’m
new with vba and have a few questions to get me started.

1.Is it advisable to combine the vba for the date range and the vba for the
option group? They both have on click commands so I’m thinking that one has
to go. So my assumption for the rest of these questions is yes, that I do
need to combine them, but please correct me if I’m wrong.

2.Allen Brown’s vba defines the report first with Dim strReport As String
and then later with strReport = “MyRpt1”. If I want to add two more reports,
do they need to be defined in these same sections? Maybe like Dim strReport2
As String and strReport2=”MyRpt2”??? Is this what I need to do?

3.Then in Allen’s vba, it’s If statements with a strWhere. The code for the
option group is Select Case Me.optReports and then each case ie Case1
strReport = “MyRpt1”, etc. Should this be incorporated into a strWhere
statement? From what I’ve learned so far, I think so but I’m really stumped
about how to do this. Any suggestions?

4.And then comes the DoCmd.OpenReport strReport, acPreview part. I’m
thinking that if I wrote my strWhere statement correctly, then this will work
for whatever option I choose. Correct?

5.And then there is the Error Handler part that I don’t have a clue so maybe
I’ll skip this part for now.

If you’ve read my post this far, then I sincerely thank you. If you think
that I need to give it up then please tell me so. I know I’m in way over my
head. I’ll take any suggestions and give it a try or not.

Many thanks for any replies.
Kay

  #2  
Old May 2nd, 2010, 03:03 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Allen Browne’s date range vba for more reports

You can get a good result by combining:
- an option group for selecting the report
- text boxes for the limiting dates
- a command button to open the report.

Given the code in Method 2 at:
http://allenbrowne.com/casu-08.html
you choose the lines:
strReport = "rptSales" 'Put your report name in these quotes.
strDateField = "[SaleDate]" 'Put your field name in the square brackets
in these quotes.
with something like the following:

Select Case Me.Frame99.Value
Case 1
strReport = "Report1"
strDateField = "[InvoiceDate]"
Case 3
strReport = "SomeOtherReport"
strDateField = "[AppointmentDate]"
Case 3
strReport = "Report9"
strDateField = "[EventDate]"
Case Else
MsgBox "I don't know what to do with option " & Me.Frame99.Value
End Select

The error handler part can stay as it is.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"AccessKay" wrote in message
...
I created an unbound form and used Allen Browne’s vba for “Limiting Report
to
Date Range”. This works nicely. But I want to use this vba to pull two
other reports. I could make three command buttons and put the report
label
name beside them but I don’t think I’ll like how this looks. I created an
option group for my reports that previews the reports on click but it’s
not
connected to the date range. So my question is, how can I have an option
group of reports for my date range? I thought I might take a stab at
doing
it myself, and post again later with either my success or failure. But I’m
new with vba and have a few questions to get me started.

1.Is it advisable to combine the vba for the date range and the vba for
the
option group? They both have on click commands so I’m thinking that one
has
to go. So my assumption for the rest of these questions is yes, that I do
need to combine them, but please correct me if I’m wrong.

2.Allen Brown’s vba defines the report first with Dim strReport As String
and then later with strReport = “MyRpt1”. If I want to add two more
reports,
do they need to be defined in these same sections? Maybe like Dim
strReport2
As String and strReport2=”MyRpt2”??? Is this what I need to do?

3.Then in Allen’s vba, it’s If statements with a strWhere. The code for
the
option group is Select Case Me.optReports and then each case ie Case1
strReport = “MyRpt1”, etc. Should this be incorporated into a strWhere
statement? From what I’ve learned so far, I think so but I’m really
stumped
about how to do this. Any suggestions?

4.And then comes the DoCmd.OpenReport strReport, acPreview part. I’m
thinking that if I wrote my strWhere statement correctly, then this will
work
for whatever option I choose. Correct?

5.And then there is the Error Handler part that I don’t have a clue so
maybe
I’ll skip this part for now.

If you’ve read my post this far, then I sincerely thank you. If you think
that I need to give it up then please tell me so. I know I’m in way over
my
head. I’ll take any suggestions and give it a try or not.

Many thanks for any replies.
Kay

  #3  
Old May 4th, 2010, 11:20 PM posted to microsoft.public.access.forms
AccessKay via AccessMonster.com
external usenet poster
 
Posts: 22
Default Allen Browne’s date range vb a for more reports

Hi Allen,

Thank you very much! This is exactly what I needed. It’s so professional
and user friendly. I plan to use it many times in the future. I appreciate
your help and I also wanted to thank you for your fantastic website. If I
can follow your clear and concise instructions, anyone can. Good job!

Kay


Allen Browne wrote:
You can get a good result by combining:
- an option group for selecting the report
- text boxes for the limiting dates
- a command button to open the report.

Given the code in Method 2 at:
http://allenbrowne.com/casu-08.html
you choose the lines:
strReport = "rptSales" 'Put your report name in these quotes.
strDateField = "[SaleDate]" 'Put your field name in the square brackets
in these quotes.
with something like the following:

Select Case Me.Frame99.Value
Case 1
strReport = "Report1"
strDateField = "[InvoiceDate]"
Case 3
strReport = "SomeOtherReport"
strDateField = "[AppointmentDate]"
Case 3
strReport = "Report9"
strDateField = "[EventDate]"
Case Else
MsgBox "I don't know what to do with option " & Me.Frame99.Value
End Select

The error handler part can stay as it is.

I created an unbound form and used Allen Browne’s vba for “Limiting Report
to

[quoted text clipped - 48 lines]
Many thanks for any replies.
Kay


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #4  
Old May 5th, 2010, 02:35 PM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Allen Browne’s date range vb a for more reports

That's great news. Well done.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"AccessKay via AccessMonster.com" u59222@uwe wrote in message
news:a78523543c945@uwe...
Hi Allen,

Thank you very much! This is exactly what I needed. It’s so professional
and user friendly. I plan to use it many times in the future. I
appreciate
your help and I also wanted to thank you for your fantastic website. If I
can follow your clear and concise instructions, anyone can. Good job!

Kay


Allen Browne wrote:
You can get a good result by combining:
- an option group for selecting the report
- text boxes for the limiting dates
- a command button to open the report.

Given the code in Method 2 at:
http://allenbrowne.com/casu-08.html
you choose the lines:
strReport = "rptSales" 'Put your report name in these quotes.
strDateField = "[SaleDate]" 'Put your field name in the square
brackets
in these quotes.
with something like the following:

Select Case Me.Frame99.Value
Case 1
strReport = "Report1"
strDateField = "[InvoiceDate]"
Case 3
strReport = "SomeOtherReport"
strDateField = "[AppointmentDate]"
Case 3
strReport = "Report9"
strDateField = "[EventDate]"
Case Else
MsgBox "I don't know what to do with option " & Me.Frame99.Value
End Select

The error handler part can stay as it is.

I created an unbound form and used Allen Browne’s vba for “Limiting
Report
to

[quoted text clipped - 48 lines]
Many thanks for any replies.
Kay


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.