Have subform of subform automatically fill in a name

[amjjam]

Hi,

I have a "Communication" form inserted as a tab into a "Contacts" form which
is inserted as a tab into a "Company" form. So it's a subform of a subform.
The communication form collects data regarding phone calls, emails, text
messages, faxes, etc. I'd like for the Contact's name that any of that
communication is with to automatically be filled in at the top of the form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " & [Forms]![Contacts]![LastName] in the
control source property. It returns #Name? during testing. Is there an
expression which will automatically fill-in the contact's name from the
contact form in the communication form? Thanks for any help, amjjam

 

[Damon Heron]

try:
= me.Parent![nameofcontrol]

In your case, the control name is FirstName, so-

=me.Parent![FirstName] & " " & me.Parent![LastName]

Damon

 

[amjjam]

Thanks for the idea, but it didn’t work. I put the expression you suggested
into the control source property of the Communication form and it still
returns #Name? So if you’ve got any other suggestions, I’d welcome them.
Thanks again, amjjam

try:
= me.Parent![nameofcontrol]

In your case, the control name is FirstName, so-

=me.Parent![FirstName] & " " & me.Parent![LastName]

Damon

Hi,

I have a "Communication" form inserted as a tab into a "Contacts" form
which
is inserted as a tab into a "Company" form. So it's a subform of a
subform.
The communication form collects data regarding phone calls, emails, text
messages, faxes, etc. I'd like for the Contact's name that any of that
communication is with to automatically be filled in at the top of the
form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " & [Forms]![Contacts]![LastName] in
the
control source property. It returns #Name? during testing. Is there an
expression which will automatically fill-in the contact's name from the
contact form in the communication form? Thanks for any help, amjjam

 

[Damon Heron]

Put the code in the current event of the Comm subform -
Private Sub Form_Current()
Me.[nameofyourtextbox] = Me.Parent!FirstName & " " & Me.Parent!LastName
End Sub

There is a master child relationship between subform Contacts and subform
Communication, right?

Damon

Thanks for the idea, but it didn't work. I put the expression you
suggested
into the control source property of the Communication form and it still
returns #Name? So if you've got any other suggestions, I'd welcome them.
Thanks again, amjjam

try:
= me.Parent![nameofcontrol]

In your case, the control name is FirstName, so-

=me.Parent![FirstName] & " " & me.Parent![LastName]

Damon

Hi,

I have a "Communication" form inserted as a tab into a "Contacts" form
which
is inserted as a tab into a "Company" form. So it's a subform of a
subform.
The communication form collects data regarding phone calls, emails,
text
messages, faxes, etc. I'd like for the Contact's name that any of that
communication is with to automatically be filled in at the top of the
form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " & [Forms]![Contacts]![LastName]
in
the
control source property. It returns #Name? during testing. Is there an
expression which will automatically fill-in the contact's name from the
contact form in the communication form? Thanks for any help, amjjam

 

[Ken Sheridan]

Presumably the communications subform's underlying table includes a foreign
key column such as ContactID referencing the primary key of the contacts
form's underlying table, so you should be able to look up the values from the
table with:

=DLookup("Firstname & "" "" & LastName", "Contacts", "ContactID = " &
Nz(ContactID,0))

Ken Sheridan
Stafford, England

 

[amjjam]

Hi Ken, You were correct, about the foreign key referencing the primary key.
I put the following expression into the text box. It’s got all of the labels
that match my tables:

=DLookup(“FirstName & “ “ & LastNameâ€, “Contactsâ€, “ContactsID = “ &
Nz(ContactsID,0))

I got the following error message: “The expression you entered contains
invalid syntax. You may have entered an operand without an operator.†I can’t
get it working and am not seeing the syntax error. ... I tried changing the
expression to =DLookup(“[FirstName] & “ “ [LastName]â€, “[Contacts]â€,
“[ContactsID] = [Forms]![Contacts]![ContactsID]â€) That doesn’t work either.
If you’ve got further thoughts, I’d appreciate them. Thanks.

