Error inserting record

G

Guest

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
 
S

Steve Schapel

Paul,

Whereas you should be able to create a record in the table with a Null
in a Foreign Key field, you will probably not be allowed to do so with a
"" in the Foreign Key field, as there will not be a record in the
related table with a "" in its Primary Key field. It seems to me that
this is what you will be doing with the rs(10).

--
Steve Schapel, Microsoft Access MVP

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
Click to expand...
 
G

Guest

You know, Steve, when you put it like that, I'm embarrassed that I missed all
that conceptually. Of course, you're absolutely right.

Thanks for your help.


Steve Schapel said:
Paul,

Whereas you should be able to create a record in the table with a Null
in a Foreign Key field, you will probably not be allowed to do so with a
"" in the Foreign Key field, as there will not be a record in the
related table with a "" in its Primary Key field. It seems to me that
this is what you will be doing with the rs(10).

--
Steve Schapel, Microsoft Access MVP

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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

record count of table while looping through all tables 1
Drowning in Loops 1
EXPORT ACCESS TO SQL DATABASE RECORD BY RECORD... 1
Compile Error ?? 2
Multi-select listbox: de-selected record not being deleted 4
Exporting Access Data into Email Body 1
run-time error 3201 8
Multiple Record Sets Open - Updating "derived" fields 1

Top