Need Help Understanding This

[EricG]

I have a form that has a sub-form on it. The contents of the sub-form are
keyed to the userID of the record on the main form. Originally, the contents
of the sub-form were drawn from a table that stored the user's information,
keyed on userID. Now, for reasons not worth going into, I'm storing that
information on a hidden form instead. Doing this has broken the sub-form and
I'm trying to figure out why.

This is how the query for the sub-form looks now:

SELECT tbl_Team_Info.* FROM tbl_Team_Info WHERE (((tbl_Team_Info.[Parent
ID])=[Forms]![frm_Hidden_User_Info]![TXT_currentUserID]));

"TXT_currentUserID" is one of the text boxes on the form.

When I view this query with the builder in datasheet view, it works fine.
However, when I bring up the main form, or even the sub-form, the sub-form is
empty. It doesn't seem to be drawing the data from 'tbl_Team_Info' like it
did before.

Sorry for the long winded explanation. Any hints regarding what I've done
wrong or which laws of proper database construction I've broken will be
greatly appreciated.

Thanks,

Eric

 

[Dirk Goldgar]

I have a form that has a sub-form on it. The contents of the sub-form are
keyed to the userID of the record on the main form. Originally, the
contents
of the sub-form were drawn from a table that stored the user's
information,
keyed on userID. Now, for reasons not worth going into, I'm storing that
information on a hidden form instead. Doing this has broken the sub-form
and
I'm trying to figure out why.

This is how the query for the sub-form looks now:

SELECT tbl_Team_Info.* FROM tbl_Team_Info WHERE (((tbl_Team_Info.[Parent
ID])=[Forms]![frm_Hidden_User_Info]![TXT_currentUserID]));

"TXT_currentUserID" is one of the text boxes on the form.

When I view this query with the builder in datasheet view, it works fine.
However, when I bring up the main form, or even the sub-form, the sub-form
is
empty. It doesn't seem to be drawing the data from 'tbl_Team_Info' like
it
did before.

Sorry for the long winded explanation. Any hints regarding what I've done
wrong or which laws of proper database construction I've broken will be
greatly appreciated.

What are the Link Master and Link Child Fields properties of the subform
control set to?

 

[EricG]

They are set to nothing. When I click on the builder button, I get a message
saying "Can't build a link between unbound forms."

I guess I left something important out - the main form is unbound. I use it
as an input form only, do quality checking on the data, and then use update
queries to update the main table.

This worked when the sub-form was gathering its data from a table. It
doesn't work when the sub-form tries to gather its data from another (open)
form instead.

 

[EricG]

Also, those fields are set to nothing for the version that fills the sub-form
using a table. Here is that query. This version works just fine.

SELECT tbl_Team_Info.* FROM tbl_UserInfo INNER JOIN tbl_Team_Info ON
tbl_UserInfo.currentUserID = tbl_Team_Info.[Parent ID] WHERE
(((tbl_Team_Info.[Parent ID])=[tbl_UserInfo]![currentUserID]));

 

[Dirk Goldgar]

Also, those fields are set to nothing for the version that fills the
sub-form
using a table. Here is that query. This version works just fine.

SELECT tbl_Team_Info.* FROM tbl_UserInfo INNER JOIN tbl_Team_Info ON
tbl_UserInfo.currentUserID = tbl_Team_Info.[Parent ID] WHERE
(((tbl_Team_Info.[Parent ID])=[tbl_UserInfo]![currentUserID]));

That query's WHERE clause appears to be redundant, since you're joining the
tables on the same equality of fields as the WHERE clause specifies.

 

[Dirk Goldgar]

They are set to nothing. When I click on the builder button, I get a
message
saying "Can't build a link between unbound forms."

I guess I left something important out - the main form is unbound. I use
it
as an input form only, do quality checking on the data, and then use
update
queries to update the main table.

This worked when the sub-form was gathering its data from a table. It
doesn't work when the sub-form tries to gather its data from another
(open)
form instead.

I'm finding this a bit confusing. Rather than go through a long process of
question and answer, would you be willing to send me your database? If so,
please compact it and zip it to reduce the download time, and send it to the
address derived by removing NO SPAM and ".invalid" from the reply address of
this message. If that address isn't visible to you, you can get my address
from my web site, which is listed in my sig. Do *not* post my real address
in the newsgroup -- I don't want to be buried in spam and viruses.

 

[Dirk Goldgar]

I'm finding this a bit confusing. Rather than go through a long process
of question and answer, would you be willing to send me your database? If
so, please compact it and zip it to reduce the download time, and send it
to the address derived by removing NO SPAM and ".invalid" from the reply
address of this message. If that address isn't visible to you, you can
get my address from my web site, which is listed in my sig. Do *not* post
my real address in the newsgroup -- I don't want to be buried in spam and
viruses.

Upon examination of the database, it turns out that the problem was that the
subform's Data Entry property was set to Yes, preventing it from showing any
existing records. Once that was changed, the subform behaved as intended.