Complicated Linking Problem

[G deady via AccessMonster.com]

I have two forms bound to different tables. I will use an example to
illustrate my problem.
I have 4 people who have each sent me 5 documents. I do not currently know
the identity of these 4 people so I can not create a lookup table. In the
future they will continue to send me docuements. In addition, other people,
currently unknown, will send me documents.
My first form records the author and the second form records the document
info. I have 3 problems. The first is setting up a procedure to make sure
the authors name does not already exist. The second is creating a link
between the forms so that the authors name is linked to the document the
first time. (If you have read any of my other posts you know this is part of
law office db and everything is already linked by client and case so this
would be a third link for these two forms. All 3 links are necessary here
because the same authors may send docuements on more than one case). And my
third problem is setting up some sort of query or lookup procedure so that I
can link the author to the docuement. I cant use the author as a lookup
table because I dont know the authors ahead of time and there will constantly
be additions to the authors. Its complicated bythe fact that I need to check
each docuement to see if its written by an existing author or new author.
I'm almost tempted to combine the tables and use one form but that would be
serious violation of normalization. Some Authors will be sending in excess
of 50 documents. Can anyone help me. I am completly at a loss as to how to
do this.

 

[Chris_h]

In this situation I would use a lookup table and query it using the author's
name when I add a new doc. If no results are returned then I'd add a record
to the lookup table for the new author. You'll need to use SQL SELECT and
INSERT statements for these respective actions. I have used the following
code in a simillar case:

'create a dataset variable that can be modified
Set dbC = CurrentDb
'Check to see if a particular table item has already beeen created
strSQL = "SELECT idea_id FROM tblEvaluation WHERE idea_id = " & idea_id & ";"
'exectute SQL
Set rcdID = dbC.OpenRecordset(strSQL)
'if no result then create a new table item
If rcdID.EOF Then
MsgBox ("not found")
'here's the insert statement
strSQL = "INSERT INTO tblEvaluation (idea_id, user_id) VALUES (" &
Forms.frmIdea_2.idea_id & "," & Forms.frmIdea_2.user_id & ");"
dbC.Execute (strSQL)
End If

There is a tutorial on SQL at www.w3schools.com if you're not familliar with
it. I have a unique id in tblEvaluation which you might link to the document
in your example. (I just use an autonumber) Hopefully you'll be able to
figure the third question once you've had a look at the tutorial!

Chris