Presumably the communications subform's underlying table includes a foreign
key column such as ContactID referencing the primary key of the contacts
form's underlying table, so you should be able to look up the values from the
table with:

=DLookup("Firstname & "" "" & LastName", "Contacts", "ContactID = " &
Nz(ContactID,0))

Ken Sheridan
Stafford, England

Hi,

I have a "Communication" form inserted as a tab into a "Contacts" form which
is inserted as a tab into a "Company" form. So it's a subform of a subform.
The communication form collects data regarding phone calls, emails, text
messages, faxes, etc. I'd like for the Contact's name that any of that
communication is with to automatically be filled in at the top of the form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " & [Forms]![Contacts]![LastName] in the
control source property. It returns #Name? during testing. Is there an
expression which will automatically fill-in the contact's name from the
contact form in the communication form? Thanks for any help, amjjam

 

[amjjam]

Damon,

The Communication subform’s table is linked to the Contacts table by
including a ContactsID foreign key in the Communication table that references
the primary key in the Contacts table. The name of my text box is
ContactsNameText. I tried inserting the code you recommended into the current
event of the Communications subform, but can’t get it to work. I’m a fairly
new Access programmer, so I appreciate your efforts to help.

Put the code in the current event of the Comm subform -
Private Sub Form_Current()
Me.[nameofyourtextbox] = Me.Parent!FirstName & " " & Me.Parent!LastName
End Sub

There is a master child relationship between subform Contacts and subform
Communication, right?

Damon

Thanks for the idea, but it didn't work. I put the expression you
suggested
into the control source property of the Communication form and it still
returns #Name? So if you've got any other suggestions, I'd welcome them.
Thanks again, amjjam

try:
= me.Parent![nameofcontrol]

In your case, the control name is FirstName, so-

=me.Parent![FirstName] & " " & me.Parent![LastName]

Damon


Hi,

I have a "Communication" form inserted as a tab into a "Contacts" form
which
is inserted as a tab into a "Company" form. So it's a subform of a
subform.
The communication form collects data regarding phone calls, emails,
text
messages, faxes, etc. I'd like for the Contact's name that any of that
communication is with to automatically be filled in at the top of the
form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " & [Forms]![Contacts]![LastName]
in
the
control source property. It returns #Name? during testing. Is there an
expression which will automatically fill-in the contact's name from the
contact form in the communication form? Thanks for any help, amjjam

 

[amjjam]

Damon,

The Communication subform’s table is linked to the Contacts table by
including a ContactsID foreign key in the Communication table that references
the primary key in the Contacts table. The name of my text box is
ContactsNameText. I tried inserting the code you recommended into the current
event of the Communications subform, but can’t get it to work. I’m a fairly
new Access programmer, so I appreciate your efforts to help.

Put the code in the current event of the Comm subform -
Private Sub Form_Current()
Me.[nameofyourtextbox] = Me.Parent!FirstName & " " & Me.Parent!LastName
End Sub

There is a master child relationship between subform Contacts and subform
Communication, right?

Damon

Thanks for the idea, but it didn't work. I put the expression you
suggested
into the control source property of the Communication form and it still
returns #Name? So if you've got any other suggestions, I'd welcome them.
Thanks again, amjjam

try:
= me.Parent![nameofcontrol]

In your case, the control name is FirstName, so-

=me.Parent![FirstName] & " " & me.Parent![LastName]

Damon


Hi,

I have a "Communication" form inserted as a tab into a "Contacts" form
which
is inserted as a tab into a "Company" form. So it's a subform of a
subform.
The communication form collects data regarding phone calls, emails,
text
messages, faxes, etc. I'd like for the Contact's name that any of that
communication is with to automatically be filled in at the top of the
form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " & [Forms]![Contacts]![LastName]
in
the
control source property. It returns #Name? during testing. Is there an
expression which will automatically fill-in the contact's name from the
contact form in the communication form? Thanks for any help, amjjam

 

[Ken Sheridan]

