Links to other records within the same table

[Guest]

Hi,
I'm a teacher trying to create a database for my lesson plans. I have a
"Topics" table which contains all the topics in my dB. I want to create a
field for each topic where I could write a description of the topic, and then
have links within that description to other topics (in the same table).
Hyperlinks, essentially. Is it possible to do this within Access? If not,
is it possible to do it by creating HTML pages through Access?

If you're not sure what I'm talking about here's an example:
Topic: Plate Tectonics
Description: Plate tectonics explains many geological events, including
"earthquakes" and the formation of "mountains".

I would want the ability to click on "earthquakes" and be taken to the
Earthquakes topic. Same for mountains.

Thanks,

 

[Guest]

Flup:

I think you are probably asking a lot to do this with hyperlinks. It might
be possible but would not be trivial and would need some work doing. Its not
something on which I could give an off the cuff answer.

The usual way to do this sort of thing in a relational database is to have a
table which models the relationship between the table and itself. What you'd
have is a table called something like CrossReferences with columns such as
ReferencingTopicID and ReferencedTopicID. Both of these would be foreign
keys referencing the primary key TopicID of the Topics table.

For data entry you would have a form based on the Topics table with a
subform within in based on the CrossRefrences table. The linking fields
would be TopicID and ReferncingTopicID. This subform would be in continuous
form view and would have just ine control, a combo box bound to the
ReferencedTopicID field. The combo box would not show this value, however,
but a more meaningful field from the Topics table, e.g. Topic which would be
a text field with the name of the topic. The properties of the combo box
would be along thse lines:

RowSource: SELECT TopicID, Topic FROM Topics ORDER BY Topic;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

With the last property the important thing is that the first dimension is
zero to hide the first column. The second is not crucial so long as its at
laest as wide as the combo box.

To cross reference a topic you simply select an item from the combo box's
list. Each cross reference will be a new row in the subform, so you can add
as many cross references as you wish.

To go to a cross reference you could out code in something like the DblClick
event procedure of the combo box on the subform which takes the parent form
to the cross referenced topic, e.g.

Dim rst As Object
DIM frm as Form

Set frm = me.Parent
Set rst = frm.Recordset.Clone

rst.FindFirst "TopicID = " & Me.cboCrossrefs
frm.Bookmark = rst.Bookmark

Alternatively you could open another form in dialog mode to show the cross
referenced topic while leaving the parent form at the original record:

Dim strCriteria As String

strCriteria = "TopicID = " & Me.cboCrossrefs

DoCmd.OpenForm "frmCrossRefs",
WhereCondtion:=strCriteria,WindowMode:=acDialog

If you wanted to be really fancy you could open multiple instances of the
Topics form as popups to show the cross references. First declare some
object variables in the form's module's Declarations area:

Dim frm As Form
Dim colPopups As New Collection

Then in the combo box's DblClick event procedure open instances of the form,
filtering each instance to the cross referenced topic:

Set frm = New Form_frmTopics
colPopups.Add frm, frm.hwnd & ""
frm.Filter = "TopicID = " & Me.cboCrossrefs
frm.FilterOn = True
frm.Visible = True

In all of the above I've assumed that TopicID (or whatever the primary key
of Topics is) is a numeric data type (e.g. an autonumber) rather than text.

 

[Guest]

Ken,
Thanks VERY much for taking the time to educate me. I'll give it a try!