INSERT INTO Not working correctly


B

Ben

I have a command button that does a "Copy" but more of a INSERT INTO command
on 3 subtables. If I comment out the first 2 INSERT INTO blocks then the 3rd
INSERT INTO command works. If I uncomment out the first 2 INSERT INTO
commands then the 3rd doesn't work. What am I doing wrong? Here's my code.

Private Sub Command63_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim strSql1 As String 'SQL Statement 2.
Dim strSqlA As String

Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectName = Me.ProjectName
![DEA/SRALeads] = Me.[DEA/SRALeads]
!PhaseComplDateReqPlan = Me.PhaseComplDateReqPlan
!PhaseComplDateDesign = Me.PhaseComplDateDesign
!PhaseComplDateDevelopment = Me.PhaseComplDateDevelopment
!PhaseComplDatePilot = Me.PhaseComplDatePilot
!PhaseComplDateTransition = Me.PhaseComplDateTransition
!ReqPlanStatus = Me.ReqPlanStatus
!DesignStatus = Me.DesignStatus
!DevelopmentStatus = Me.DevelopmentStatus
!PilotStatus = Me.PilotStatus
!TransitionStatus = Me.TransitionStatus
!Other = Me.Other

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !WebFarmID

MsgBox "LAST WEEK'S WAR DATA HAS BEEN COPIED"

'Duplicate the related records: append query.
If Me.[frmWebFarmNewAIs].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblWebFarmNewAIs] ( WebFarmID,
ActionItem, Owner) " & _
"SELECT " & lngID & " As NewID, ActionItem, Owner " & _
"FROM [tblWebFarmNewAIs] WHERE WebFarmID = " &
Me.WebFarmID & ";"
DBEngine(0)(0).Execute strSql

If Me.[frmWebFarmOverdueAIs].Form.RecordsetClone.RecordCount > 0
Then
strSql1 = "INSERT INTO [tblWebFarmOverdueAIs] (WebFarmID,
ActionItemOverdue, Owner) " & _
"SELECT " & lngID & " As NewID, ActionItemOverdue, Owner
" & _
"FROM [tblWebFarmOverdueAIs] WHERE WebFarmID = " &
Me.WebFarmID & ";"

DBEngine(0)(0).Execute strSql1

If Me.[frmWebFarmDeliverables].Form.RecordsetClone.RecordCount >
0 Then
strSqlA = "INSERT INTO [tblWebFarmDeliverables] (WebFarmID,
Deliverable, Completed) " & _
"SELECT " & lngID & " As NewID, Deliverable, Completed "
& _
"FROM [tblWebFarmDeliverables] WHERE WebFarmID = " &
Me.WebFarmID & ";"

DBEngine(0)(0).Execute strSqlA
Else
MsgBox "Main record duplicated, but there were no related
records."
End If
End If
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"command63_Click"
Resume Exit_Handler
End Sub
 
Ad

Advertisements

L

Lord Kelvan

what is actually not working or how is it not working it is brining up
an error or is it just not executing that code

if it is nto executing that code try adding a break point to the code
and press f8 to go through line by line to see if you can note the
problem of where it is skilling code or where the error is comming
from

you can hover your mouse over variables or highlight a variable to add
it to watch to see if the value in the valuable is what you want cause
that could cause an error

Regards
Kelvan
 

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

Top