Form dependent queries with multiple criteria options

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

Guest

Is it possible to do the following in MS Access and if it is please outline
the conceptual idea of what needs to be done?

I would like the user to open a form [SearchForm] and enter a ID number
(primary key), or a last name, or a first name in a textbox named
[SearchCriteria] located on that form. After hitting a submit button
[SearchButton], all records that contain i.e. the same first name would be
displayed in a subform or if the use choose to enter a last name, then all
records that contain the identified last name would be displayed. The user
would some how select the desired record from the list and a report on the
selected record would be generated.

This seems to be a logical approach to a simple problem, however I am only
finding bits and pieces for a solution in the discussion groups and I can't
seem to put it all together.
 
If you just want to search on one field (but be able to choose which one),
you can implement this with copy'n'paste (i.e. without having to write any
new code):
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

If you want to give the user the option to enter multiple criteria, this one
demonstrates how to write the filtering code for the specific fields you
need:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Once you have the form filtered correctly, you can then use the form's
Filter in the WhereCondition of OpenReport. The "Trouble-shooting and
Extending" section in the last link above discusses that.
 
Hi Allen

I have created a form that provides the parameter to the query that
functions very well, however this is what happens when I hit the submit
button on my form. After entering the search criteria in the form, and
hitting enter, the parameter value window opens and the words that are typed
in the form are displayed above the entry box in the parameter value pop up
window. If I enter the search criteria again in the parameter value box the
query runs perfect.

How can I prevent the parameter value window from opening and having to
enter the search criteria twice?

Allen Browne said:
If you just want to search on one field (but be able to choose which one),
you can implement this with copy'n'paste (i.e. without having to write any
new code):
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

If you want to give the user the option to enter multiple criteria, this one
demonstrates how to write the filtering code for the specific fields you
need:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Once you have the form filtered correctly, you can then use the form's
Filter in the WhereCondition of OpenReport. The "Trouble-shooting and
Extending" section in the last link above discusses that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Prohock said:
Is it possible to do the following in MS Access and if it is please
outline
the conceptual idea of what needs to be done?

I would like the user to open a form [SearchForm] and enter a ID number
(primary key), or a last name, or a first name in a textbox named
[SearchCriteria] located on that form. After hitting a submit button
[SearchButton], all records that contain i.e. the same first name would be
displayed in a subform or if the use choose to enter a last name, then all
records that contain the identified last name would be displayed. The user
would some how select the desired record from the list and a report on the
selected record would be generated.

This seems to be a logical approach to a simple problem, however I am only
finding bits and pieces for a solution in the discussion groups and I
can't
seem to put it all together.
Click to expand...
Click to expand...
 
Acces pops up the parameter window if it cannot resolve the name.

For example, you may have misspelled the name, or the form may not be open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Prohock said:
Hi Allen

I have created a form that provides the parameter to the query that
functions very well, however this is what happens when I hit the submit
button on my form. After entering the search criteria in the form, and
hitting enter, the parameter value window opens and the words that are
typed
in the form are displayed above the entry box in the parameter value pop
up
window. If I enter the search criteria again in the parameter value box
the
query runs perfect.

How can I prevent the parameter value window from opening and having to
enter the search criteria twice?

Allen Browne said:
If you just want to search on one field (but be able to choose which
one),
you can implement this with copy'n'paste (i.e. without having to write
any
new code):
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

If you want to give the user the option to enter multiple criteria, this
one
demonstrates how to write the filtering code for the specific fields you
need:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Once you have the form filtered correctly, you can then use the form's
Filter in the WhereCondition of OpenReport. The "Trouble-shooting and
Extending" section in the last link above discusses that.

Prohock said:
Is it possible to do the following in MS Access and if it is please
outline
the conceptual idea of what needs to be done?

I would like the user to open a form [SearchForm] and enter a ID number
(primary key), or a last name, or a first name in a textbox named
[SearchCriteria] located on that form. After hitting a submit button
[SearchButton], all records that contain i.e. the same first name would
be
displayed in a subform or if the use choose to enter a last name, then
all
records that contain the identified last name would be displayed. The
user
would some how select the desired record from the list and a report on
the
selected record would be generated.

This seems to be a logical approach to a simple problem, however I am
only
finding bits and pieces for a solution in the discussion groups and I
can't
seem to put it all together.
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen

Thanks for helping me along the way, I feel that I am very close to working
this out.

Is it possible to have the user enter a first or last name in a single
unbound textbox on a form and run a query that will pull up all records
matching either the first or last name entered?

This is what I have so far, it only works when the last name is entered.

VBA

Private Sub cmdFilter_Click()
Dim strWhere As String
If Not IsNull(Me.txtFilterMainName) Then
strWhere = Me.txtFilterMainName
strWhere = "([FirstName] Like ""*" & Me.txtFilterMainName & "*"") or "
End If

