Creating a form based on two differnt tables

[Guest]

Hi,

I have a Subject table, which the 1st 3 subjects have Key Objectives, which
are stored within another table and subjects 4 to 12 have Topics, again
stored in another table.

What i want to be able to do, is be able to enter in Subject ID 1, then this
should display the key objectioves which relate to that ID, then if i type in
Subject ID 8, it would display the topics which relate to that Subject ID.

Not been able to create a query to do this, so please any help will be
grateful. Thanks!

 

[Guest]

Hi, kmistry.

Assuming a Subject has EITHER associated Topics or Objectives, you need a
field in Subjects to denote which this is. Let's say it's a numeric field
that you set with an option group, with values 1 for Topics and 2 for
Objectives. Then create a subform for each of the detail tables, set their
Visible property to False in Design View, and place both into your main
Subjects form.

Then use the On Current event to toggle the Visible property as needed:

If Me!MyOptionGroup = 1 Then
Me!sbfTopics.Visible = True
Me!sbfObjectives.Visible = False
Else
Me!sbfTopics.Visible = False
Me!sbfObjectives.Visible = True
End If

Alternatively, you could store both Topics and Objectives in the same
detail, distinguishing between them with another field. Then you would need
only one subform, and not have to toggle Visible properties at all:

SubjectDetails
-----------------
SubjectDetailID AutoNumber Primary Key
DetailType Numeric (1 for Topic, 2 for Objective, etc.)
Detail Text

Finally, if you need both topics and objectives for a given subject, place
two subforms, each based on a query that selects records of a single
DetailType.

Hope that helps.
Sprinks