Lookup Values Not Showing Up in Query

[Guest]

In Form A, there is a combo box (Document ID) that is used to lookup the
corresponding value (Document Title) in a table. The values for the Document
ID are in the same table as the Document Title. The values are used in form

When I run a query on the form's record's values, I get the Document ID;
however, I do not receive the corresponding Document Title. I need to use
the query's info to create a report. (I've done the same with Any guess on
why the Document Title is not appearing?

Note: There are five Document ID fields (Doc ID 1...5) and five Document
Title fields (Doc Title 1...5).

Thanks for the help.

 

[John W. Vinson]

In Form A, there is a combo box (Document ID) that is used to lookup the
corresponding value (Document Title) in a table. The values for the Document
ID are in the same table as the Document Title. The values are used in form

When I run a query on the form's record's values, I get the Document ID;
however, I do not receive the corresponding Document Title. I need to use
the query's info to create a report. (I've done the same with Any guess on
why the Document Title is not appearing?

You're apparently another victim of Microsoft's misdesigned,
misleading, and confusing Lookup Fields feature.

The table *DOES NOT* contain the Document Title. That title exists
only in the Documents table - the lookup table upon which your lookup
field is based. If the table with the combo box appears to contain the
title, *it doesn't*.

You need to base your Report on a query joining its table *to the
document table* by DocumentID.

Note: There are five Document ID fields (Doc ID 1...5) and five Document
Title fields (Doc Title 1...5).

Then your database design *is wrong*. Someday you'll need a sixth
document! If some manager says "that won't happen for years", it will
happen next week; if she says "that will never happen" then one of her
employees is grumbling about being unable to add the sixth document
ALREADY.

If you have many documents for each <whatever>, then you have a Many
to Many relationship, and you need another table linking the Documents
table to this table.

John W. Vinson [MVP]

 

[Guest]

John,

Thank you for the response.

First, how do I correct the latter problem (Document ID 1...5)? I realized
that I was doing something incorrect; however, at the time I needed a way to
identify the documents to be used. How do I create a single field and use it
over and over for entry?

