Display Parameter choice On Report

[Brian]

I have a form containing a combo box based on a Suppliers Table. When a
supplier is selected from the list and the "GO" cmd button a macro opens a
report is opened filtered to that particular supplier.

On the report I have an unbound field with the expression in the report header
=[Forms]![Search Supplier]![Supplier]. The report will only show the
suppliers ID number and not the actual name even though only the name is
diplayed on the combo form.

I have tried several combinations of column counts etc but cannot get the
report to display as I wish.

Help Needed Please

 

[Ken Snell \(MVP\)]

Use a DLookup expression (replace my generic names with your real names):

=DLookup("NameOfSupplierNameField", "SuppliersTable", "[Supplier]=" &
[Forms]![Search Supplier]![Supplier])

 

[Brian]

Ken
I appreciate the fast response. I don't pretend to understand the expression
so I copied it as written with the name chages as directed - Iassmed the " "
were to be included.
The report still returns the ID code and not the name. Can I just clarify
the first stage of your formula ie NameofSupplierNameField - I am assuming
this is what I have called "Supplier" from the Suppliers Table which is also
the field name [Supplier] which is the last part of the first portion of your
expression.
I should add, in case it is relevant, that I created the combo box form by
first creating a new table called tbSupplierSearch with just one field
[Supplier] with a look-up connection to the main tbSuppliers. Should I have
used new unbound form and created a combox manually?

Appreciate your assistance
Brian

Use a DLookup expression (replace my generic names with your real names):

=DLookup("NameOfSupplierNameField", "SuppliersTable", "[Supplier]=" &
[Forms]![Search Supplier]![Supplier])


--

Ken Snell
<MS ACCESS MVP>

I have a form containing a combo box based on a Suppliers Table. When a
supplier is selected from the list and the "GO" cmd button a macro opens a
report is opened filtered to that particular supplier.

On the report I have an unbound field with the expression in the report
header
=[Forms]![Search Supplier]![Supplier]. The report will only show the
suppliers ID number and not the actual name even though only the name is
diplayed on the combo form.

I have tried several combinations of column counts etc but cannot get the
report to display as I wish.

Help Needed Please

 

[Ken Snell \(MVP\)]

Generically, here is what the three different arguments represent for the
DLookup function:

