Do...Loop Problem - Inserting same record

[FishVal]

Well.

........................
mySQL = "INSERT INTO Schedule " & tblfldNames _
                            & "VALUES (" & fldValues & "," _
                         & ActTime & "," & RunHours & ",#" & [StartTime] & "#,#" & EndTime & "#);"
                         
Debug.Print mySQL                '[B]Add this line here    [/B]                     
DoCmd.RunSQL mySQL         '[B]Toggle breakpoint here[/B]
........................

When execution stop on breakpoint, copy content of mySQL from VBA Immediate window, paste it to Query builder and try to execute.

[kjworm]

I will try to work with these latest suggestions, thank you.

I have a few questions though. If I need to use the edit..update functionality, why is the first record able to be inserted correctly several times? I am not trying to update a recordset, I am trying to update a table with data from the recordset.

Is there a way to delete a record from the recordset "SchedulePa rts" after it is inserted into the table so that it cannot be inserted again?

[FishVal]

I will try to work with these latest suggestions, thank you.

I have a few questions though. If I need to use the edit..update functionality, why is the first record able to be inserted correctly several times? I am not trying to update a recordset, I am trying to update a table with data from the recordset.

Is there a way to delete a record from the recordset "SchedulePa rts" after it is inserted into the table so that it cannot be inserted again?

You don't need it. :)

[puppydogbuddy]

Based on info you provided, I thought you were updating recordset, and then inserting updated recordset into table. If that no longer applies then scratch.

One of your problems might be here:

queueinfo = CheckQueue!FGPa rtNum
tblSchedule = ScheduleParts!P artNum
If tblSchedule = queueinfo Then

try this:
queueinfo = CStr(CheckQueue !FGPartNum)
tblSchedule = CStr(SchedulePa rts!PartNum)

[kjworm]

Based on info you provided, I thought you were updating recordset, and then inserting updated recordset into table. If that no longer applies then scratch.

One of your problems might be here:

queueinfo = CheckQueue!FGPa rtNum
tblSchedule = ScheduleParts!P artNum
If tblSchedule = queueinfo Then

try this:
queueinfo = CStr(CheckQueue !FGPartNum)
tblSchedule = CStr(SchedulePa rts!PartNum)

OK, I figured out that whichever record that is highlighted/selected in the form is the record that is being inserted the number of times of existing records in the recordset. I have the recordset "QueueInfo" displayed as a continuous subform where the user will click to run the code displayed in this post. This is resulting in the top record having the black arrow selector default that Access uses and for whatever reason, I believe, is causing the loop to remain on this record. I've tried changing the form properties around but can't find anything that will prevent a record in the record set from being automatically selected. Any suggestions for this?

[kjworm]

Ok everyone, please consider this thread closed. I have figured this out...somehow. I guess I just needed to have all the right variable definitions inside of the loop. It became hard to see the forest from the trees after staring at this for so long...

Here is my final code if interested...

Set db = CurrentDb()
    Set ScheduleParts = db.OpenRecordset("QueueInfo")
    DoCmd.SetWarnings False
ScheduleParts.MoveFirst
Do Until ScheduleParts.EOF
        Set CheckQueue = db.OpenRecordset("CheckQueue")
            CheckQueue.MoveLast
                Do Until CheckQueue.EOF
                    queueinfo = CStr(CheckQueue!FGPartNum)
                    tblSchedule = CStr(ScheduleParts!PartNum)
                       If tblSchedule = queueinfo Then
                         ScheduleParts.MoveNext
                       Else
                         ActTime = S1 / [ProdEff]
                         RunHours = ActTime * Qty + SetupTime
                         EndTime = [StartTime] + RunHours
                         tblfldNames = "(FGPartNum, GBPartNum, Qty, Day, Dia, Length, Setup, CCYTime, ActTime, RunHours, StartTime, EndTime)"
                         fldValues = "'" & ScheduleParts!PartNum & "','" & ScheduleParts!GB1 & "'," & ScheduleParts!Qty & "," & ScheduleParts!bucketDay & "," & ScheduleParts!Dia & "," & ScheduleParts!Length & "," & ScheduleParts!SetupTime & "," & ScheduleParts!S1 & ", "
                         mySQL = "INSERT INTO Schedule " & tblfldNames _
                            & " VALUES (" & fldValues _
                            & ActTime & "," & RunHours & ",#" & [StartTime] & "#,#" & EndTime & "#);"
                          
                         DoCmd.RunSQL mySQL
                         ScheduleParts.MoveNext
                         CheckQueue.MoveNext
                       End If
                Loop
                CheckQueue.Close
        Loop
      
    ScheduleParts.Close
    db.Close
    Set ScheduleParts = Nothing
    Set CheckQueue = Nothing
    Set db = Nothing
    DoCmd.SetWarnings True

Thank you puppydogbuddy and FishVal!

[FishVal]

Glad you've figured it out yourself.
You are welcome.

Best regards,
Fish.

[puppydogbuddy]

You are welcome. Glad you got it resolved.