You need to use two pairs of contiguous quotes characters to represent the
literal quotes characters when concatenating the first and last names:

=DLookup("FirstName & "" "" & LastNameâ€, “Contacts", "ContactsID = " &
Nz(ContactsID,0))

A pair of contiguous quotes characters within a string expression itself
delimited with quotes characters is interpreted as a literal quotes character.

Ken Sheridan
Stafford, England

Hi Ken, You were correct, about the foreign key referencing the primary key.
I put the following expression into the text box. It’s got all of the labels
that match my tables:

=DLookup(“FirstName & “ “ & LastNameâ€, “Contactsâ€, “ContactsID = “ &
Nz(ContactsID,0))

I got the following error message: “The expression you entered contains
invalid syntax. You may have entered an operand without an operator.†I can’t
get it working and am not seeing the syntax error. ... I tried changing the
expression to =DLookup(“[FirstName] & “ “ [LastName]â€, “[Contacts]â€,
“[ContactsID] = [Forms]![Contacts]![ContactsID]â€) That doesn’t work either.
If you’ve got further thoughts, I’d appreciate them. Thanks.

Presumably the communications subform's underlying table includes a foreign
key column such as ContactID referencing the primary key of the contacts
form's underlying table, so you should be able to look up the values from the
table with:

=DLookup("Firstname & "" "" & LastName", "Contacts", "ContactID = " &
Nz(ContactID,0))

Ken Sheridan
Stafford, England

Hi,

I have a "Communication" form inserted as a tab into a "Contacts" form which
is inserted as a tab into a "Company" form. So it's a subform of a subform.
The communication form collects data regarding phone calls, emails, text
messages, faxes, etc. I'd like for the Contact's name that any of that
communication is with to automatically be filled in at the top of the form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " & [Forms]![Contacts]![LastName] in the
control source property. It returns #Name? during testing. Is there an
expression which will automatically fill-in the contact's name from the
contact form in the communication form? Thanks for any help, amjjam

 

[Damon Heron]

When you say "I can't get it to work" what does that mean? Were there
error msgs -did it stop at a particular line or word in the code? All I can
tell you is that it works fine for me.

Damon

Damon,

The Communication subform's table is linked to the Contacts table by
including a ContactsID foreign key in the Communication table that
references
the primary key in the Contacts table. The name of my text box is
ContactsNameText. I tried inserting the code you recommended into the
current
event of the Communications subform, but can't get it to work. I'm a
fairly
new Access programmer, so I appreciate your efforts to help.

Put the code in the current event of the Comm subform -
Private Sub Form_Current()
Me.[nameofyourtextbox] = Me.Parent!FirstName & " " & Me.Parent!LastName
End Sub

There is a master child relationship between subform Contacts and subform
Communication, right?

Damon

Thanks for the idea, but it didn't work. I put the expression you
suggested
into the control source property of the Communication form and it still
returns #Name? So if you've got any other suggestions, I'd welcome
them.
Thanks again, amjjam

:

try:
= me.Parent![nameofcontrol]

In your case, the control name is FirstName, so-

=me.Parent![FirstName] & " " & me.Parent![LastName]

Damon


Hi,

I have a "Communication" form inserted as a tab into a "Contacts"
form
which
is inserted as a tab into a "Company" form. So it's a subform of a
subform.
The communication form collects data regarding phone calls, emails,
text
messages, faxes, etc. I'd like for the Contact's name that any of
that
communication is with to automatically be filled in at the top of
the
form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " &
[Forms]![Contacts]![LastName]
in
the
control source property. It returns #Name? during testing. Is there
an
expression which will automatically fill-in the contact's name from
the
contact form in the communication form? Thanks for any help, amjjam

 

[amjjam]

Thanks, Ken! This is working for me now.

You need to use two pairs of contiguous quotes characters to represent the
literal quotes characters when concatenating the first and last names:

=DLookup("FirstName & "" "" & LastNameâ€, “Contacts", "ContactsID = " &
Nz(ContactsID,0))

