Multiple Parameters in a Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

How will I code a multiple parameters in a form to open specific data in a
form. Using a single criteria is not a problem, but if parameters is more
than one it becomes a problem especially if the data to show is in the
subform.
 
Hi Andy:

I have a Form which consists also of a Subform. I created another Form that
consists of parameters(txtbox) to which criteia will be entered and cmd
button to execute the command. Using the Wizard (for adding cmd button to
open specific record) was easy because the Wizard creates the criteria (the
WHERE Clause). Now, i have more that one parameters/criteria to filter the
result of the Form when it opens, e.g. LastName, FirstName, and ID.. My
problem is how will I organize the codes to filter the result based on the
entered data. If you have a similar database like this one, it would be
helpful.

Thanks,

Jologs
 
Hi Jologs,

I hope I did not imply I had an answer. I too am looking for a solution to
a problem Ihave that is similar to yours. I think I have the answer by the
answers to my poast since June 9, 2005 (search for Andy) and from doing a
search on "parameter queries" and Parameter query works." I do know now you
create the form as you did, using combo boxes for the user to select the
inputs/filters that will then be displayed/printed in a report. The report is
created when the user clicks a button on the form he enters the report
parameters in. Now, the actual steps to make this work....still learning. I
have a test form & report I created. I open the form, enter my selections
(parameters) on by use of a combo box and text boxes for dates, and click a
command button to get the report to run & display, but I get #Name? That is
my job for tomrrow ...to figure out why I get #Name?

I hope this helps.

Andy
 
.. e.g. LastName, FirstName, and ID.. My
problem is how will I organize the codes to filter the result based on the
entered data. If you have a similar database like this one, it would be
helpful.
Click to expand...

just build a nice form, with 3 un-bound text boxes (txtLastName,
txtFirstName, txtID)

Remove all parameters from the sql...this makes the sql nice and clean, and
further, getting rid of all that junk and garbage out of the sql query means
that the query is NOT tied to a particular form, and you are free to use
that query anytime.

Note that the above is also better then query parameters since some of the
fields are now optional, and with parameters in a query, this is again a
very messy affair when you want to make some of the conditions optional.

So, now, just build the where clause in code. Assuming the above 3 fields,
you could then use the following code to launch the form (or report, as this
works great for that too).

dim strWhere as string
dim strSql as string

if isnull(txtLastName) = false then

strWhere = "LastName like '" & me.txtLastName & "*''

end if

strSql = strWhere

if isnull(txtFirstName) = false then

strWhere = "FirstName like '" & me.txtFirstName & "*''

end if

if strWhere <> "" then
if strSql <> "" then
strSql = strSql & " and "
end if
strSql = strSql & strWhere
end if

if isnull(txtID) = false then

strWhere = "id = " & id

end if

' at this point, you could open a form to show the matches

docmd.openForm "frmCustomer",,,strWhere

' or, you could stuff the results into a listbox, or lets assume a continues
subform.

me.MySubForm.Form.ReocrdSource = "select * from tblCustomers where " &
strWhere

I have some screen shots of the above code for a continues form here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

And, I have some screen shots for reports that also uses the above EXACT
same approach....

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