DLookup("Name of the field being looked up", "Name of table/query containing
the field being looked up", "WHERE criterion statement to filter the
lookup")

What I was assuming is that your SuppliersTable (that may not be the real
name) contains a field "Supplier" that holds a unique value for each
supplier, and contains a field that holds the name of the supplier
corresponding to the Supplier value that is in that record. I also assumed
that your combobox is using the Supplier unique id value as the
"BoundColumn" value for the combo box, and thus the value of the combobox
would be used to find the corresponding name of the supplier.

Based on your comments that the DLookup is still not showing the desired
supplier name, please tell us the actual name of the table that contains the
supplier ID and supplier name values, the actual names of the fields holding
those values, the SQL statement for the query that is the Row Source for the
combo box on the form, and the value in the BoundColumn property of that
combobox.
--

Ken Snell
<MS ACCESS MVP>

Ken
I appreciate the fast response. I don't pretend to understand the
expression
so I copied it as written with the name chages as directed - Iassmed the "
"
were to be included.
The report still returns the ID code and not the name. Can I just clarify
the first stage of your formula ie NameofSupplierNameField - I am assuming
this is what I have called "Supplier" from the Suppliers Table which is
also
the field name [Supplier] which is the last part of the first portion of
your
expression.
I should add, in case it is relevant, that I created the combo box form by
first creating a new table called tbSupplierSearch with just one field
[Supplier] with a look-up connection to the main tbSuppliers. Should I
have
used new unbound form and created a combox manually?

Appreciate your assistance
Brian

Use a DLookup expression (replace my generic names with your real names):

=DLookup("NameOfSupplierNameField", "SuppliersTable", "[Supplier]=" &
[Forms]![Search Supplier]![Supplier])


--

Ken Snell
<MS ACCESS MVP>

I have a form containing a combo box based on a Suppliers Table. When a
supplier is selected from the list and the "GO" cmd button a macro
opens a
report is opened filtered to that particular supplier.

On the report I have an unbound field with the expression in the report
header
=[Forms]![Search Supplier]![Supplier]. The report will only show the
suppliers ID number and not the actual name even though only the name
is
diplayed on the combo form.

I have tried several combinations of column counts etc but cannot get
the
report to display as I wish.

Help Needed Please

 

[David W. Fenton]

On the report I have an unbound field with the expression in the
report header
=[Forms]![Search Supplier]![Supplier]. The report will only show
the
suppliers ID number and not the actual name even though only the
name is diplayed on the combo form.

Assuming the combo box displays the supplier name, the supplier ID
is in the first column (and hidden) and the supplier name is the
next column, change your expression to:

=[Forms]![Search Supplier]![Supplier].[Column](1)

(that may not be exactly right -- if you paste Forms![Search
Supplier]!Supplier.Column(1) into the control source of the control
on the report, Access will put in the appropriate brackets when you
hit ENTER; I'd also suggest you get in the habit of never using
spaces in the names of any of your Access objects, as it saves the
need for brackets)

 

[Brian]

I am pleased to report a successfull result. I read your comments and having
taken the trouble to actually disseminate what you were saying, I changed the
expression with a positive outcome. Something else I've now learned.

Thanks for your guidance, I would otherwise never have gone anywhere near
Dlookup functions

Generically, here is what the three different arguments represent for the
DLookup function:

DLookup("Name of the field being looked up", "Name of table/query containing
the field being looked up", "WHERE criterion statement to filter the
lookup")

What I was assuming is that your SuppliersTable (that may not be the real
name) contains a field "Supplier" that holds a unique value for each
supplier, and contains a field that holds the name of the supplier
corresponding to the Supplier value that is in that record. I also assumed
that your combobox is using the Supplier unique id value as the
"BoundColumn" value for the combo box, and thus the value of the combobox
would be used to find the corresponding name of the supplier.

Based on your comments that the DLookup is still not showing the desired
supplier name, please tell us the actual name of the table that contains the
supplier ID and supplier name values, the actual names of the fields holding
those values, the SQL statement for the query that is the Row Source for the
combo box on the form, and the value in the BoundColumn property of that
combobox.
--

Ken Snell
<MS ACCESS MVP>

Ken
I appreciate the fast response. I don't pretend to understand the
expression
so I copied it as written with the name chages as directed - Iassmed the "
"
were to be included.
The report still returns the ID code and not the name. Can I just clarify
the first stage of your formula ie NameofSupplierNameField - I am assuming
this is what I have called "Supplier" from the Suppliers Table which is
also
the field name [Supplier] which is the last part of the first portion of
your
expression.
I should add, in case it is relevant, that I created the combo box form by
first creating a new table called tbSupplierSearch with just one field
[Supplier] with a look-up connection to the main tbSuppliers. Should I
have
used new unbound form and created a combox manually?

Appreciate your assistance
Brian

Use a DLookup expression (replace my generic names with your real names):

=DLookup("NameOfSupplierNameField", "SuppliersTable", "[Supplier]=" &
[Forms]![Search Supplier]![Supplier])


--

Ken Snell
<MS ACCESS MVP>


I have a form containing a combo box based on a Suppliers Table. When a
supplier is selected from the list and the "GO" cmd button a macro
opens a
report is opened filtered to that particular supplier.

On the report I have an unbound field with the expression in the report
header
=[Forms]![Search Supplier]![Supplier]. The report will only show the
suppliers ID number and not the actual name even though only the name
is
diplayed on the combo form.

I have tried several combinations of column counts etc but cannot get
the
report to display as I wish.

Help Needed Please

 

[Brian]

Thanks Dave, That works fine. Point taken over the naming convention. On the
down side it means re-naming all the labels but on balance it is easier when
you have a lot of queries or expressions to write.
Appreciate your help.

On the report I have an unbound field with the expression in the
report header
=[Forms]![Search Supplier]![Supplier]. The report will only show
the
suppliers ID number and not the actual name even though only the
name is diplayed on the combo form.

Assuming the combo box displays the supplier name, the supplier ID
is in the first column (and hidden) and the supplier name is the
next column, change your expression to:

=[Forms]![Search Supplier]![Supplier].[Column](1)

(that may not be exactly right -- if you paste Forms![Search
Supplier]!Supplier.Column(1) into the control source of the control
on the report, Access will put in the appropriate brackets when you
hit ENTER; I'd also suggest you get in the habit of never using
spaces in the names of any of your Access objects, as it saves the
need for brackets)

 

[David W. Fenton]

Point taken over the naming convention. On the
down side it means re-naming all the labels but on balance it is
easier when you have a lot of queries or expressions to write.

Not necessarily. In table design you can set the CAPTION property
for a field and I believe that when you drop controls on a form, it
will inherit that for the label. Thus, you can do it once (in table
design) and never have to deal with it again.