generating two control sources dependent on a single reference

[Guest]

I have created a form that has a drop down list that shows a numerical code
and also a descriptor for each code. The drop down list is using a reference
table, table 2, with two fields to generate the list. Field one is the
numerical code and Field 2 is the descriptor. I have set the form up to store
only the numerical code (Field 1) in Table 1.

In my report, I am having trouble creating a text box that provides both the
numerical code selected (Field one) and its corresponding descriptor. I can
create a text box that puts in the control source for the numerical code from
table 1 but not its corresponding descriptor (Field 2 from table 2). I have
tried the following:

=[GOVCODE] And [Table2]!Field2 (note [GOVCODE] is from Table 1

When running the report I am prompted to provide a value for Table2, please
help.

 

[Larry Linson]

You need to have Table 1 and Table 2, joined on the Numerical Code, in the
Query that you use as the RecordSource for the Report. When you do, you can
drag down the Descriptor field into the Query Grid and it will be available
for use in the Report.

Larry Linson
Microsoft Access MVP

 

[Guest]

Is there anyway to avoid using a Query, I am currently using Tabel 1 as my
RecordSource.

You need to have Table 1 and Table 2, joined on the Numerical Code, in the
Query that you use as the RecordSource for the Report. When you do, you can
drag down the Descriptor field into the Query Grid and it will be available
for use in the Report.

Larry Linson
Microsoft Access MVP

I have created a form that has a drop down list that shows a numerical code
and also a descriptor for each code. The drop down list is using a
reference
table, table 2, with two fields to generate the list. Field one is the
numerical code and Field 2 is the descriptor. I have set the form up to
store
only the numerical code (Field 1) in Table 1.

In my report, I am having trouble creating a text box that provides both
the
numerical code selected (Field one) and its corresponding descriptor. I
can
create a text box that puts in the control source for the numerical code
from
table 1 but not its corresponding descriptor (Field 2 from table 2). I
have
tried the following:

=[GOVCODE] And [Table2]!Field2 (note [GOVCODE] is from Table 1

When running the report I am prompted to provide a value for Table2,
please
help.

 

[Larry Linson]

Why would you want to "avoid using a Query"? The Query will include the
Table you are using, plus the Lookup Table for Descriptors, and the Field
Names will be identical, so you can simply use it to replace the Tablename
in the Report's RecordSource.

Create the Query in the QueryBuilder, add both Tables as data sources, and
on the Table 1, double-click then drag as an easy way to get all the Fields
in the Query Builder Grid. Click on Numeric Code in Table 1 and drag to
Numeric Code in the lookup table. Right click the join line, and choose "All
records from Table 1 and only those that match from..." as the Join Type.
Now drag down the Descriptor to the Query Grid... save the Query.

Replace the current reference to Table 1 with the name of the saved Query,
by opening the Report in design view. In the Report's Property Sheet, click
the data tab, then click in RecordSource -- the dropdown will list and allow
you to choose the name of the Query.

Then, add the new Text Box for Descriptor, and choose Descriptor from the
dropdown list in its Control Source.

It is actually even less work than it seems from reading this.

Good luck.

Larry Linson
Microsoft Access MVP

Is there anyway to avoid using a Query, I am currently using Tabel 1 as my
RecordSource.

You need to have Table 1 and Table 2, joined on the Numerical Code, in
the
Query that you use as the RecordSource for the Report. When you do, you
can
drag down the Descriptor field into the Query Grid and it will be
available
for use in the Report.

Larry Linson
Microsoft Access MVP

I have created a form that has a drop down list that shows a numerical
code
and also a descriptor for each code. The drop down list is using a
reference
table, table 2, with two fields to generate the list. Field one is the
numerical code and Field 2 is the descriptor. I have set the form up to
store
only the numerical code (Field 1) in Table 1.

In my report, I am having trouble creating a text box that provides
both
the
numerical code selected (Field one) and its corresponding descriptor. I
can
create a text box that puts in the control source for the numerical
code
from
table 1 but not its corresponding descriptor (Field 2 from table 2). I
have
tried the following:

=[GOVCODE] And [Table2]!Field2 (note [GOVCODE] is from Table 1

When running the report I am prompted to provide a value for Table2,
please
help.