How to reference a field in a subdatasheet in ControlSource?

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

Guest

Access 2003

How does one properly reference, in the Properties>ControlSource of a form,
the contents of a field in a subdatasheet?

Using the expression builder I concocted the following:
=[Summary of Aggregated Deficiencies]![Matrix Reference]

Matrix Reference is a valid field in the table: frmViewChart2


Summary of Aggregated Deficiencies
which is a subdatasheet to tblChart2

But when ControlSource =[Summary of Aggregated Deficiencies]![Matrix
Reference] it yields a #Name?

TIA Dennis
 
Assuming that the name [Summary of Aggregated Deficiencies] is the name of
the sub form control in the main form, and [Matrix Reference] is the name of
the field in the subform you want to refer to, then try this

=[Summary of Aggregated Deficiencies].form![Matrix Reference]
 
Ofer, as is usual I did not make myself clear. I appreciate your time and
knowledge very much.

"Summary of Aggregated Deficiencies" is a table that contains the field
"Matrix Reference".

tblChart2 is also a table to which Summary of Aggregated Deficiencies is a
subdatasheet.

FrmViewChart2 is a form with the Control Source being tblChart2.

I would like to add to FrmViewChart2, the field "Matrix Reference" from the
"Summary of Aggregated Deficiencies" table (also subdatasheet to tblChart2).

I do not know how to code the Control Source for the field "Matrix
Reference" I wish to add to FrmViewChart2.

If the Control Source in FrmViewChart2 is "=[Summary of Aggregated
Deficiencies]![Matrix Reference]" I get an error #Name?

Again thanks, Ofer, or to anyone who may have an answer

Dennis



Ofer said:
Assuming that the name [Summary of Aggregated Deficiencies] is the name of
the sub form control in the main form, and [Matrix Reference] is the name of
the field in the subform you want to refer to, then try this

=[Summary of Aggregated Deficiencies].form![Matrix Reference]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Dennis said:
Access 2003

How does one properly reference, in the Properties>ControlSource of a form,
the contents of a field in a subdatasheet?

Using the expression builder I concocted the following:
=[Summary of Aggregated Deficiencies]![Matrix Reference]

Matrix Reference is a valid field in the table: frmViewChart2


Summary of Aggregated Deficiencies
which is a subdatasheet to tblChart2

But when ControlSource =[Summary of Aggregated Deficiencies]![Matrix
Reference] it yields a #Name?

TIA Dennis
Click to expand...
Click to expand...
 
If you are looking for a value in a table, then use DLookUp

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]")

That will work if you have one record in the table, if you have few records
you need to add the WhereStatment

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Add
the Where statement here]")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Dennis said:
Ofer, as is usual I did not make myself clear. I appreciate your time and
knowledge very much.

"Summary of Aggregated Deficiencies" is a table that contains the field
"Matrix Reference".

tblChart2 is also a table to which Summary of Aggregated Deficiencies is a
subdatasheet.

FrmViewChart2 is a form with the Control Source being tblChart2.

I would like to add to FrmViewChart2, the field "Matrix Reference" from the
"Summary of Aggregated Deficiencies" table (also subdatasheet to tblChart2).

I do not know how to code the Control Source for the field "Matrix
Reference" I wish to add to FrmViewChart2.

If the Control Source in FrmViewChart2 is "=[Summary of Aggregated
Deficiencies]![Matrix Reference]" I get an error #Name?

Again thanks, Ofer, or to anyone who may have an answer

Dennis



Ofer said:
Assuming that the name [Summary of Aggregated Deficiencies] is the name of
the sub form control in the main form, and [Matrix Reference] is the name of
the field in the subform you want to refer to, then try this

=[Summary of Aggregated Deficiencies].form![Matrix Reference]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Dennis said:
Access 2003

How does one properly reference, in the Properties>ControlSource of a form,
the contents of a field in a subdatasheet?

Using the expression builder I concocted the following:
=[Summary of Aggregated Deficiencies]![Matrix Reference]

Matrix Reference is a valid field in the table: frmViewChart2


Summary of Aggregated Deficiencies
which is a subdatasheet to tblChart2

But when ControlSource =[Summary of Aggregated Deficiencies]![Matrix
Reference] it yields a #Name?

TIA Dennis
Click to expand...
Click to expand...
Click to expand...
 
Ofer,

We are almost there! I get the value in the first record of the
subdatasheet for every record. Therefore, I need to use the where clause as
you mentioned. The tables have a common field of DeficiencyNumber.

How do you write that clause?
My attempt is:

=DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Where
DeficiencyNumber = [Summary of Aggregated Deficiencies]![DeficiencyNumber] )"


