form or report or ? for user input

[MNJoe]

What is the best way to have a user enter in some criteria that will update
the query for a report. I have the report completely done and have 3 message
boxes that appear for the input. Everything works perfectly. Now it is just a
matter of creating an input form or what ever, so I can do a little
formatting and checking of the input for simple errors that are made. Then
have these used in the query that is associated with the report.

 

[Dennis]

The best way is with a form with 3 text boxes for your query parameters and a
command button. Put code behind the command button to validate the input in
your boxes and then if OK print the report. Your query then needs to change
its criteria options to access the information on the form instead using the
following syntax.
[Forms]![FormName]![TextBoxname]

 

[fredg]

What is the best way to have a user enter in some criteria that will update
the query for a report. I have the report completely done and have 3 message
boxes that appear for the input. Everything works perfectly. Now it is just a
matter of creating an input form or what ever, so I can do a little
formatting and checking of the input for simple errors that are made. Then
have these used in the query that is associated with the report.

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a CustomerID number you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the CustomerID field as well as the
Customer Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CustomerID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
CustomerID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's CustomerID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

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

Run the Report.
The report will open the form.

Find the CustomerName in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.

 

[Albert D. Kallal]

Two ways:

1) if *all* values are *always* to be entered, and you don't want to write
code, then build a un-bound prompt form


In the sql, for the parameters simple go:


= forms!NameOfForm!NameOfTextBox.

The above works well if you just have a few parameters, and you *always* are
going to enter all values.

If you willing to write a bit of code, then read on:

----------------

use the "where" clause, and make the reports sql *without* any
parameters..and you not need to change the params..

So, you can build a un-bound form (a un-bound form is a form that is NOT
attached to a table - these forms are typically designed for user interface
face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar controls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

 

[MNJoe]

OK within the Query I have put in place of the msgbox inputs

[forms]![material requirement form]![Customer_ID]
[forms]![Matterial Requirement form]![Starting_Work_Order_ID]
[forms]![Matterial Requirement form]![Ending_Work_Order_ID]

I have created a form with 3 inputs (Unbound) and named them as above with
some of my formatting. (Not all of it yet). On the form I created a START
button with the code below.

Private Sub CMD_Start_Click()
DoCmd.OpenReport [Reports]![Material Requirement Report], acViewPreview
End Sub

When I fill in the 3 inputs and then click the START button I get the error.

Run-Time error '2451';
The report name 'Material Requirement Report' you entered is misspelled or
refers to a report that isn't open or doesn't exist.

Is the way I have it named or the length of the name wrong?
I have the entire Access file named "Material Requirement"
the query is "Material Requirement Query"
the report is just "Material Requirement Report"
and the form is "Material Requirement Form"
is something getting meesed up with this nameing of the parts?


--
MNJoe

The best way is with a form with 3 text boxes for your query parameters and a
command button. Put code behind the command button to validate the input in
your boxes and then if OK print the report. Your query then needs to change
its criteria options to access the information on the form instead using the
following syntax.
[Forms]![FormName]![TextBoxname]

What is the best way to have a user enter in some criteria that will update
the query for a report. I have the report completely done and have 3 message
boxes that appear for the input. Everything works perfectly. Now it is just a
matter of creating an input form or what ever, so I can do a little
formatting and checking of the input for simple errors that are made. Then
have these used in the query that is associated with the report.

 

[MNJoe]

With a little bit of modification for my report, FREDG's reply worked great.
Thank you all for the help.