Runtime Report generated based on a Form

[laserized]

Hi to all
My question is
I ve two tables in access and I ve a relationship between the ids which
is one to many
and I prepared a form which has some fields in it, what i want to do is
that user can enter criteria in the textboxes, not all ofthem but one
of them can be enough like:
UserName:
id:
Tool Type:

or so. When user clicks the command button, I should be able to
generate report in runtime and print the report in an Excel document. I
know openning a report and printing in excel document can be done by
macro but, the problem is "generating a runtime report among the
criterias in the form"

I hope its clear.
Thanx for help
metan.

 

[Allen Browne]

You can use the WhereCondition of the OpenReport action to limit the report
to just the entries that match your criteria.

For a couple of examples, see:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html
and:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

 

[laserized]

First of all Thanx for your quick reply Allen. Appriciated

Well I can see that with the strWhere condition string I can limit the
report among some criterias.
Well I m totally lost in these VB codes below:

--> strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)

--> strWhere = "[ID] = " & Me.[ID]

Dont know but correct me pls, can write a code like this? I cant try it
right now cuz my access is not installed in this public comp.

strWhere = [toolType] like Me.[toolType]

?

and another question.
How can i save my report in excel from VB code?
Because i know that i cannot append macro and code builder in same
button.

Thanx for ur help again
appriciated.

 

[laserized]

In addition,
The report does not need to be generated in runtime yes?
the limitation with the strWhere string can be enough?

Also to correct my last post:
Can I do this?


if isNull(Me.[toolType]) = false then
strWhere = myReport.[toolType] like Me.[toolType]
if isNull(Me.[UserName]) = false then
strWhere = myReport.[UserName] like Me.[UserName]


and so on?

 

[Allen Browne]

Correct: you create the report ahead of time, and just filter it to the
values the user has chosen.

The click event procedure of your command button will contain something like
this:

Private Sub cmdPrint_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.ToolType) Then
strWhere = strWhere & "([ToolType] = """ & Me.ToolType & ") AND "
End If

If Not IsNull(Me.UserName) Then
strWhere = strWhere & "([UserName] = """ & Me.UserName & """) AND "
End If

'etc for other boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End Sub

Once the report is open in preview mode, there is a button on the toolbar
for Office Links. If you drop down the options for this button, it includes
Analyze with Excel. Click this choice exports the report to Excel.

There are other choices that might be better, such as using
TransferSpreadsheet to output directly to Excel. This does not have a
WhereCondition like OpenReport does, but it is possible to use the same
technique to build up a filter string, and programmatically set the Filter
of the report in its Open event. If you want more details and you have the
code above working, post a reply to this thread asking for further details
on this.

 

[laserized]

Hello again,
Well I ve tried your code above but it just doesnt work. I did the 7 th
part of the second link you gave me
which is

Open the report in Design View, and add two text boxes to the report
header for displaying the date range. Set the ControlSource for these
text boxes to:
=Forms.frmWhatDates.txtStartDate
=Forms.frmWhatDates.txtEndDate

it just changes the field that i've entered in textbox in the form.
What I want to to is that
for example when I enter "Eclipse" to the ToolType textBox I want only
to be view the other attributes in the report where the toolType is
"Eclipse". Like a Where condition in a query.

Helps appriciated
thanx
metan

 

[Allen Browne]

Not sure which was the 7th part of the link, but do I understand that you
have the code running (building strWhere as in my previous reply), and you
want to show this filter on the report?

Try a text box bound to:
=[Report].[Filter]
Unfortunatly, this retains the value of the previously saved filter, even if
it is no applied.

 

[laserized]

I think I ve solved my problem with a code like this

Dim strWhere as String
Dim rptForm as String

rptForm = "DenemeReport"

'lot of if statements here bec of the form textboxes
'example of one if here

if isNull(Me.Text_LicenseType) = False then
strWhere = "select license_Type, blah, blah, blah, from
LicenseStatus where License_Type = " & Me.Text_LicenseType & ";"
End If

DoCmd.OpenReport rptForm, acViewPreview, strWhere



Well code works properly and shows only what I entered in the textbox.
Like: When I enter Floating it shows only the attributes of users where
LicenseType is Floating.
Well the new problem is that, when I enter my criteria in form, it asks
me again by prompting a dialog box. So that I ve to enter LicenseType
where I ve already wrote in the form.
How Can I prevent that now?

In Addition, I still have the problem that, I ve to save this report in
an Excel document like OutputTo functionality in Macro. How can I do
that in VB?

Thanx for your great helps. Appricitated.
metan

 

[Allen Browne]

I am not sure why your form asks for the value twice; I am not clear if this
form is unbound, or bound. If bound to a table, you will need unbound boxes
to enter the parameters, as well as the boxes that are bound to your fields.

The OutputTo action does not provide the WhereCondition, so you create the
string the same way but have to pass it to the report differently. The way
to do that is the use a global string variable to hold the WhereCondition
string, and apply it to the report's Filter property in its Open event.

1. Click the Modules tab of the Database window.
Click New. Access opens a new code window.
Just below the Option statements, enter:
Public gstrReportFilter As String
Save the module with a name such as basGlobal.
Close.

2. Open your report in design view.
Open the Properties box (View menu.)
Make sure its Title reads Report (i.e. you are looking at the properties of
the report, not a control.)
On the Event tab, set the On Open property to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:
If gstrReportFilter <> vbNullString Then
Me.filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
Save. Close.

3. Open the code where you currently have:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Replace that line with:
gstrReportFilter = strWhere
DoCmd.OutputTo acOutputReport, "Report1", acFormatXLS, _
"C:\MyFolder\MyFile.xls", True
Save.