Search by Combobox Criteria

[Steve Moss]

This is the whole thread so far, for somereason the last reply hasnt appeared
on the main page. scroll to the bottom for most recent. can anyone help, i
am getting a little desperate now, as this is holding me back, many thanks

Steve Moss 10/9/2007 10:15 AM PST

Question

I have a database which stores books and there owners. I would like to have
a combo box from which i select a persons name. This would then filter the
books belonging to this person and show a report of them. I have had
something similar working in the past, but this was only achieved if i made a
querie and report for each individual. in this case more people will be
added to the database as time goes on, and i dont want to do an individual
querie and report for each person. how can i do this


Was this post helpful to you?
Reply Top





Ken Snell (MVP) 10/9/2007 10:32 AM PST



You can use the value of the combo box to build a string that you then use
in the fourth argument (WHERE) of the DoCmd.OpenReport action -- that will
filter the report for the selection made in the combo box.

--

Ken Snell
<MS ACCESS MVP>



Click to show or hide original message or reply text.

I have a database which stores books and there owners. I would like to
have
a combo box from which i select a persons name. This would then filter
the
books belonging to this person and show a report of them. I have had
something similar working in the past, but this was only achieved if i
made a
querie and report for each individual. in this case more people will be
added to the database as time goes on, and i dont want to do an individual
querie and report for each person. how can i do this

Did this post answer the question?
Reply Top





Steve Moss 10/9/2007 10:59 AM PST



Thanks Ken

this is the code i had for the previous comobox:-

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenForm Me![Combo6], acViewPreview
Else
MsgBox "You need to select a report"
End If
End Sub

Combo6 being the drop down containing the following:-
"BOSUN";"CO";"COXN";"DMEO";"DWEO";"GUNZ";"LD";"MAIN
HOLDINGS";"MEO";"NAVS";"OPS";"ORS";"WEO";"XO".

This selection would now become a list of names held in another table called
"personnel & Department". what would the code be now.


:

Click to show or hide original message or reply text.

You can use the value of the combo box to build a string that you then use
in the fourth argument (WHERE) of the DoCmd.OpenReport action -- that will
filter the report for the selection made in the combo box.

Was this post helpful to you?
Reply Top





Ken Snell (MVP) 10/9/2007 11:20 AM PST



Build a single report (not a form) that will display the desired data. Use
a
query as its RecordSource that will provide you with the desired
(unfiltered) data. Be sure to include the field that holds the person's name
in the query.

Then your code would change to this (generic -- replace generic names with
real field and report names):

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenReport "ReportName", acViewPreview, , _
"[NameOfPersonField] ='" & Me![Combo6].Value & "'"
Else
MsgBox "You need to select a report"
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>




Click to show or hide original message or reply text.

Thanks Ken

this is the code i had for the previous comobox:-

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenForm Me![Combo6], acViewPreview
Else
MsgBox "You need to select a report"
End If
End Sub

Combo6 being the drop down containing the following:-
"BOSUN";"CO";"COXN";"DMEO";"DWEO";"GUNZ";"LD";"MAIN
HOLDINGS";"MEO";"NAVS";"OPS";"ORS";"WEO";"XO".

This selection would now become a list of names held in another table
called
"personnel & Department". what would the code be now.

You can use the value of the combo box to build a string that you then
use
in the fourth argument (WHERE) of the DoCmd.OpenReport action -- that
will
filter the report for the selection made in the combo box.

Did this post answer the question?
Reply Top





Steve Moss 10/10/2007 10:00 AM PST



Thanks Ken
I couldnt get it to work for some reason. When i selected the name from the
Combobox it opened up the report, but there was no data on it. i manged to
get round the problem by attaching the code to a command button, so i select
the name and then press the button. It works a treat, many many thanks, you
have saved me a world of head aches

:

Click to show or hide original message or reply text.

Build a single report (not a form) that will display the desired data. Use a
query as its RecordSource that will provide you with the desired
(unfiltered) data. Be sure to include the field that holds the person's name
in the query.

Then your code would change to this (generic -- replace generic names with
real field and report names):

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenReport "ReportName", acViewPreview, , _
"[NameOfPersonField] ='" & Me![Combo6].Value & "'"
Else
MsgBox "You need to select a report"
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken

this is the code i had for the previous comobox:-

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenForm Me![Combo6], acViewPreview
Else
MsgBox "You need to select a report"
End If
End Sub

Combo6 being the drop down containing the following:-
"BOSUN";"CO";"COXN";"DMEO";"DWEO";"GUNZ";"LD";"MAIN
HOLDINGS";"MEO";"NAVS";"OPS";"ORS";"WEO";"XO".

This selection would now become a list of names held in another table
called
"personnel & Department". what would the code be now.

You can use the value of the combo box to build a string that you then
use
in the fourth argument (WHERE) of the DoCmd.OpenReport action -- that
will
filter the report for the selection made in the combo box.

--

Ken Snell
<MS ACCESS MVP>


