Pulling a Single Value from a Child Table/Subform for Display on the Main Form

[Larry Lutz]

I'm using Access 2003, but need to remain compatible with Access 2000. I
need to display information from a selected record in a subform on the main
form, and can't seem to figure out how to do it.

The Parent Table, tblTasks (and, hence, form) has the following fields:

taskID - Long Integer (autonumber)
taskName - Text
taskDescription - Memo
taskDueDate - Date
and so on

The subform is based on a query, qselCategory, joining two tables:

The first table, tblCategories, has the following fields:

relcatID - Long Integer (autonumber)
relcatTask - Long Integer
relcatCategory - Long Integer
relcatPrimary - Yes/No

The second table, tlkpCategory, has the following fields:

catID - Long Integer (autonumber)
catName - Text

The query joins these two tables as relcatCategory <--> catID, one to many,
with relcatCategory being the one side.

The form/subform are joined on taskID <--> relcatTask, one to many, with
taskID being the one side.

What I want to do is display catName from qselCategory where relcatPrimary
is True on the main form. This field is used for information purposes only,
and would not be used for data entry. Because there is a lot of information
on the form (it uses tabs), the information field would be very helpful.
However, try as I might, I can't seem to get a SQL string to work as the
control source for an unbound text box. The remainder of the form is used
for data enty/update, so I can do anything that would result in a
non-updateable query. Is there any way to do this?

Larry

 

[Steve Schapel]

Larry,

=IIf([NameOfSubform]![relcatPrimary],[NameOfSubform]![catName],Null)

You might need to Requery the main form control, on the Current event of
the subform.