What VBA Access 2000 code updates many-to-many relationships?

[Douglas J. Steele]

You might want to use the big white space underneath to post some
details....

 

[Jim]

I have a one to many relationship between tblMain!ID and tblAgeID!RecordID,
and a one to many relationship between tblAgeValue!AgeID and
tblAgeID!ValueID. That is, a many to many relationship between tblMain and
tblAgeValue, since each record in tblMain may contain more than one age value
and each age value in tblAgeValue may appear in more than one record in
tblMain.

tblMain!ID and tblAgeValue!AgeID are Autonumber primary keys. In the
junction table, tblAgeID!RecordID and tblAgeID!ValueID are long integer
primary keys.

The following code makes it down to the end but then claims that changes
would create duplicate values in index, primary key or relationship.

Private Sub cboAgeValue_NotInList(NewData As String, Response As Integer)

Dim rstMain As New ADODB.Recordset
Dim rstAgeID As New ADODB.Recordset
Dim rstAgeValue As New ADODB.Recordset
Dim intAnswer As Integer

On Error GoTo ErrorHandler

intAnswer = MsgBox("Add '" & NewData & "' to the list of age values?",
vbQuestion + vbYesNo)

If intAnswer = vbYes Then

rstAgeValue.Open "tblAgeValue", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
rstAgeID.Open "tblAgeID", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
rstMain.Open "tblMain", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

' Add age stored in NewData argument to new record in the age table.

rstAgeValue.AddNew
rstAgeValue!Value = NewData
rstAgeValue.Save

' Assign ID values to new record in the junction table.

rstAgeID.AddNew
rstAgeID!ValueID = rstAgeValue!AgeID
rstAgeID!RecordID = rstMain!ID

' Close the recordsets

rstAgeValue.Close

Response = acDataErrAdded

Else
Response = acDataErrDisplay ' Require the user to select an existing
age value

End If

Exit Sub

ErrorHandler:
MsgBox "Error # " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Detailed enough?

Thanks in advance for any help,
Jim

 

[Douglas J. Steele]

I believe the problem is your reference to rstAgeValue!AgeID in the line of
code

rstAgeID!ValueID = rstAgeValue!AgeID

That's going to be a reference to a (somewhat random) row in the recordset,
not the id you've just added.

Take a look at http://support.microsoft.com/?id=232144 for how to use
@@Identity to retrieve the value of the Autonumber field you've just added.

OTOH, what sort of values are you storing in tblAgeValue? If the ages are
strictly numeric, like 14 or 16, why bother with the autonumber field?

 

[Jim]

I'm off to a Christmas luncheon shortly so won't have time to review the
sample code you referred me to right away.

On your question re: tblAgeValue!Value, it is defined as text because it
takes on groupings such as "Age(8b)", "Age(7b)", "Age at immigration(6)",
"Age at immigration(5)" and so on. The user wants to be able to see all
records which use a particular age grouping value. Records may contain more
than one.

Many thanks for your help.

Jim