Can I enter parameter values for a query into a form?

[RBohannon]

I have a report with most fields populated by a query. However, some
of the fields are variable in such a way that their values cannot be
queried from a table. At present the values for these fields are
being entered by the user as parameter values when the query is run.
But there are four fields which need to be entered, and the pop-up
dialog boxes can be annoying.

I would like to have a form where the user can enter the data for
these four fields into corresponding text boxes and then have those
special fields in the report populated from the text boxes of the form
(probably by clicking a command button on the form.)

I have some ideas on how this could be done, but they seem overly
complicated. What would be the easiest/best way to do this? I have
included the SQL generated by Access2000 for my current query.
SELECT [tblExam_Data].[Title 1] & " " & [tblExam_Data].[Salary
Grade] AS TandG, tblExam_Data.[Exam Number], tblList_Data.[Type of
Appt], [Enter Location] AS Expr2, tblPersonal.Pho ne,
tblList_Data.[Appt Date], tblExam_Data.[Type of Exam],
[tblPersonal].[FName] & " " & [tblPersonal].[LName] AS Name,
tblList_Data.[Final Rating], [Enter Type of Appointment] AS Expr1

FROM tblPersonal INNER JOIN (tblExam_Data INNER JOIN tblList_Data ON
tblExam_Data.[Exam Number] = tblList_Data.[Exam Number]) ON
tblPersonal.ID = tblList_Data.ID

WHERE (((tblList_Data .[Final Rating])>=[Enter Lowest Score]) AND
(([Enter Exam Number])=[tblExam_Data].[Exam Number]) AND
((tblList_Data.[Remove from Canvas List])=No))

ORDER BY tblList_Data.[Final Rating] DESC;

Any assistance is appreciated.

[J. Paul Schmidt]

The free downloadable sample database at www.bullschmidt.com/access uses the
query by form concept so that on the invoices dialog one can optionally choose
a rep, a customer, and perhaps a date range, click on a button that says
"Input," and then have the invoice form open up showing all the invoices that
match the criteria.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Yea r(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called cmdInput to open
the invoices form with the following code behind the OnClick property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenRepor t "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And
[Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks (i.e.
Nulls) in fields that are going to be used with Like in any criteria for that
field. Otherwise the blanks wouldn't be shown.

For example (based on what is entered into a last name search field):

Like 'Smith' would show Smith records

Like '' would show no records (probably not what one would want)

Like '*' would show all records

And to counter that I like to have the search fields have a DefaultValue of *
and not allow the search fields to be blank.

Or a more complicated solution would be to create the query's SQL statement
dynamically so that the criteria on a particular field isn't used unless
needed.

For example:

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Not IsNull(Rep) Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Not IsNull(Customer ) Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If
Best regards,
J. Paul Schmidt, Freelance Access and ASP Web Developer
www.Bullschmidt.com
Classic ASP Design Tips, ASP Web Database Demo, ASP Bar Chart Tool...

[RBohannon]

Thanks for the help. I'm still having some difficulty.

Is there a RecordSource for frmInvDialog? I can't duplicate the way
it works with the combo boxes.

jp******@aol.co mx (J. Paul Schmidt) wrote in message news:<20******* *************** *****@mb-m29.aol.com>...

The free downloadable sample database at www.bullschmidt.com/access uses the
query by form concept so that on the invoices dialog one can optionally choose
a rep, a customer, and perhaps a date range, click on a button that says
"Input," and then have the invoice form open up showing all the invoices that
match the criteria.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Yea r(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called cmdInput to open
the invoices form with the following code behind the OnClick property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenRepor t "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And
[Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks (i.e.
Nulls) in fields that are going to be used with Like in any criteria for that
field. Otherwise the blanks wouldn't be shown.

For example (based on what is entered into a last name search field):

Like 'Smith' would show Smith records

Like '' would show no records (probably not what one would want)

Like '*' would show all records

And to counter that I like to have the search fields have a DefaultValue of *
and not allow the search fields to be blank.

Or a more complicated solution would be to create the query's SQL statement
dynamically so that the criteria on a particular field isn't used unless
needed.

For example:

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Not IsNull(Rep) Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Not IsNull(Customer ) Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If
Best regards,
J. Paul Schmidt, Freelance Access and ASP Web Developer
www.Bullschmidt.com
Classic ASP Design Tips, ASP Web Database Demo, ASP Bar Chart Tool...