DLookup Stopped working when subform placed on tab control

[Ruth]

I am building a form with multiple tab controls in Access 2003. Each
control will have a subform linked to the OrgID field that I've hidden
in the form header. I built my subform and used DLookup strings to
populate several fields. When I viewed the subform in form view (prior
to placing on the tab) the DLookups worked. Now they don't. I've added
the subform name to the string, but still no luck. I'm wondering if I
need to reference the tab name as well.

In short, the database tracks visits to organizations. Multiple
contacts work at each organization. I need to be able to select the
contact for each visit from a list. (That part works fine.) The form
shows details for a specific visit.

Here are the details:
Form name: frmVisits
Subform name: frmVisitOrgTab
Tab control namegVisitInfo

Combo Box [ContactID] on subform has the following statement to select
Contacts who work at the Organization we're viewing details for.

SELECT tblContacts.ContID, tblContacts!FName & " " & tblContacts!LName
AS Name, tblOrgs.OrgID FROM tblOrgs INNER JOIN tblContacts ON
tblOrgs.OrgID=tblContacts.OrgID WHERE (((tblOrgs.OrgID)=Forms!
frmVisits![Visit Details].Form!OrgID));

The text values for position titles are stored in the tblTitles while
a title ID# is stored in the tblContacts. I have a text box doing a
lookup for the contact's title. It will return a number which I was
able to use to show the text title in another lookup.

First lookup:

Control Name: txtTitle-LU
Expression: DLookUp("[Title]","tblContacts","[ContID] =" & Forms!
frmVisits!pgVisitInfo!frmVisitOrgTab!ContactID)

This isn't returning anything so my Title lookup isn't working.

But the lookups for phone, e-mail and zip aren't working either.
Sample:
=DLookUp("[Phone]","tblContacts","[ContID] =" & Forms!frmVisitOrgTab!
ContactID)

I've tried adding the name of the tab (may be out of sequence as I
don't even know where to put it) and .Column(0) at the end of the
[ContactID] part of the string.

Can anyone help? I'm really frustrated as it worked perfectly before I
placed the subform on the tab!

Thanks
Ruth

 

[Guest]

The path to a field in the sub form should be

Forms![MainFormName]![SubFormControlName].Form![FieldName]

I can't see that path in the dlookup, change the name to fit your objects
name.
Expression: DLookUp("[Title]","tblContacts","[ContID] =" & Forms!
frmVisits!pgVisitInfo!frmVisitOrgTab!ContactID)

In the Row Source of the combo you have the right path, but not in the dlookup

Note: the TAB doesn't apear in the path, it make no different if the sub
form on a TAB or not, the path will be the same

 

[Ruth]

