Getting Foreign Key from Parent Table for New Record via SQL

[Ray Todd Jr]

I am getting error 3201 (You cannot add or change a record because a related
recored is required in table 'taDEFENDANTS'.)

I understand WHAT the error means, however, what I don't know is HOW to fix
it using SQL.

What I am trying to do is when I print a letter, to have a note put into the
notes table that a letter was printed and the date printed.

The two tables:

taDefendants taDEFENDANTSNotes
DefendantID (PK) DefNotesID (PK)
PropertyID DefendantID (FK)
BatchID ...
....

Private Sub cmdCreateBatch_Click()
'On Error Goto Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngBatchID As Long
Dim lngKt As Long
Dim lngKtSubtotal1 As Long
Dim lngKtSubtotal2 As Long
Dim strDefNote As String
Dim lngDefendantID As Long

strDefNote = "Letter Printed on " & Now()


'Create the new batch, and get the number.
Set db = CurrentDb()
Set rs = db.OpenRecordset("taBATCH", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!BatchDateTime = Now()
lngBatchID = rs!BatchID
rs.Update
rs.Close

'Give this batch number to all members who have not been printed.
strSql = "Update taDefendants set BatchID= " & lngBatchID & " Where
BatchID is Null;"
db.Execute strSql, dbFailOnError
lngKtSubtotal1 = db.RecordsAffected

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " _
& "values (" & lngDefendantID & ", '" & strDefNote & "')"
db.Execute strSql, dbFailOnError


Any help/suggestions would be appreciated.


Thanks,

Ray.

 

[Steve Sanford]

Your process in the code is:
* get a new Batch number
* update the table taDefendants
* insert records into taDefendantsNotes

The problem is that you are not passing a value for "lngDefendantID" when
you run the code to add records to the table "taDefendantsNotes".

Maybe this will work (or a MVP/ SQL guru can fix it):

strSql = "INSERT INTO taDefendantsNotes (DefendantID, DefNote)"
strSql = strSql & " SELECT taDefendants.DefendantID,'" & DefNote & "' as
TheNote"
strSql = strSql & " FROM taDefendants"
strSql = strSql & " WHERE DefendantID.BatchID = " & BatchID & ";"


The only way I know how to solve this involves a recordset:

' -- UNTESTED ---
Private Sub cmdCreateBatch_Click()
'On Error Goto Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngBatchID As Long
Dim lngKt As Long
Dim lngKtSubtotal1 As Long
Dim lngKtSubtotal2 As Long
Dim strDefNote As String
Dim lngDefendantID As Long

strDefNote = "Letter Printed on " & Now()


'Create the new batch, and get the number.
Set db = CurrentDb()
Set rs = db.OpenRecordset("taBATCH", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!BatchDateTime = Now()
lngBatchID = rs!BatchID
rs.Update
rs.Close

'Give this batch number to all members who have not been printed.
strSql = "Update taDefendants set BatchID= " & lngBatchID & " Where
BatchID is Null;"
db.Execute strSql, dbFailOnError
lngKtSubtotal1 = db.RecordsAffected

'------------new------

'get the DefendantID for the BatchID
strSql = "Select DefendantID From taDefendants"
strSql = strSql & " Where BatchID = " & lngBatchID

Set rs = db.OpenRecordset(strSql)

If Not rs.BOF And Not rs.EOF Then
rs.MoveLast
rs.MoveFirst

'loop thru the recordset
Do While Not rs.EOF
strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " _
& "values (" & lngDefendantID & ", '" & strDefNote & "')"
db.Execute strSql, dbFailOnError

rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing

' -- UNTESTED ---

 

[Ray Todd Jr]

Hello Steve:

Thanks for you help and time. The first suggestion does not work, or at
least, I wasn't able to get it to work. That was the way I was working
toward solving this problem previously.

My thinking parallels your in that this will involve using the recordset.
For the most part, your second suggestion works, but not perfectly. What it
does is if it finds 5 records that match where the DefendantID and the
BatchID match, it will post 5 copies of the DefendantNotes of the FIRST
defendant ID ONLY. It will not loop through each of the DefendantID.

At first, I thought I could solve this by assigning the result of the sql
select statement to a variable prior to the Update statement, however,
apparently I am unable too using this method:

lngDefendantID=db.Execute strSql, dbFailOnError

So, any suggestions from you or anyone else would be appreciated.


Here is the code as it currently exist:

' Place note into the taDEFENDANTSNOTE table detailing that the letter was
sent

strSql = "Select DefendantID from taDefendants " & _
"where BatchID= " & lngBatchID

Set rs = db.OpenRecordset(strSql)

If Not rs.BOF And Not rs.EOF Then
rs.MoveLast
rs.MoveFirst

'loop thru the recordset
Do While Not rs.EOF

lngDefendantID = DLookup("DefendantID", "taDefendants", "BatchID=" &
lngBatchID)

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " & _
"values (" & lngDefendantID & ", '" & strDefNote & "')"
db.Execute strSql, dbFailOnError

rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing

 

[Ray Todd Jr]

ok, I figured it out.

To solve the problem I put the following statement within the do while loop:

lngDefendantID = rs.Fields("DefendantID")

Here is the revised code:

' Place note into the taDEFENDANTSNOTE table a note detailing that the
letter was printed

strSql = "Select DefendantID from taDefendants " & _
"where BatchID= " & lngBatchID

Set rs = db.OpenRecordset(strSql)


Debug.Print lngDefendantID

If Not rs.BOF And Not rs.EOF Then
rs.MoveLast
rs.MoveFirst

'loop thru the recordset
Do While Not rs.EOF

lngDefendantID = rs.Fields("DefendantID")

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " & _
"values (" & lngDefendantID & ", '" & strDefNote & "')"
db.Execute strSql, dbFailOnError

rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing


strSql = "Update taPropertyLiens set BatchID= " & lngBatchID & " Where
BatchID is Null;"
db.Execute strSql, dbFailOnError
lngKtSubtotal2 = db.RecordsAffected

lngKt = lngKtSubtotal1 + lngKtSubtotal2

 

[Steve Sanford]

Hi Ray,

Well, ..... I *did* say it was untested....

I am working nights, uh, I was cutting and pasting,... lets see - what other
excuses can I use... oh - the dog ate my notes...

You are right - the DefendantID needs to be referenced.

You could also use:

strSql = "Insert into taDefendantsNotes (DefendantID, DefNote) " & _
"values (" & rs.Fields("DefendantID") & ", '" & strDefNote &
"')"

db.Execute strSql, dbFailOnError


This is what I was planning on posting, but I guess I copied one of the
other pieces of code I was playing with.

Glad you were agle to get it to work.

 

[Ray Todd Jr]

Steve you were a great help and it was appreciated!