Attaching Calendar control to Combo Box of Reports 1

[kentwoodjean]

I have place an ActiveX calendar control on my form along with a list of reports in a combo box. The control works perfectly but will only open one of the several reports even though they each have the date criteria of:

Between [Forms]![frmLookUpReport]![FromDate] And [Forms]![frmLookUpReport]![ToDate]

I have looked at the posts on this site along with other posts and examples but cannot find one doing what I am. Is it possible that the one calendar control to open 1 of many reports listed once it is selected? If so, can someone please assist me with the code, whether it be on the query, calendar control, or OK button?

 

[MaxEd]

If you are opening up the reports using VBA then you need to make sure the names of the reports are referenced correctly. The way I use the combo box is I show the report name that is meaningful to the user and the rptName as the value that the combobox retains. My combo box Row Source Type are values. For example: "Report 1";rpt1;"Report 2";rpt2

I bound that combox to the 2nd column which is the actual report name.

So when I do the docmd.openreport I just point it to the name of that combo box.

-Laughter works miracles.

 

[kentwoodjean]

Thanks for your response. I kind of wondered if that would be the case, where I would need to list each of the reports. My table is set up as you described, with the real table name and then the name to appear on the list. I tried inputing as you indicated, but it is not working, with an error stating that I am missing operators. Let me show you a few entries on my table along with the current look of my command before trying your suggestion. If you can explain please what parts of the existing Raw source say or go and what it would look like using a couple of my reports. Again, I am not good at writing code and normally rely on what Access gives me using the wizard. Thanks.

tblReports
ReportName ReportLstg
POSDailyTotals05 Daily - BlueChoice
SubPOSMo05 Mo - Blue Choice
SubPOSwkly05 Wkly - Blue Choice


SELECT tblReports.ReportName, tblReports.ReportLstg FROM tblReports;

 

[RezaNana]

Forgive me if i'm wrong but, i think your problem will be solved if you use the "Report Date Range " from the Ms Access templates.You can use the "Inventory Control" templates. And i think you should use your code inside query.

 

[kentwoodjean]

I did go to the template you referred to and did learn one thing, and that is I can enter the date range parameters on the report as opposed to the query. That I did not know I could do and would save me a ton of time as each new year comes. The result of this is a box requesting the begin and end dates (same as my calendar) and the command button opens the 1 report identified. I can do this today with no problem.

I would like to know how to modify the Row source property in my combo box properties so once a report is selected and the date parameters are set, the command button will only open the selected report. Right now I get the same report each time regardless of my choice.

I am sure that I will need to use the words "Select" and "From" and as I see it, each of my reports will need to be entered. I just need some help to Format it using a couple of my report names so I can continue on, unless there is another way. I.e. Do I use the Select and From with each report name, or list all reports and use the Select and From at the beginning and end??

 

[MaxEd]

In Design View, the combo box that you are entering the reports, open up the properties for it (right click or just hold Alt-Enter keys together). In the data tab there should be "Row Source Type" (3rd line from the top), change it to Value List. Just below that row is Row Source. That's where you would enter

"Report 1";Report1;"Report 2";Report2

Make sure you have the double Quotes if you are Enter the report names.

-Laughter works miracles.

 

[kentwoodjean]

Thanks for helping to clear that up for me. It was the "Value List" that did the trick. Now I just have one final problem. The OK command button to open the report. The Wizard does not help since that typcially points to a specifically named report via a table/query. I have tried a few different codes but they are not working. Let me show you my last code for the button and perhaps you can assist with how to better format and open the selected report. Your help is really appreciated.

Private Sub cmdOK_Click()
On Error GoTo Err_CmdOK_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLookUpReport"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdOK_Click:
Exit Sub

Err_CmdOK_Click:
MsgBox Err.Description
Resume Exit_CmdOK_Click

End Sub

 

[MaxEd]

Change

FROM: stDocName = "frmLookUpReport"
TO: stDocName = ComboBoxName

See if this works for you.

 

[kentwoodjean]

I am sorry I am so late in getting back to you but I have had a bit of down time from my computer. Thanks for indicating the minor change I had to make but it works beautifully for me now and will serve as a model for other similar situations.