*********************************
Ofer said:
If you are looking for a value in a table, then use DLookUp

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]")

That will work if you have one record in the table, if you have few records
you need to add the WhereStatment

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Add
the Where statement here]")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Dennis said:
Ofer, as is usual I did not make myself clear. I appreciate your time and
knowledge very much.

"Summary of Aggregated Deficiencies" is a table that contains the field
"Matrix Reference".

tblChart2 is also a table to which Summary of Aggregated Deficiencies is a
subdatasheet.

FrmViewChart2 is a form with the Control Source being tblChart2.

I would like to add to FrmViewChart2, the field "Matrix Reference" from the
"Summary of Aggregated Deficiencies" table (also subdatasheet to tblChart2).

I do not know how to code the Control Source for the field "Matrix
Reference" I wish to add to FrmViewChart2.

If the Control Source in FrmViewChart2 is "=[Summary of Aggregated
Deficiencies]![Matrix Reference]" I get an error #Name?

Again thanks, Ofer, or to anyone who may have an answer

Dennis



Ofer said:
Assuming that the name [Summary of Aggregated Deficiencies] is the name of
the sub form control in the main form, and [Matrix Reference] is the name of
the field in the subform you want to refer to, then try this

=[Summary of Aggregated Deficiencies].form![Matrix Reference]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Access 2003

How does one properly reference, in the Properties>ControlSource of a form,
the contents of a field in a subdatasheet?

Using the expression builder I concocted the following:
=[Summary of Aggregated Deficiencies]![Matrix Reference]

Matrix Reference is a valid field in the table: frmViewChart2


Summary of Aggregated Deficiencies
which is a subdatasheet to tblChart2

But when ControlSource =[Summary of Aggregated Deficiencies]![Matrix
Reference] it yields a #Name?

TIA Dennis
Click to expand...
Click to expand...
Click to expand...
 
Try this

=DLookUp("[Matrix Reference]","[Summary of Aggregated
Deficiencies]","[DeficiencyNumber] = " & [DeficiencyNumber])

The question is where do you get the [DeficiencyNumber] value from?

Dennis said:
Ofer,

We are almost there! I get the value in the first record of the
subdatasheet for every record. Therefore, I need to use the where clause as
you mentioned. The tables have a common field of DeficiencyNumber.

How do you write that clause?
My attempt is:

=DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Where
DeficiencyNumber = [Summary of Aggregated Deficiencies]![DeficiencyNumber] )"


*********************************
Ofer said:
If you are looking for a value in a table, then use DLookUp

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]")

That will work if you have one record in the table, if you have few records
you need to add the WhereStatment

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Add
the Where statement here]")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Dennis said:
Ofer, as is usual I did not make myself clear. I appreciate your time and
knowledge very much.

"Summary of Aggregated Deficiencies" is a table that contains the field
"Matrix Reference".

tblChart2 is also a table to which Summary of Aggregated Deficiencies is a
subdatasheet.

FrmViewChart2 is a form with the Control Source being tblChart2.

I would like to add to FrmViewChart2, the field "Matrix Reference" from the
"Summary of Aggregated Deficiencies" table (also subdatasheet to tblChart2).

I do not know how to code the Control Source for the field "Matrix
Reference" I wish to add to FrmViewChart2.

If the Control Source in FrmViewChart2 is "=[Summary of Aggregated
Deficiencies]![Matrix Reference]" I get an error #Name?

Again thanks, Ofer, or to anyone who may have an answer

Dennis



:

Assuming that the name [Summary of Aggregated Deficiencies] is the name of
the sub form control in the main form, and [Matrix Reference] is the name of
the field in the subform you want to refer to, then try this

=[Summary of Aggregated Deficiencies].form![Matrix Reference]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Access 2003

How does one properly reference, in the Properties>ControlSource of a form,
the contents of a field in a subdatasheet?

Using the expression builder I concocted the following:
=[Summary of Aggregated Deficiencies]![Matrix Reference]

Matrix Reference is a valid field in the table: frmViewChart2


Summary of Aggregated Deficiencies
which is a subdatasheet to tblChart2

But when ControlSource =[Summary of Aggregated Deficiencies]![Matrix
Reference] it yields a #Name?

TIA Dennis
Click to expand...
Click to expand...
Click to expand...
 
Ofer,

The Deficiency Number is present in the table "Summary of Aggregated
Deficiencies" AND in the table tblChart2.

So the goal is to have the form frmViewChart2 (which the Control Source is
tblChart2) display the "Matrix Reference" field from the table "Summary of
Aggregated Deficiencies" ... Where the Deficiency Number in tblChart2 =
Deficiency Number in the "Summary of Aggregated Deficiencies."