I have a database which stores books and there owners. I would like to
have
a combo box from which i select a persons name. This would then filter
the
books belonging to this person and show a report of them. I have had
something similar working in the past, but this was only achieved if i
made a
querie and report for each individual. in this case more people will
be
added to the database as time goes on, and i dont want to do an
individual
querie and report for each person. how can i do this

Was this post helpful to you?
Reply Top





Ken Snell (MVP) 10/10/2007 10:54 AM PST



My error - sorry. Instead of using the Click event of the combo box, use
the
AfterUpdate event of the combo box to run the code.

--

Ken Snell
<MS ACCESS MVP>



Click to show or hide original message or reply text.

Thanks Ken
I couldnt get it to work for some reason. When i selected the name from
the
Combobox it opened up the report, but there was no data on it. i manged
to
get round the problem by attaching the code to a command button, so i
select
the name and then press the button. It works a treat, many many thanks,
you
have saved me a world of head aches

Build a single report (not a form) that will display the desired data.
Use a
query as its RecordSource that will provide you with the desired
(unfiltered) data. Be sure to include the field that holds the person's
name
in the query.

Then your code would change to this (generic -- replace generic names
with
real field and report names):

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenReport "ReportName", acViewPreview, , _
"[NameOfPersonField] ='" & Me![Combo6].Value & "'"
Else
MsgBox "You need to select a report"
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken

this is the code i had for the previous comobox:-

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenForm Me![Combo6], acViewPreview
Else
MsgBox "You need to select a report"
End If
End Sub

Combo6 being the drop down containing the following:-
"BOSUN";"CO";"COXN";"DMEO";"DWEO";"GUNZ";"LD";"MAIN
HOLDINGS";"MEO";"NAVS";"OPS";"ORS";"WEO";"XO".

This selection would now become a list of names held in another table
called
"personnel & Department". what would the code be now.


:

You can use the value of the combo box to build a string that you then
use
in the fourth argument (WHERE) of the DoCmd.OpenReport action -- that
will
filter the report for the selection made in the combo box.

--

Ken Snell
<MS ACCESS MVP>


I have a database which stores books and there owners. I would like
to
have
a combo box from which i select a persons name. This would then
filter
the
books belonging to this person and show a report of them. I have
had
something similar working in the past, but this was only achieved if
i
made a
querie and report for each individual. in this case more people
will
be
added to the database as time goes on, and i dont want to do an
individual
querie and report for each person. how can i do this

Did this post answer the question?
Reply Top





Steve Moss 11/1/2007 5:10 AM PST



The code doesnt seem to be working. the first part works fine

Private Sub Combo6_Click()

If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenReport "ReportName", acViewPreview, , _

but it seems that the filter isnt right.

"[NameOfPersonField] ='" & Me![Combo6].Value & "'"
[NameOfPersonField] is this the field on the report which contains the
information (names) to be filtered.
[Combo6] and is this the field that contains the information by which the
[NameOfPersonField] is going to be filtered.
what does the last part of the code mean i.e. ='" & Me![Combo6].Value & "'"
I think if i can understand the last bit i might be able to fudge somehting
together myself. The report opens but without any information on it.


:

Click to show or hide original message or reply text.

My error - sorry. Instead of using the Click event of the combo box, use the
AfterUpdate event of the combo box to run the code.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken
I couldnt get it to work for some reason. When i selected the name from
the
Combobox it opened up the report, but there was no data on it. i manged
to
get round the problem by attaching the code to a command button, so i
select
the name and then press the button. It works a treat, many many thanks,
you
have saved me a world of head aches

Build a single report (not a form) that will display the desired data.
Use a
query as its RecordSource that will provide you with the desired
(unfiltered) data. Be sure to include the field that holds the person's
name
in the query.

Then your code would change to this (generic -- replace generic names
with
real field and report names):

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenReport "ReportName", acViewPreview, , _
"[NameOfPersonField] ='" & Me![Combo6].Value & "'"
Else
MsgBox "You need to select a report"
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>



Thanks Ken

this is the code i had for the previous comobox:-

Private Sub Combo6_Click()
If Len(Trim(Me![Combo6]) & "") > 0 Then
DoCmd.OpenForm Me![Combo6], acViewPreview
Else
MsgBox "You need to select a report"
End If
End Sub

Combo6 being the drop down containing the following:-
"BOSUN";"CO";"COXN";"DMEO";"DWEO";"GUNZ";"LD";"MAIN
HOLDINGS";"MEO";"NAVS";"OPS";"ORS";"WEO";"XO".

This selection would now become a list of names held in another table
called
"personnel & Department". what would the code be now.


:

You can use the value of the combo box to build a string that you then
use
in the fourth argument (WHERE) of the DoCmd.OpenReport action -- that
will
filter the report for the selection made in the combo box.

--

Ken Snell
<MS ACCESS MVP>


I have a database which stores books and there owners. I would like
to
have
a combo box from which i select a persons name. This would then
filter
the
books belonging to this person and show a report of them. I have
had
something similar working in the past, but this was only achieved if
i
made a
querie and report for each individual. in this case more people
will
be
added to the database as time goes on, and i dont want to do an
individual
querie and report for each person. how can i do this