If Not IsNull(Me.txtFilterMainName) Then
strWhere = Me.txtFilterMainName
strWhere = "([LastName] Like ""*" & Me.txtFilterMainName & "*"") "
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub

query SQL
SELECT qryRecordSource.StudentID, qryRecordSource.FirstName,
qryRecordSource.LastName, qryRecordSource.CurrentGrade
FROM qryRecordSource
GROUP BY qryRecordSource.StudentID, qryRecordSource.FirstName,
qryRecordSource.LastName, qryRecordSource.CurrentGrade
HAVING (((qryRecordSource.FirstName) Like "*" &
Forms!StudentReport!txtFilterMainName & "*") Or ((qryRecordSource.LastName)
Like "*" & Forms!StudentReport!txtFilterMainName & "*"));

Allen Browne said:
Acces pops up the parameter window if it cannot resolve the name.

For example, you may have misspelled the name, or the form may not be open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Prohock said:
Hi Allen

I have created a form that provides the parameter to the query that
functions very well, however this is what happens when I hit the submit
button on my form. After entering the search criteria in the form, and
hitting enter, the parameter value window opens and the words that are
typed
in the form are displayed above the entry box in the parameter value pop
up
window. If I enter the search criteria again in the parameter value box
the
query runs perfect.

How can I prevent the parameter value window from opening and having to
enter the search criteria twice?

Allen Browne said:
If you just want to search on one field (but be able to choose which
one),
you can implement this with copy'n'paste (i.e. without having to write
any
new code):
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

If you want to give the user the option to enter multiple criteria, this
one
demonstrates how to write the filtering code for the specific fields you
need:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Once you have the form filtered correctly, you can then use the form's
Filter in the WhereCondition of OpenReport. The "Trouble-shooting and
Extending" section in the last link above discusses that.

Is it possible to do the following in MS Access and if it is please
outline
the conceptual idea of what needs to be done?

I would like the user to open a form [SearchForm] and enter a ID number
(primary key), or a last name, or a first name in a textbox named
[SearchCriteria] located on that form. After hitting a submit button
[SearchButton], all records that contain i.e. the same first name would
be
displayed in a subform or if the use choose to enter a last name, then
all
records that contain the identified last name would be displayed. The
user
would some how select the desired record from the list and a report on
the
selected record would be generated.

This seems to be a logical approach to a simple problem, however I am
only
finding bits and pieces for a solution in the discussion groups and I
can't
seem to put it all together.
Click to expand...
Click to expand...
Click to expand...
 
Allen, Answered my own question: I used

VBA
strWhere = "[FirstName] Like '*" & Me.txtFilterMainName & "*' Or " &
"[LastName] Like '*" & Me.txtFilterMainName & "*'"

and I was able to join them together and everything works just fine.

Thanks Again

Prohock said:
Hi Allen

Thanks for helping me along the way, I feel that I am very close to working
this out.

Is it possible to have the user enter a first or last name in a single
unbound textbox on a form and run a query that will pull up all records
matching either the first or last name entered?

This is what I have so far, it only works when the last name is entered.

VBA

Private Sub cmdFilter_Click()
Dim strWhere As String
If Not IsNull(Me.txtFilterMainName) Then
strWhere = Me.txtFilterMainName
strWhere = "([FirstName] Like ""*" & Me.txtFilterMainName & "*"") or "
End If

If Not IsNull(Me.txtFilterMainName) Then
strWhere = Me.txtFilterMainName
strWhere = "([LastName] Like ""*" & Me.txtFilterMainName & "*"") "
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub

query SQL
SELECT qryRecordSource.StudentID, qryRecordSource.FirstName,
qryRecordSource.LastName, qryRecordSource.CurrentGrade
FROM qryRecordSource
GROUP BY qryRecordSource.StudentID, qryRecordSource.FirstName,
qryRecordSource.LastName, qryRecordSource.CurrentGrade
HAVING (((qryRecordSource.FirstName) Like "*" &
Forms!StudentReport!txtFilterMainName & "*") Or ((qryRecordSource.LastName)
Like "*" & Forms!StudentReport!txtFilterMainName & "*"));

Allen Browne said:
Acces pops up the parameter window if it cannot resolve the name.

For example, you may have misspelled the name, or the form may not be open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Prohock said:
Hi Allen

I have created a form that provides the parameter to the query that
functions very well, however this is what happens when I hit the submit
button on my form. After entering the search criteria in the form, and
hitting enter, the parameter value window opens and the words that are
typed
in the form are displayed above the entry box in the parameter value pop
up
window. If I enter the search criteria again in the parameter value box
the
query runs perfect.