I believe that = " & [Deficiency Number]) assumes that the Deficiency Number
is a variable.


The Parent Link and Child Link is "Deficiency Number"

****************************


Ofer said:
Try this

=DLookUp("[Matrix Reference]","[Summary of Aggregated
Deficiencies]","[DeficiencyNumber] = " & [DeficiencyNumber])

The question is where do you get the [DeficiencyNumber] value from?

Dennis said:
Ofer,

We are almost there! I get the value in the first record of the
subdatasheet for every record. Therefore, I need to use the where clause as
you mentioned. The tables have a common field of DeficiencyNumber.

How do you write that clause?
My attempt is:

=DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Where
DeficiencyNumber = [Summary of Aggregated Deficiencies]![DeficiencyNumber] )"


*********************************
Ofer said:
If you are looking for a value in a table, then use DLookUp

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]")

That will work if you have one record in the table, if you have few records
you need to add the WhereStatment

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Add
the Where statement here]")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Ofer, as is usual I did not make myself clear. I appreciate your time and
knowledge very much.

"Summary of Aggregated Deficiencies" is a table that contains the field
"Matrix Reference".

tblChart2 is also a table to which Summary of Aggregated Deficiencies is a
subdatasheet.

FrmViewChart2 is a form with the Control Source being tblChart2.

I would like to add to FrmViewChart2, the field "Matrix Reference" from the
"Summary of Aggregated Deficiencies" table (also subdatasheet to tblChart2).

I do not know how to code the Control Source for the field "Matrix
Reference" I wish to add to FrmViewChart2.

If the Control Source in FrmViewChart2 is "=[Summary of Aggregated
Deficiencies]![Matrix Reference]" I get an error #Name?

Again thanks, Ofer, or to anyone who may have an answer

Dennis



:

Assuming that the name [Summary of Aggregated Deficiencies] is the name of
the sub form control in the main form, and [Matrix Reference] is the name of
the field in the subform you want to refer to, then try this

=[Summary of Aggregated Deficiencies].form![Matrix Reference]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Access 2003

How does one properly reference, in the Properties>ControlSource of a form,
the contents of a field in a subdatasheet?

Using the expression builder I concocted the following:
=[Summary of Aggregated Deficiencies]![Matrix Reference]

Matrix Reference is a valid field in the table: frmViewChart2


Summary of Aggregated Deficiencies
which is a subdatasheet to tblChart2

But when ControlSource =[Summary of Aggregated Deficiencies]![Matrix
Reference] it yields a #Name?

TIA Dennis
Click to expand...
Click to expand...
Click to expand...
 
The = " & [Deficiency Number] assume that you have a field in the form record
source name [Deficiency Number], and this is the search criteria



Dennis said:
Ofer,

The Deficiency Number is present in the table "Summary of Aggregated
Deficiencies" AND in the table tblChart2.

So the goal is to have the form frmViewChart2 (which the Control Source is
tblChart2) display the "Matrix Reference" field from the table "Summary of
Aggregated Deficiencies" ... Where the Deficiency Number in tblChart2 =
Deficiency Number in the "Summary of Aggregated Deficiencies."

I believe that = " & [Deficiency Number]) assumes that the Deficiency Number
is a variable.


The Parent Link and Child Link is "Deficiency Number"

****************************


Ofer said:
Try this

=DLookUp("[Matrix Reference]","[Summary of Aggregated
Deficiencies]","[DeficiencyNumber] = " & [DeficiencyNumber])

The question is where do you get the [DeficiencyNumber] value from?

Dennis said:
Ofer,

We are almost there! I get the value in the first record of the
subdatasheet for every record. Therefore, I need to use the where clause as
you mentioned. The tables have a common field of DeficiencyNumber.

How do you write that clause?
My attempt is:

=DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Where
DeficiencyNumber = [Summary of Aggregated Deficiencies]![DeficiencyNumber] )"


*********************************
:

If you are looking for a value in a table, then use DLookUp

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]")

That will work if you have one record in the table, if you have few records
you need to add the WhereStatment

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Add
the Where statement here]")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Ofer, as is usual I did not make myself clear. I appreciate your time and
knowledge very much.

"Summary of Aggregated Deficiencies" is a table that contains the field
"Matrix Reference".

tblChart2 is also a table to which Summary of Aggregated Deficiencies is a
subdatasheet.

FrmViewChart2 is a form with the Control Source being tblChart2.

I would like to add to FrmViewChart2, the field "Matrix Reference" from the
"Summary of Aggregated Deficiencies" table (also subdatasheet to tblChart2).

