Unbound Form

[Mac_Wood]

Hello Learned Friends,

I have an unbound form with start date and end date text boxes. The code behind the form allows a combination of dates to produce a report, but only one named report. I would like, with your help, to give the user a choice of reports to preview and/or print. Thank you in advance for divulging your knowledge.

 

[RuralGuy]

How about creating a table with the reports you want to provide and using a ComboBox based on this table to let the user select the report they want?

 

[Mac_Wood]

Thanks RG for your suggestions. Do I just add the report names as text in the table? What extra code do I need to add to the 'On Click' event? This is the code I have so far courtesy of Mr Allen Browne's web site:

Dim strReport As String ‘Name of report to open.
Dim strfield As String ‘Name of your date field.
Dim strwhere As String ‘Where condition for OpenReport.
Const conDateFormat “\#mm\/dd\/yyyy\#”
strReport = “rptSales”
strField = “SaleDate”
If IsNull(Ne.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then ‘End date, but no start.
strWhere = strField & “ “ & Format(Me.txtsndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then ‘Start date, but no End.
strWhere = strField & “ > “ & Format(Me.txtStartDate, conDateForma
Else Both start: and end dates.
strWhere strField & “ Between “ & Format(Me.txtStartDate, conDat & “ And “ & Format(Me.txtEndDate, conDateFormat)
End If
End If
Debug.Print strwhere ‘For debugging purposes only.
DoCmd.OpenReport strReport, acviewPreview, , strwhere

 

[RuralGuy]

I would have two text fields. 1st one is the actual name of the report and the 2nd field is a more friendly name to display in the ComboBox.

 

[AN60]

Another way is to use buttons to launch your reports. Create your reports so they use the date range(s) on your unbound form then add a button for each report to your unbound form. It's very fast but not as nice as the combo method if you have heaps of reports.

 

[Mac_Wood]

Unbound form

Yes I could but I was kinda hoping to use the combo box method as there are quite a few reports.

 

[Mac_Wood]

Can anybody help?

Is there anybody out there who could give me some pointers for how to enable my users to print their selected report?

 

[RuralGuy]

Do you have a table of report names and friendly names? Would you like the report to start when they select the report from the ComboBox or after they push a button? Can you do all of the Date verification or do you need assistance?

 

[Mac_Wood]

Thanks RG for responding. I have a table with the report names and friendly names. I would like the user to click a button to preview the report and then print it. The form already has an OK and a Cancel Button the OK button's On Click event has what I think you mean by date verification, see below

Dim strReport As String ‘Name of report to open.
Dim strfield As String ‘Name of your date field.
Dim strwhere As String ‘Where condition for OpenReport.
Const conDateFormat “\#mm\/dd\/yyyy\#”
strReport = “rptSales”
strField = “SaleDate”
If IsNull(Ne.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then ‘End date, but no start.
strWhere = strField & “ “ & Format(Me.txtsndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then ‘Start date, but no End.
strWhere = strField & “ > “ & Format(Me.txtStartDate, conDateForma
Else Both start: and end dates.
strWhere strField & “ Between “ & Format(Me.txtStartDate, conDat & “ And “ & Format(Me.txtEndDate, conDateFormat)
End If
End If
Debug.Print strwhere ‘For debugging purposes only.
DoCmd.OpenReport strReport, acviewPreview, , strwhere

 

[RuralGuy]

Once you get the ComboBox displaying the Friendly Report Name then you just change the OpenReport to:
DoCmd.OpenReport Me.ComboBox.Column(0), acviewPreview, , strwhere

 

[Mac_Wood]

Thanks a million RG. I've not tried it yet so watch this space but thanks anyway for helping me out.

 

[Mac_Wood]

It works

Tried out the code it works great with a couple of alterations. I took the Columns(0) out and used the name of my combo box. Thanks again RG

 

[RuralGuy]

You're most welcome. Glad I could help.

 

[Mac_Wood]

Date Limited Reports

Hello Learned Friends,

I have an unbound form with start date and end date text boxes. The code behind the form allows a combination of dates to produce a report, but only one named report. I would like, with your help, to give the user a choice of reports to preview and/or print. Thank you in advance for divulging your knowledge.

I have encountered a problem with my form and I was wondering whether anyone could help out? Even though I enter a start and end date the resulting report contains records from before the start date. When I alter the report query to ask for a parameter value which in this case is a start date and an end date the report does not contain the 'old' records

 

[RuralGuy]

How are you referencing the StartDate and EndDate controls?

 

[Mac_Wood]

Unbound form

How are you referencing the StartDate and EndDate controls?

txtStartDate, txtEndDate.

 

[RuralGuy]

You need to go through the Forms collection.
Forms!YourFormName!txtStartDate using Your Form Name of course.

 

[Mac_Wood]

Unbound form

Hi RG,
Sorry for the delay in replying to your suggestion. Does the code you suggested need to go in a query or in one of the form's controls? I have an ok button with an On click event procedure as detailed below:

Private Sub OK_Click ()
Dim strReport As String 'As Name of report to open
Dim strField As String 'Name of your date field
Dim strWhere As String 'Where condition for OpenReport
Const conDateFormat = "\#dd\/mm\/yyyy\#"

strReport = "ForkliftsQueryReport"
strField = "EntryDate"

If IsNull (Me.txtStartDate) Then
If Not IsNull (Me.txtEndDate) Then 'End date, but no start
strWhere = strField & " =< " & Format (Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " => " & Format (Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport Me.ComboBox15, acviewPreview, , strwhere

 

[DanG]

What I have done:

Unbound form has 2 date fields like yours on the form and all queries look for those dates. I also have an additional combo box with "district names" because I want to narrow it down even more for my reports. to handle the district box I use:

Private Sub ReportButton_Click()
Dim stDocName As String

stDocName = ReportPick.Value

DoCmd.OpenReport stDocName, acPreview, , "IIf([Forms]![Compliance Lair Reporting]![DistrictPick] Is Null,"""",[txtDistname]=[Forms]![Compliance Lair Reporting]![DistrictPick])"
End Sub

"txtDistrictname" has to be in the report hidden or not for the above to work.

Also on the form I have one last field (list field) to show all the reports that apply to this collection. You will see in the code above "ReportPick". In the list box row source I use:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "offices*" Or (MSysObjects.Name) Like "complaints*") AND ((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;

It uses "MSysObjects" to pick up on the reports as I add them to the collection (note that the "like statement" in the SQL above). As long as I use "offices" or "compliance" in the naming of my reports it will add them to my list box.

This is just my approach and it works well for me.
And by the way I couldn't have put this all together in the first place without all the help I have received from the people on this board.

 

[RuralGuy]

It looks like you are setting up a valid WhereCondition argument that should work unless your [EntryDate] field is *not* a DateTime field. I would also add the brackets by making this change: strField = "[EntryDate]"