Thanks so much for the suggestion. Actually, last night after I posted
my question, I noticed that. I had been tinkering with multiple ways
to get the control to work. I double-checked this morning, comparing
your formula (couldn't see any difference) and then pasting yours
(just in case there was something I was missing) and it still won't
return a value. I don't think it would bug me so much if I hadn't had
it working at one time!

Thanks for your suggestion though!

 

[JK]

Hi Ruth,

You should *not* refer to the tab name. If you had it working before you put
the sub form(s) in tab control(s) it should work just the same. Do you get
an error or there is no result from the Dlookup()?

As far as I can see, if the right side of the expressions are numbers, both
your Dlookup() syntax are fine as they are.

Regards
Jacob


|I am building a form with multiple tab controls in Access 2003. Each
| control will have a subform linked to the OrgID field that I've hidden
| in the form header. I built my subform and used DLookup strings to
| populate several fields. When I viewed the subform in form view (prior
| to placing on the tab) the DLookups worked. Now they don't. I've added
| the subform name to the string, but still no luck. I'm wondering if I
| need to reference the tab name as well.
|
| In short, the database tracks visits to organizations. Multiple
| contacts work at each organization. I need to be able to select the
| contact for each visit from a list. (That part works fine.) The form
| shows details for a specific visit.
|
| Here are the details:
| Form name: frmVisits
| Subform name: frmVisitOrgTab
| Tab control namegVisitInfo
|
| Combo Box [ContactID] on subform has the following statement to select
| Contacts who work at the Organization we're viewing details for.
|
| SELECT tblContacts.ContID, tblContacts!FName & " " & tblContacts!LName
| AS Name, tblOrgs.OrgID FROM tblOrgs INNER JOIN tblContacts ON
| tblOrgs.OrgID=tblContacts.OrgID WHERE (((tblOrgs.OrgID)=Forms!
| frmVisits![Visit Details].Form!OrgID));
|
| The text values for position titles are stored in the tblTitles while
| a title ID# is stored in the tblContacts. I have a text box doing a
| lookup for the contact's title. It will return a number which I was
| able to use to show the text title in another lookup.
|
| First lookup:
|
| Control Name: txtTitle-LU
| Expression: DLookUp("[Title]","tblContacts","[ContID] =" & Forms!
| frmVisits!pgVisitInfo!frmVisitOrgTab!ContactID)
|
| This isn't returning anything so my Title lookup isn't working.
|
| But the lookups for phone, e-mail and zip aren't working either.
| Sample:
| =DLookUp("[Phone]","tblContacts","[ContID] =" & Forms!frmVisitOrgTab!
| ContactID)
|
| I've tried adding the name of the tab (may be out of sequence as I
| don't even know where to put it) and .Column(0) at the end of the
| [ContactID] part of the string.
|
| Can anyone help? I'm really frustrated as it worked perfectly before I
| placed the subform on the tab!
|
| Thanks
| Ruth
|

 

[JK]

oops,

This one is *incorrect*

DLookUp("[Title]","tblContacts","[ContID] =" & Forms!
frmVisits!pgVisitInfo!frmVisitOrgTab!ContactID)

It refres to a page, the correct one is:

DLookUp("[Title]","tblContacts","[ContID] =" _
& Forms!frmVisits!frmVisitOrgTab!ContactID)

Regards



| Hi Ruth,
|
| You should *not* refer to the tab name. If you had it working before you
put
| the sub form(s) in tab control(s) it should work just the same. Do you
get
| an error or there is no result from the Dlookup()?
|
| As far as I can see, if the right side of the expressions are numbers,
both
| your Dlookup() syntax are fine as they are.
|
| Regards
| Jacob
|
|
| ||I am building a form with multiple tab controls in Access 2003. Each
|| control will have a subform linked to the OrgID field that I've hidden
|| in the form header. I built my subform and used DLookup strings to
|| populate several fields. When I viewed the subform in form view (prior
|| to placing on the tab) the DLookups worked. Now they don't. I've added
|| the subform name to the string, but still no luck. I'm wondering if I
|| need to reference the tab name as well.
||
|| In short, the database tracks visits to organizations. Multiple
|| contacts work at each organization. I need to be able to select the
|| contact for each visit from a list. (That part works fine.) The form
|| shows details for a specific visit.
||
|| Here are the details:
|| Form name: frmVisits
|| Subform name: frmVisitOrgTab
|| Tab control namegVisitInfo
||
|| Combo Box [ContactID] on subform has the following statement to select
|| Contacts who work at the Organization we're viewing details for.
||
|| SELECT tblContacts.ContID, tblContacts!FName & " " & tblContacts!LName
|| AS Name, tblOrgs.OrgID FROM tblOrgs INNER JOIN tblContacts ON
|| tblOrgs.OrgID=tblContacts.OrgID WHERE (((tblOrgs.OrgID)=Forms!
|| frmVisits![Visit Details].Form!OrgID));
||
|| The text values for position titles are stored in the tblTitles while
|| a title ID# is stored in the tblContacts. I have a text box doing a
|| lookup for the contact's title. It will return a number which I was
|| able to use to show the text title in another lookup.
||
|| First lookup:
||
|| Control Name: txtTitle-LU
|| Expression: DLookUp("[Title]","tblContacts","[ContID] =" & Forms!
|| frmVisits!pgVisitInfo!frmVisitOrgTab!ContactID)
||
|| This isn't returning anything so my Title lookup isn't working.
||
|| But the lookups for phone, e-mail and zip aren't working either.
|| Sample:
|| =DLookUp("[Phone]","tblContacts","[ContID] =" & Forms!frmVisitOrgTab!
|| ContactID)
||
|| I've tried adding the name of the tab (may be out of sequence as I
|| don't even know where to put it) and .Column(0) at the end of the
|| [ContactID] part of the string.
||
|| Can anyone help? I'm really frustrated as it worked perfectly before I
|| placed the subform on the tab!
||
|| Thanks
|| Ruth
||
|
|

 

[Ruth]

Thanks, Jacob! I'll give that a whirl.