A pair of contiguous quotes characters within a string expression itself
delimited with quotes characters is interpreted as a literal quotes character.

Ken Sheridan
Stafford, England

Hi Ken, You were correct, about the foreign key referencing the primary key.
I put the following expression into the text box. It’s got all of the labels
that match my tables:

=DLookup(“FirstName & “ “ & LastNameâ€, “Contactsâ€, “ContactsID = “ &
Nz(ContactsID,0))

I got the following error message: “The expression you entered contains
invalid syntax. You may have entered an operand without an operator.†I can’t
get it working and am not seeing the syntax error. ... I tried changing the
expression to =DLookup(“[FirstName] & “ “ [LastName]â€, “[Contacts]â€,
“[ContactsID] = [Forms]![Contacts]![ContactsID]â€) That doesn’t work either.
If you’ve got further thoughts, I’d appreciate them. Thanks.

Presumably the communications subform's underlying table includes a foreign
key column such as ContactID referencing the primary key of the contacts
form's underlying table, so you should be able to look up the values from the
table with:

=DLookup("Firstname & "" "" & LastName", "Contacts", "ContactID = " &
Nz(ContactID,0))

Ken Sheridan
Stafford, England

:

Hi,

I have a "Communication" form inserted as a tab into a "Contacts" form which
is inserted as a tab into a "Company" form. So it's a subform of a subform.
The communication form collects data regarding phone calls, emails, text
messages, faxes, etc. I'd like for the Contact's name that any of that
communication is with to automatically be filled in at the top of the form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " & [Forms]![Contacts]![LastName] in the
control source property. It returns #Name? during testing. Is there an
expression which will automatically fill-in the contact's name from the
contact form in the communication form? Thanks for any help, amjjam

 

[amjjam]

Hi Damon,

Thanks for your efforts to help. You're right; I should have elaborated on
the information the program was giving me back when I tried to implement your
solution to my problem. I'll do that in the future. As it is, Ken's post in
this thread - using a DLookup expression - helped me get my db to function
the way I want it to. Next time I ask for help, I will, however, explain
what's going on to the helpers as I try their suggestions. Thanks for
alerting me to the need to do so. ~ amjjam

When you say "I can't get it to work" what does that mean? Were there
error msgs -did it stop at a particular line or word in the code? All I can
tell you is that it works fine for me.

Damon

Damon,

The Communication subform's table is linked to the Contacts table by
including a ContactsID foreign key in the Communication table that
references
the primary key in the Contacts table. The name of my text box is
ContactsNameText. I tried inserting the code you recommended into the
current
event of the Communications subform, but can't get it to work. I'm a
fairly
new Access programmer, so I appreciate your efforts to help.

Put the code in the current event of the Comm subform -
Private Sub Form_Current()
Me.[nameofyourtextbox] = Me.Parent!FirstName & " " & Me.Parent!LastName
End Sub

There is a master child relationship between subform Contacts and subform
Communication, right?

Damon


Thanks for the idea, but it didn't work. I put the expression you
suggested
into the control source property of the Communication form and it still
returns #Name? So if you've got any other suggestions, I'd welcome
them.
Thanks again, amjjam

:

try:
= me.Parent![nameofcontrol]

In your case, the control name is FirstName, so-

=me.Parent![FirstName] & " " & me.Parent![LastName]

Damon


Hi,

I have a "Communication" form inserted as a tab into a "Contacts"
form
which
is inserted as a tab into a "Company" form. So it's a subform of a
subform.
The communication form collects data regarding phone calls, emails,
text
messages, faxes, etc. I'd like for the Contact's name that any of
that
communication is with to automatically be filled in at the top of
the
form.
I've inserted an unbound text box and tried using the expression
=[Forms]![Contacts]![FirstName] & " " &
[Forms]![Contacts]![LastName]
in
the
control source property. It returns #Name? during testing. Is there
an
expression which will automatically fill-in the contact's name from
the
contact form in the communication form? Thanks for any help, amjjam