How can I prevent the parameter value window from opening and having to
enter the search criteria twice?

:

If you just want to search on one field (but be able to choose which
one),
you can implement this with copy'n'paste (i.e. without having to write
any
new code):
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

If you want to give the user the option to enter multiple criteria, this
one
demonstrates how to write the filtering code for the specific fields you
need:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Once you have the form filtered correctly, you can then use the form's
Filter in the WhereCondition of OpenReport. The "Trouble-shooting and
Extending" section in the last link above discusses that.

Is it possible to do the following in MS Access and if it is please
outline
the conceptual idea of what needs to be done?

I would like the user to open a form [SearchForm] and enter a ID number
(primary key), or a last name, or a first name in a textbox named
[SearchCriteria] located on that form. After hitting a submit button
[SearchButton], all records that contain i.e. the same first name would
be
displayed in a subform or if the use choose to enter a last name, then
all
records that contain the identified last name would be displayed. The
user
would some how select the desired record from the list and a report on
the
selected record would be generated.

This seems to be a logical approach to a simple problem, however I am
only
finding bits and pieces for a solution in the discussion groups and I
can't
seem to put it all together.
Click to expand...
Click to expand...
Click to expand...
 
Great! You have it all sorted out.

If you are interested in combining filters from several unbound text boxes,
you might enjoy this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The sample database illustrates how to handle different field types, a date
range, etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Prohock said:
Allen, Answered my own question: I used

VBA
strWhere = "[FirstName] Like '*" & Me.txtFilterMainName & "*' Or " &
"[LastName] Like '*" & Me.txtFilterMainName & "*'"

and I was able to join them together and everything works just fine.

Thanks Again

Prohock said:
Hi Allen

Thanks for helping me along the way, I feel that I am very close to
working
this out.

Is it possible to have the user enter a first or last name in a single
unbound textbox on a form and run a query that will pull up all records
matching either the first or last name entered?

This is what I have so far, it only works when the last name is entered.

VBA

Private Sub cmdFilter_Click()
Dim strWhere As String
If Not IsNull(Me.txtFilterMainName) Then
strWhere = Me.txtFilterMainName
strWhere = "([FirstName] Like ""*" & Me.txtFilterMainName & "*"")
or "
End If

If Not IsNull(Me.txtFilterMainName) Then
strWhere = Me.txtFilterMainName
strWhere = "([LastName] Like ""*" & Me.txtFilterMainName & "*"")
"
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub

query SQL
SELECT qryRecordSource.StudentID, qryRecordSource.FirstName,
qryRecordSource.LastName, qryRecordSource.CurrentGrade
FROM qryRecordSource
GROUP BY qryRecordSource.StudentID, qryRecordSource.FirstName,
qryRecordSource.LastName, qryRecordSource.CurrentGrade
HAVING (((qryRecordSource.FirstName) Like "*" &
Forms!StudentReport!txtFilterMainName & "*") Or
((qryRecordSource.LastName)
Like "*" & Forms!StudentReport!txtFilterMainName & "*"));

Allen Browne said:
Acces pops up the parameter window if it cannot resolve the name.

For example, you may have misspelled the name, or the form may not be
open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I have created a form that provides the parameter to the query that
functions very well, however this is what happens when I hit the
submit
button on my form. After entering the search criteria in the form,
and
hitting enter, the parameter value window opens and the words that
are
typed
in the form are displayed above the entry box in the parameter value
pop
up
window. If I enter the search criteria again in the parameter value
box
the
query runs perfect.

How can I prevent the parameter value window from opening and having
to
enter the search criteria twice?

:

If you just want to search on one field (but be able to choose which
one),
you can implement this with copy'n'paste (i.e. without having to
write
any
new code):
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

If you want to give the user the option to enter multiple criteria,
this
one
demonstrates how to write the filtering code for the specific fields
you
need:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Once you have the form filtered correctly, you can then use the
form's
Filter in the WhereCondition of OpenReport. The "Trouble-shooting
and
Extending" section in the last link above discusses that.

Is it possible to do the following in MS Access and if it is
please
outline
the conceptual idea of what needs to be done?

I would like the user to open a form [SearchForm] and enter a ID
number
(primary key), or a last name, or a first name in a textbox named
[SearchCriteria] located on that form. After hitting a submit
button
[SearchButton], all records that contain i.e. the same first name
would
be
displayed in a subform or if the use choose to enter a last name,
then
all
records that contain the identified last name would be displayed.
The
user
would some how select the desired record from the list and a
report on
the
selected record would be generated.

This seems to be a logical approach to a simple problem, however I
am
only
finding bits and pieces for a solution in the discussion groups
and I
can't
seem to put it all together.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top