DLookup help

[MikeB]

I'm trying to display in the header of a form a name derived from the
"parents" of the form at higher level.

I have Courses, then Sections, then a Roster for each Section.

On the Roster form I'd like to display the Course Name and the Section
Number in the header.

This gives me the section number:
=DLookUp("[SectionNo]","[tblSections]","[SectionID] = " & [Forms]!
[CourseSections Subform]![SectionID])

How can I lookup and use the fk_CourseID in tblSections and use that
as the criteria to lookup the Course name in Courses?

THe only way I can think of is to create a hidden field in the header,
use DLookup to store the fk_CourseID in there and then use another
DLookup to use that value, but I'm not sure that is the right and or
elegant way to do it.

Thanks.

 

[BruceM]

If you have a Course main form with a Section subform and a Roster subform
within that you should be able to select the necessary fields from the main
form and first subform record sources. It would help to know more about how
your database is structured and where you would have the information
appear -- main form header, roster form header, or what exactly?

 

[MikeB]

Of course - you're right. Since the Course form is open (with the
CourseSection subform), I can simply get it from there.

I lost sight of that and thought I needed to construct a "query" that
took the fk_CourseID from the Sections table and then did the DLookup
in the Courses table.

I will do it as per your suggestion, but for the sake of my
enlightenment, is there a way to construck a more complex "Where"
clause for a DLookup query?

Thanks for your help, tho.

M

If you have a Course main form with a Section subform and a Roster subform
within that you should be able to select the necessary fields from the main
form and first subform record sources.  It would help to know more about how
your database is structured and where you would have the information
appear -- main form header, roster form header, or what exactly?

I'm trying to display in the header of a form a name derived from the
"parents" of the form at higher level.

I have Courses, then Sections, then a Roster for each Section.

On the Roster form I'd like to display the Course Name and the Section
Number in the header.

This gives me the section number:
=DLookUp("[SectionNo]","[tblSections]","[SectionID] = " & [Forms]!
[CourseSections Subform]![SectionID])

How can I lookup and use the fk_CourseID in tblSections and use that
as the criteria to lookup the Course name in Courses?

THe only way I can think of is to create a hidden field in the header,
use DLookup to store the fk_CourseID in there and then use another
DLookup to use that value, but I'm not sure that is the right and or
elegant way to do it.

Thanks.

 

[MikeB]

If you have a Course main form with a Section subform and a Roster subform
within that you should be able to select the necessary fields from the main
form and first subform record sources.  It would help to know more about how
your database is structured and where you would have the information
appear -- main form header, roster form header, or what exactly?

I didn't answer your question fully. My apologies.

The way I have this at the moment is that I have a form for courses
with a subform for sections of the course. Then for each section, I
was going to create a button, when pressed would open a Roster for
that particular subsection. For purposes of identification, I wanted
the Course name and section number to appear in the header of the
Roster form.

You can see the relationships between the tables here:
http://i42.tinypic.com/53k01t.jpg

My usual hobby is to try and create a chess database for my chess
club, but I got involved in helping a friend with something he's been
messing with. Not exactly sure exactly what he was trying to do, but I
just took it on to see how I would do something like this. Just to
break the monotony of working on the Chess database where I'm a little
stalled right now.

 

[John W. Vinson]

I will do it as per your suggestion, but for the sake of my
enlightenment, is there a way to construck a more complex "Where"
clause for a DLookup query?

The WHERE clause can contain multiple criteria - anything you can use in a
Query can be duplicated in a domain function. You just need to piece the
chunks together with the appropriate AND, OR and other operators.

For example,

=DLookUp("[SectionNo]","[tblSections]","[CourseID] = " & [Forms]!
[Mainform]![CourseID] & " AND [SectionID] = [Forms]![Mainform]![CourseSections
Subform].Form![CourseID])

Note that a Subform is NOT open in its own right, and is NOT a member of the
FORMS collection - you need to get to it via the main form and the name of the
subform control on that form.

 

[MikeB]

I will do it as per your suggestion, but for the sake of my
enlightenment, is there a way to construck a more complex "Where"
clause for a DLookup query?

The WHERE clause can contain multiple criteria - anything you can use in a
Query can be duplicated in a domain function. You just need to piece the
chunks together with the appropriate AND, OR and other operators.

For example,

=DLookUp("[SectionNo]","[tblSections]","[CourseID] = " & [Forms]!
[Mainform]![CourseID] & " AND [SectionID] = [Forms]![Mainform]![CourseSections
Subform].Form![CourseID])

Note that a Subform is NOT open in its own right, and is NOT a member of the
FORMS collection - you need to get to it via the main form and the name of the
subform control on that form.

OK, thanks. Good point. In my efforts to make the DLookup work, I
simply opened the subform. I guess I'll have to rework the DLookup.
oh well, live and learn....

 

[BruceM]

One way is to use the same general approach John Vinson described for the
Where clause. In an unbound text box in the subform header you could have:
= "Course: " & Forms!CourseFormName!CourseName
In another:
= "Section: " & Forms!CourseFormName!SectionFormName.Form!SectionName

Note that you need to use the Form property (preceded by a dot) of the
section form.

Use your own form names, of course. CourseName and SectionName are fields
in the Course and Section tables, respectively. I think you can reference
the fields directly, but you may need to create hidden text boxes bound to
the CourseName and SectionName fields, and reference those instead of the
fields. It should not be necessary to do it that way with a form, but with
a report you need to, as I recall.

Note that in a report you may be able, depending on your display needs, to
create a query using all three tables, and group by Course and/or Section.
It may be a read-only query, but that doesn't matter with a report.

Another comment is that you can use the Roster subform's Current event to
place values into an unbound text box:

Me.txtSectionName = "Section: " & _
Forms!CourseFormName!SectionFormName.Form!SectionName

If you have a Course main form with a Section subform and a Roster subform
within that you should be able to select the necessary fields from the
main
form and first subform record sources. It would help to know more about
how
your database is structured and where you would have the information
appear -- main form header, roster form header, or what exactly?

I didn't answer your question fully. My apologies.

The way I have this at the moment is that I have a form for courses
with a subform for sections of the course. Then for each section, I
was going to create a button, when pressed would open a Roster for
that particular subsection. For purposes of identification, I wanted
the Course name and section number to appear in the header of the
Roster form.

You can see the relationships between the tables here:
http://i42.tinypic.com/53k01t.jpg

My usual hobby is to try and create a chess database for my chess
club, but I got involved in helping a friend with something he's been
messing with. Not exactly sure exactly what he was trying to do, but I
just took it on to see how I would do something like this. Just to
break the monotony of working on the Chess database where I'm a little
stalled right now.