Search by Combobox Criteria

[Steve Moss]

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

 

[Ken Snell \(MVP\)]

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.

 

[Steve Moss]

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.

 

[Ken Snell \(MVP\)]

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.

 

[Steve Moss]

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

 

[Ken Snell \(MVP\)]

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

 

[Steve Moss]

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.

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

 

[Ken Snell \(MVP\)]

what does the last part of the code mean i.e. ='" & Me![Combo6].Value &

"'"

This is used to concatenate the actual value of the Combo6 control into the
"WHERE" filter string for the DoCmd.OpenReport. The code assumes that the
value in Combo6 control is a text data type.

If it's a numeric data type, then use this:
=" & Me![Combo6].Value


--

Ken Snell
<MS ACCESS MVP>

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.

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