Accessing table field from unbound form field?

[DKDiveDude]

Accessing table field from unbound form field?

Not sure that really is the best way to put the following problem, as a question, but here is my problem with simplified example tables and forms, which I never been able to easily solve it even with other databases:

Tables:

tblDepartment - Related to below user table (One to Many)
numDepartmentID
txtDepartmentName

tblUsers
numUserID
txtUserName
numDepartmentFKID

tblResources
numResourceID
txtResourceName

tblUsersXResources
numUsersXResourcesID
numUserFKID
numResourceFKID


Form:

frmResources
txtResourceName

Subform - Continuous Forms:

frmUsersXResources
numUserFKID - which looks up the user name

and here comes the part I can not figure out. On this subform I want to show and even be able to change/select the department name for this user. The problem is I want or at least tried to make a query that lookup up the department name of the user, but the problem seems to be that the user ID is on this subform (Continuous Forms), so I can't include this "field"/data in my query. Example:

numDepartmentFKID - which looks up the department this user belongs to

Remember the above tables is NOT the real database I am working with, where I have many more tables, and forms with subforms, with subforms etc. It is just a simplified example.

Let me know if I need to explain, or rephrase my self in more detail.

Thanks

 

[Pat Hartman]

If you want to show both the department ID and its name on the subform, use a query that joins tblDepartments and tblUsers. Make sure that you set the DepartmentName field properties to Locked = Yes to prevent accidental updates from this form.

I have to compliment you on your attempts to name things properly. However, (don't you just hate however in this context) column names do not normally have datatype prefixes. They are the one object type that does not normally include a prefix in their name. My personal perference is for foreign key names to be identical to the primary key they relate to. In most cases the primary key name ends with ID so the foreign keys are easy to spot even with out the extra help. But, your addition of FK is fine.

 

[DKDiveDude]

Ok, it is almost a year after I posted about this problem.

I guess my previous problem description was not the best, and I forgot to mention that the subform was in table view.

If anyone has a minute please look at the attached simplified example database, and tell me how I can display the movie description in the unbound field of the subform.

Thanks

 

[Pat Hartman]

You have caused your own confusion by using the lookup "feature" in the table. This prevents you from "seeing" the actual contents of the query. I don't ever use this "feature". If I don't want to see the id in a particular query, I join to the lookup table. It isn't really that hard. The "feature" is there in a misguided attempt to help newcommers by disguising the fact that there is a relationship to another table.

You need to do EXACTLY what I said to do. Base the form on a query. In the query join the main table to the lookup table. Select the text value from the lookup table rather than the ID value from the main table.