Second (to address the first issue in my original post), there are two
tables. The first is tbl_iprinfo on which a query (qry__iprinfo) is used to
create frm_iprinfo.
The second table is tbl_DocumentDetails in which the document data is
retained. There is a query (qry_DocumentDetails) that is used to provide the
selection values for the combo box used in frm_iprinfo. (This second table
is the one that the lookup function is going to:
=DLookUp("tbl_DocumentDetails![Document Title]","tbl_DocumentDetails","""" &
[Document ID 1] & """ =tbl_DocumentDetails![Document ID]")

Am I to join qry_iprinfo to qry_DocumentDetails-- in order to get the values
to show up? (I tried this and received some data but again, no document
titles. I believe the problem lies in the naming of the fields as Document
ID 1...5. Correct me if I'm wrong.)

Thanks for your help,

LS

 

[John W. Vinson]

John,

Thank you for the response.

First, how do I correct the latter problem (Document ID 1...5)? I realized
that I was doing something incorrect; however, at the time I needed a way to
identify the documents to be used. How do I create a single field and use it
over and over for entry?

No. You add A THIRD TABLE.

If one iprinfo can have many documents, and each document can be
included in multiple iprinfos, you need a new table to resolve the
many to many relationship. This table would have a field for the
primary key of iprinfo, and another for the docID. If a given iprinfo
record needs five documents, you would have five RECORDS in this new
table - all for that iprinfo, with different DocID's.

Second (to address the first issue in my original post), there are two
tables. The first is tbl_iprinfo on which a query (qry__iprinfo) is used to
create frm_iprinfo.
The second table is tbl_DocumentDetails in which the document data is
retained. There is a query (qry_DocumentDetails) that is used to provide the
selection values for the combo box used in frm_iprinfo. (This second table
is the one that the lookup function is going to:
=DLookUp("tbl_DocumentDetails![Document Title]","tbl_DocumentDetails","""" &
[Document ID 1] & """ =tbl_DocumentDetails![Document ID]")

You can use DLookUp - but it's inefficient.

Am I to join qry_iprinfo to qry_DocumentDetails-- in order to get the values
to show up? (I tried this and received some data but again, no document
titles. I believe the problem lies in the naming of the fields as Document
ID 1...5. Correct me if I'm wrong.)

If you want to keep the five DocID's (not that I recommend doing so!)
the names ARE OF NO IMPORTANCE. You could call the five fields Moe,
Larry, Curly, CurlyJoe and Melvin if you wanted. What you would need
to do is add the DocInfo table *FIVE TIMES* to your query, one joined
to each of the fields. That's one (of many) reasons why it's better to
create this third table - you would simply use a Subform or a
Subreport to show as many records (one for each document) as you wish.

John W. Vinson [MVP]

 

[Guest]

Thanks.

I added a third table and based the subform on this third table. Is this
correct? One drop-down defaults to a value in the table, no matter what I
select. Another row is added with the default information. In addition, the
selected information is not retained. When I run the query, I get the
default value, but obviously, not the selected value.

If adding a third table and basing the subform on that table is not the
correct approach, which is the correct approach? I am puzzled by how the
application understands to populate the third table with the values. Through
the relationships. (Obviously, I am not a developer. I'm an improvement
consultant, who knows a bit about Access in order to help clients get
something they can convert after my departure into something more fancy and
to their liking.)

Yet, in the second subform, I select the values, but they disappear
completely. It doesn't default to some random value in the table, nor is the
selected data retained.

Incidentally, I have another database which has a subform and I attempted to
replicate the same thing. (I had a pc help group help with that one. I
don't see any established relationships, however, I think there must be some
but they are hidden since the data is populating the third table as it
should.)

Thanks for your help, John. After this I'm leaving you alone so you can work!

John,

Thank you for the response.

First, how do I correct the latter problem (Document ID 1...5)? I realized
that I was doing something incorrect; however, at the time I needed a way to
identify the documents to be used. How do I create a single field and use it
over and over for entry?

No. You add A THIRD TABLE.

If one iprinfo can have many documents, and each document can be
included in multiple iprinfos, you need a new table to resolve the
many to many relationship. This table would have a field for the
primary key of iprinfo, and another for the docID. If a given iprinfo
record needs five documents, you would have five RECORDS in this new
table - all for that iprinfo, with different DocID's.

Second (to address the first issue in my original post), there are two
tables. The first is tbl_iprinfo on which a query (qry__iprinfo) is used to
create frm_iprinfo.
The second table is tbl_DocumentDetails in which the document data is
retained. There is a query (qry_DocumentDetails) that is used to provide the
selection values for the combo box used in frm_iprinfo. (This second table
is the one that the lookup function is going to:
=DLookUp("tbl_DocumentDetails![Document Title]","tbl_DocumentDetails","""" &
[Document ID 1] & """ =tbl_DocumentDetails![Document ID]")

You can use DLookUp - but it's inefficient.

Am I to join qry_iprinfo to qry_DocumentDetails-- in order to get the values
to show up? (I tried this and received some data but again, no document
titles. I believe the problem lies in the naming of the fields as Document
ID 1...5. Correct me if I'm wrong.)

If you want to keep the five DocID's (not that I recommend doing so!)
the names ARE OF NO IMPORTANCE. You could call the five fields Moe,
Larry, Curly, CurlyJoe and Melvin if you wanted. What you would need
to do is add the DocInfo table *FIVE TIMES* to your query, one joined
to each of the fields. That's one (of many) reasons why it's better to
create this third table - you would simply use a Subform or a
Subreport to show as many records (one for each document) as you wish.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks.

I added a third table and based the subform on this third table. Is this
correct? One drop-down defaults to a value in the table, no matter what I
select. Another row is added with the default information. In addition, the
selected information is not retained. When I run the query, I get the
default value, but obviously, not the selected value.

What's the Recordsource for your form; for the subform; and what's the
RowSource and Control Source for the combo on the subform?

If adding a third table and basing the subform on that table is not the
correct approach, which is the correct approach? I am puzzled by how the
application understands to populate the third table with the values. Through
the relationships. (Obviously, I am not a developer. I'm an improvement
consultant, who knows a bit about Access in order to help clients get
something they can convert after my departure into something more fancy and
to their liking.)

It is the correct approach. The relationships DON'T "populate" the
third table; relationships only *prevent* addition of invalid values,
they don't add any values automagically!

Normally one would have the parent form based on the "one" side table,
which i'm GUESSING (I don't know your business or your database
structure clearly) is tbl_iprinfo; on this form would be a Subform
based on this third table. The Master Link Field would be the primary
key field of tbl_iprinfo, and the Child Link Field would be the
corresponding foreign key. On the subform you would have a combo box
based on the documents table to store the document ID.

Yet, in the second subform, I select the values, but they disappear
completely. It doesn't default to some random value in the table, nor is the
selected data retained.

A default value will NOT WORK HERE - a default only applies when a
record is created by editing some other field in the table. If you're
just selecting a document, you *need to select the document* to create
the record.

Incidentally, I have another database which has a subform and I attempted to
replicate the same thing. (I had a pc help group help with that one. I
don't see any established relationships, however, I think there must be some
but they are hidden since the data is populating the third table as it
should.)

Again... it sounds like you're misunderstanding how relationships
work. *Relationships do not populate ANYTHING*.


John W. Vinson [MVP]

 

[Guest]

Thank you, John.

What's the Recordsource for your form; for the subform; and what's the
RowSource and Control Source for the combo on the subform?


It is the correct approach. The relationships DON'T "populate" the
third table; relationships only *prevent* addition of invalid values,
they don't add any values automagically!

Normally one would have the parent form based on the "one" side table,
which i'm GUESSING (I don't know your business or your database
structure clearly) is tbl_iprinfo; on this form would be a Subform
based on this third table. The Master Link Field would be the primary
key field of tbl_iprinfo, and the Child Link Field would be the
corresponding foreign key. On the subform you would have a combo box
based on the documents table to store the document ID.


A default value will NOT WORK HERE - a default only applies when a
record is created by editing some other field in the table. If you're
just selecting a document, you *need to select the document* to create
the record.


Again... it sounds like you're misunderstanding how relationships
work. *Relationships do not populate ANYTHING*.


John W. Vinson [MVP]

 

[John W. Vinson]

Thank you, John.

so... did you get the subform working? Was it a missing master/child
link, or what?

Glad to be of help.

John W. Vinson [MVP]

 

[Guest]

John,

I sorta, sorta have the subforms working. Here is what I did:

tbl_IPRInfo has the following fields for which subforms are needed
(CMMIRequirement, IPRParticipants, DocumentID, and ProjectName).

For each one, I created a third table by opening a new table in Design View
and copying the fields (IPRID and each respective field) from tbl_IPRInfo, so
I now have:

tbl_RelatedCMMI, tbl_RelatedParticipants, tbl_RelatedProcesses,
tbl_RelatedProjects

In the Relationships window, I dragged IPRID from tbl_IPRInfo into the
original table for each of the tbl_IPRInfo fields. (tbl_CMMIRequirement,
tbl_Participants, etc.) This established a one-to-many relationship.

I dropped the subforms into frm_IPRInfo, each based on the third table.
Each subform has Link Master Fields: CMMIRequirement, Link Child Fields:
CMMIRequirement (respectively, for each of the others).

When I select the subform data, it disappears upon reopening of the form
(subform record count is zero); however, when I open the third table the data
is there. I need to see the data (obviously). The IPRID is not there,
either.

Should I have established the one-to-many relationship from tbl_IPRInfo to
the third table (tbl_RelatedProjects) instead of the original table
(tbl_ProjectName)?

I am still getting the default project (will not go away) in
subfrm_RelatedProject. Any idea why? (Incidentally, it doesn't show up in
the table as a selected project.)

Thanks again,

Londa Sue

 

[John W. Vinson]

John,

I sorta, sorta have the subforms working. Here is what I did:

tbl_IPRInfo has the following fields for which subforms are needed
(CMMIRequirement, IPRParticipants, DocumentID, and ProjectName).

For each one, I created a third table by opening a new table in Design View
and copying the fields (IPRID and each respective field) from tbl_IPRInfo, so
I now have:

tbl_RelatedCMMI, tbl_RelatedParticipants, tbl_RelatedProcesses,
tbl_RelatedProjects

ok... I hope there are no redundant fields across these...

In the Relationships window, I dragged IPRID from tbl_IPRInfo into the
original table for each of the tbl_IPRInfo fields. (tbl_CMMIRequirement,
tbl_Participants, etc.) This established a one-to-many relationship.

Four one to many relationships right?

I dropped the subforms into frm_IPRInfo, each based on the third table.
Each subform has Link Master Fields: CMMIRequirement, Link Child Fields:
CMMIRequirement (respectively, for each of the others).

Well, you know your fieldnames and datatypes. I don't.

When I select the subform data, it disappears upon reopening of the form
(subform record count is zero); however, when I open the third table the data
is there. I need to see the data (obviously). The IPRID is not there,
either.

Are these subforms in Data Entry mode? If so you won't see the existing
records! Check the Form properties and be certain that Data Entry is NO.

Should I have established the one-to-many relationship from tbl_IPRInfo to
the third table (tbl_RelatedProjects) instead of the original table
(tbl_ProjectName)?

Again... I DON'T KNOW, because I don't know what is in your tables.

I am still getting the default project (will not go away) in
subfrm_RelatedProject. Any idea why? (Incidentally, it doesn't show up in
the table as a selected project.)

Not a clue. Again - nothing you have posted indicates what the default might
be. It could be in the Table, it could be in a form control, it could be in
some VBA code.

John W. Vinson [MVP]

 

[Guest]

Only redundant field is IPRID.
There are four one-to-many relationships.
Data entry is set to "No".

Example on relationship establishment:

tbl_IPRInfo tbl_ProjectName tbl_RelatedProjects
IPRID ProjectName Copied IPRID to this table
ProjectName Copied ProjectName to this table

This created the one-to-many relationship.

I don't know why I'm not getting this info. I copied the following
instructions from another posted question, coupled with what you wrote (you'd
weighed in with the other person's question).

In any case, thank you. You are free to move about the cabin.

 

[Guest]

John,

I have these working. Thank you.

 

[John W. Vinson]

Only redundant field is IPRID.
There are four one-to-many relationships.
Data entry is set to "No".

Example on relationship establishment:

tbl_IPRInfo tbl_ProjectName tbl_RelatedProjects
IPRID ProjectName Copied IPRID to this table
ProjectName Copied ProjectName to this table

I don't see why tbl_ProjectName should exist AT ALL.

Is each record in tbl_IPRInfo for a different ProjectName? If so, that's the
ONLY table that should contain ProjectName.

Is there a record in tbl_RelatedProjects corresponding to every record in
tbl_IPRInfo? If so, then tbl_RelatedProjects need not and should not exist.

I'm just REALLY confused and I'm afraid I've confused you too!

John W. Vinson [MVP]

 

[Guest]

John,

Good morning. I've another question. Everything is working. I am working
on the report, which is the reason for my original inquiry (as I did not get
the values I should have been getting). How do I get the data to report in
some understandable fashion? That is, when I run the query which pulls data
from the IPR table and the related subforms, I get rows and rows (as
expected). I need to do some grouping so that for IPR 1, I am able to see
the projects selected, the requirements that apply, etc. I am getting
multiple rows for each project selected which is duplicate data. From a
query standpoint I understand this, but from a reporting view, it is not as
clean as it should be.

Thanks again for your help,

Londa Sue