I do not know how to code the Control Source for the field "Matrix
Reference" I wish to add to FrmViewChart2.

If the Control Source in FrmViewChart2 is "=[Summary of Aggregated
Deficiencies]![Matrix Reference]" I get an error #Name?

Again thanks, Ofer, or to anyone who may have an answer

Dennis



:

Assuming that the name [Summary of Aggregated Deficiencies] is the name of
the sub form control in the main form, and [Matrix Reference] is the name of
the field in the subform you want to refer to, then try this

=[Summary of Aggregated Deficiencies].form![Matrix Reference]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Access 2003

How does one properly reference, in the Properties>ControlSource of a form,
the contents of a field in a subdatasheet?

Using the expression builder I concocted the following:
=[Summary of Aggregated Deficiencies]![Matrix Reference]

Matrix Reference is a valid field in the table: frmViewChart2


Summary of Aggregated Deficiencies
which is a subdatasheet to tblChart2

But when ControlSource =[Summary of Aggregated Deficiencies]![Matrix
Reference] it yields a #Name?

TIA Dennis
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help Ofer,

The following is what worked in this situation:

=DLookUp("[Matrix Reference]","[Summary of Aggregated
Deficiencies]","[Summary of Aggregated Deficiencies]![Deficiency Number] =
Forms![frmViewChart2]![Deficiency Number]")

Ofer said:
The = " & [Deficiency Number] assume that you have a field in the form record
source name [Deficiency Number], and this is the search criteria



Dennis said:
Ofer,

The Deficiency Number is present in the table "Summary of Aggregated
Deficiencies" AND in the table tblChart2.

So the goal is to have the form frmViewChart2 (which the Control Source is
tblChart2) display the "Matrix Reference" field from the table "Summary of
Aggregated Deficiencies" ... Where the Deficiency Number in tblChart2 =
Deficiency Number in the "Summary of Aggregated Deficiencies."

I believe that = " & [Deficiency Number]) assumes that the Deficiency Number
is a variable.


The Parent Link and Child Link is "Deficiency Number"

****************************


Ofer said:
Try this

=DLookUp("[Matrix Reference]","[Summary of Aggregated
Deficiencies]","[DeficiencyNumber] = " & [DeficiencyNumber])

The question is where do you get the [DeficiencyNumber] value from?

:

Ofer,

We are almost there! I get the value in the first record of the
subdatasheet for every record. Therefore, I need to use the where clause as
you mentioned. The tables have a common field of DeficiencyNumber.

How do you write that clause?
My attempt is:

=DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Where
DeficiencyNumber = [Summary of Aggregated Deficiencies]![DeficiencyNumber] )"


*********************************
:

If you are looking for a value in a table, then use DLookUp

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]")

That will work if you have one record in the table, if you have few records
you need to add the WhereStatment

= DLookUp("[Matrix Reference]","[Summary of Aggregated Deficiencies]","[Add
the Where statement here]")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Ofer, as is usual I did not make myself clear. I appreciate your time and
knowledge very much.

"Summary of Aggregated Deficiencies" is a table that contains the field
"Matrix Reference".

tblChart2 is also a table to which Summary of Aggregated Deficiencies is a
subdatasheet.

FrmViewChart2 is a form with the Control Source being tblChart2.

I would like to add to FrmViewChart2, the field "Matrix Reference" from the
"Summary of Aggregated Deficiencies" table (also subdatasheet to tblChart2).

I do not know how to code the Control Source for the field "Matrix
Reference" I wish to add to FrmViewChart2.

If the Control Source in FrmViewChart2 is "=[Summary of Aggregated
Deficiencies]![Matrix Reference]" I get an error #Name?

Again thanks, Ofer, or to anyone who may have an answer

Dennis



:

Assuming that the name [Summary of Aggregated Deficiencies] is the name of
the sub form control in the main form, and [Matrix Reference] is the name of
the field in the subform you want to refer to, then try this

=[Summary of Aggregated Deficiencies].form![Matrix Reference]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Access 2003

How does one properly reference, in the Properties>ControlSource of a form,
the contents of a field in a subdatasheet?

Using the expression builder I concocted the following:
=[Summary of Aggregated Deficiencies]![Matrix Reference]

Matrix Reference is a valid field in the table: frmViewChart2


Summary of Aggregated Deficiencies
which is a subdatasheet to tblChart2

But when ControlSource =[Summary of Aggregated Deficiencies]![Matrix
Reference] it yields a #Name?

TIA Dennis
Click to expand...
Click to expand...
Click to expand...
 
Back
Top