Linking Records Back to Same Table

[Ernie]

I did this a long time ago, and no longer have the database to reference how
I did it.

I created a form on which I enter document ID's & some other info. After
entering one, it is highly likely that I will need to enter another document
ID that refers to the document ID just entered. I had set up the following:

tblDocs
DocID - Text - key field
DocDate - Date
DocType - Text

tblLinkDocs
OrigDocID - Text - key field
LinkedDocID - Text - key field

For search and retrieval purposes, I need the document ID's in a single
table. To have two tables or a second document ID field in the table would
limit flexibility and possibly mess up data integrity. I fooled around
doing everything wrong for most of the afternoon, but I just can't remember
(or find in the Knowledge Base) how I can do this. I'm not a very good
coder - so the simpler the better.

Thanks,

Ernie

 

[Ken Snell]

I'm sorry, but I just can't understand what you want to set up here? Can you
be more specific about what should happen when you do what you want to do?

 

[Ernie]

After I enter a new record, I then need to enter a related record (usually
just one, but potentially more than one, so it needs to be a one-to-many
relationship), but the related record must occupy the the same fields in the
same table.

The way I set it up was to create tblLinkDocs to contain the parent record
ID in the first field, and the child(ren) record ID(s) in the second field.
I created a form to enter the parent record, and a second form to enter the
related record. I tried it as a subform, too, but I got that wrong, too.

Does any of this make sense?

Thanks,

Ernie

 

[Ernie]

I guess I don't know the terminology to describe what I need. Imagine a
personnel table containing all the employees of an organization. An
employee can be a Vice-President, Director, Manager, Supervisor or Staff
Member. A VP might have three Directors reporting to her, and a Director
may have two Managers reporting to him. A Manager might have three or four
Supervisors reporting to her, and, finally, a Supervisor might have ten
Staff Members reporting to him. In order to properly represent this, I
can't just have another field to list the next descending person because
there could be one, or there could be many. There needs to be a table to
store each relationship, which, in my case is the tblLinkDocs table. It
contains a Document ID in the left field, and the child in the right field.
The same Document ID can appear in the left field several times - once for
each unique child Document ID in the right field. The design protects
against duplicate pairs, and permits the user to report an organizational
chart, of sorts.

I've built the tables, but I'm having trouble populating them through the
queries and forms I've designed. I'm lacking the knowledge that enables me
to do this. If I was working with a completely populated table, it would be
easy - create a subform to enter the Doc. ID's for the selected main form
item. But in this case, I'm entering a new document number (along with
additional fields), then dropping down to the subform to enter the related
document number (along with it's additional fields). tblDocs needs to add a
new record and tblLinkDocs needs to populate the LinkedDocID field.

Does this make any more sense?

Thanks for your patience - I apologize for not making this understandable.

Ernie

 

[Ken Snell]

Ernie -

I don't have the time tonite to respond in detail to this reply. But I will
do it tomorrow after work. Sorry for the delay!

 

[Armen Stein]

Ernie -

I don't have the time tonite to respond in detail to this reply. But I will
do it tomorrow after work. Sorry for the delay!

Hi Ernie,

I think you are trying to store all the children records with the
parent. The proper way to do this, and what Ken is suggesting, is that
each child record just have a field to store its parent's key.

In one-to-many relationships, remember that the parent has no idea of
who its children are. Each child must remember who its parent is!

So, in your Document table, you need a field like Parent_DocumentKey.
Make that a combobox on your Document form and you'll be all set.

Now, this assumes that a Document can have only one Parent document. If
it could have many, then you'll need another associative (many-to-many)
table.

Hope this helps,

 

[Ken Snell]

OK - with multiple associated documents for a document, then your concept of
a separate, joining table would be appropriate.

Here is how I'd approach what you want to do.

I'd set up a main form with controls on it for entering the "primary"
document. This main form should be bound to a query that contains the
appropriate fields for the main table that holds the document information. I
think I'd then put a subform on that main form, and bind this subform to the
same query that the main form is using. This subform would be used to enter
additional documents that will be associated to the document on the main
form.

I'd put a command button in the detail section of the subform. This command
button would be used to "save" the child document. By doing this, it then
would run code that would insert the joining records in the joining tables
(using the document ID from the main form and the document ID from the
current record of the subform).

The code for this command button would be something like this:

Private Sub cmdButtonSave_Click()
Dim strSQL As String
Dim dbs As DAO.Database

' Save the current record in the subform
DoCmd.RunCommand acCmdSaveRecord

' Insert a new record into the joining table
strSQL = "INSERT INTO tblLinkDocs (OrigDocID, LinkDocID) " & _
"SELECT " & Me.Parent.DocID & ", " & Me.DocID & ";"
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing

' Go to new record in the subform
DoCmd.RunCommand acCmdRecordsGoToNew

End Sub

I have not tested all of the above, but it should get you started.