Hi gang,

I have an interesting issue.  Here is some code i use to copy existing
records for one person to new records for a second person:

Private Sub cmdTaskCopy_Click()
On Error GoTo Err_cmdTaskCopy_Click

Dim eEmp1, eEmp2, tTask1, tTask2, sSQL1, sSQL2, mMesg, rChar, rRTime As
Variant
Dim dDriver As Boolean
Dim rs As ADODB.Recordset

eEmp1 = DLookup("[workerid]", "[worker]", "workername = '" & cmbEmp1.Value &
"'")
eEmp2 = DLookup("[workerid]", "[worker]", "workername = '" & cmbEmp2.Value &
"'")
tTask1 = txtTask1.Value

sSQL1 = "select * from TaskAll where workerid = '" & eEmp1 & "' and taskID
like '" & tTask1 & "%';"

Set rs = New ADODB.Recordset
rs.Open sSQL1, CurrentProject.Connection, adOpenStatic

rs.MoveFirst
Do While Not rs.EOF

   'Change the task order
   If IsNull(rs(0)) Or (rs(0) = "") Then
       mMesg = MsgBox("Query returned no records.", vbCritical)
       End
   End If
   rChar = Right(rs(1), 1)
   If (rChar >= "a") And (rChar <= "z") Then
       tTask2 = txtTask2.Value & rChar
   Else
       tTask2 = txtTask2.Value
   End If

   'Assuming the team member is already entered, get the existing driver
flag and change it.
   dDriver = rs(15)
   If dDriver = False Then
       dDriver = True
   Else
       dDriver = False
   End If
   'Check for blank running tine field.
   If IsNull(rs(14)) Then
       rRTime = Date
   Else
       rRTime = rs(14)
   End If
   
   sSQL2 = "insert into TaskAll values ('" & eEmp2 & "','" & tTask2 & "','"
& rs(2) & "','" & _
     rs(3) & "','" & rs(4) & "',#" & Format(rs(5), "mm\/dd\/yyyy") & "#,#"
& _
     Format(rs(6), "mm\/dd\/yyyy") & "#,#" & FormatDateTime(rs(7),
vbLongTime) & "#,#" & _
     FormatDateTime(rs(8), vbLongTime) & "#,'" & rs(9) & "','" & rs(10) &
"'," & _
     rs(11) & ",'" & rs(12) & "'," & rs(13) & ",#" & FormatDateTime(rRTime,
vbLongTime) & "#," & dDriver & ",'" & _
     rs(16) & "',#" & FormatDateTime(rs(17), vbGeneralDate) & "#,'" &
rs(18) & "');"
   DoCmd.SetWarnings False
   DoCmd.RunSQL sSQL2
   DoCmd.SetWarnings True
   rs.MoveNext
Loop
rs.Close

Exit_cmdTaskCopy_Click:
   Exit Sub

Err_cmdTaskCopy_Click:
   Resume Next
   
End Sub

I have instances where some records are successfully written to the
database, and others are not.  I've tracked the issue to be two fields (seen
here in the insert statement as rs(10) and rs(11) ) that are the FK to other
tables.  If either of these values are null, the insert fails.  I've checked
the table, TaskAll, to which this statement writes; both fields on this table
are set to allow zero length strings are neither field require an entry.  
I've checked that many times.   It seems the issue lies with trying to write
to a table with a FK value that is null.  The message I get indicates I'm
receiving a key violation, and the record being entered does not violate the
PK setup on the table.

I suppose as a work-around, I could use Nz() to populate the field with a
dummy value that matches one in each table to which the field is a FK/PK, but
I'd really rather know why this is happening.

I have a form that can successfully write to the database with either or
both of these values blank, using the standard
DoCmd.GoToRecord , , acNewRec
and it's been explained to me previously that this line just opens up a new
record at the bottom of the table, just as opening the table, but somewhere
in this process the record has to be saved to the database, with the
requisite key and validation checks.  Is there a difference in the way the
system treats adding a new record via the standard DoCmd.GoToRecord , ,
acNewRec versus using an SQL insert command?

Thanks very much in advance for any help.  I'm looking forward to some
edification here. :)

Paul