Do...Loop Problem - Inserting same record

[kjworm]

Greetings everyone,

I am working on a scheduling tool and have a temp table that I am attempting to loop through to pull the necessary info out of before deleting.

I am using Access '97 on Windows XP.

Here is my code:

Private Sub ScheduleParts_Click()
 
Dim ScheduleParts As Recordset
Dim mySQL As String
Dim ActTime As Single
Dim RunHours As Single
Dim EndTime As Date
Dim db As Database
 
Set db = CurrentDb()
Set ScheduleParts = db.OpenRecordset("QueueInfo")
 
ScheduleParts.MoveFirst
Do Until ScheduleParts.EOF
    ActTime = [S1] / [ProdEff]
    RunHours = ActTime * [Qty] + [SetupTime]
    EndTime = [StartTime] + RunHours
    mySQL = "INSERT INTO Schedule (FGPartNum,GBPartNum,Qty,Day,Dia,Length,Setup,CCYTime,ActTime,RunHours,StartTime,EndTime) " _
            & " VALUES(PartNum,GB1,Qty,bucketDay,Dia,Length,SetupTime,S1," _
            & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
    DoCmd.RunSQL mySQL
    ScheduleParts.MoveNext
Loop
ScheduleParts.Close
db.Close
Set ScheduleParts = Nothing
Set db = Nothing
 
End Sub
 

In testing, I have 9 records in my temp table. My code adds the first record from the temp table 9 times. My recordset "QueueInfo" is a saved query in the database. I did try to compare the records from the temp table to existing records in my destination table to prevent duplicates, but ran into a number of other issues.

I've been wrestling with this one for a few days now and haven't been able to figure it out. Any help you could provide would be appreciated.

[puppydogbuddy]

Greetings everyone,

I am working on a scheduling tool and have a temp table that I am attempting to loop through to pull the necessary info out of before deleting.

I am using Access '97 on Windows XP.

Here is my code:

Private Sub ScheduleParts_Click()
 
Dim ScheduleParts As Recordset
Dim mySQL As String
Dim ActTime As Single
Dim RunHours As Single
Dim EndTime As Date
Dim db As Database
 
Set db = CurrentDb()
Set ScheduleParts = db.OpenRecordset("QueueInfo")
 
ScheduleParts.MoveFirst
Do Until ScheduleParts.EOF
    ActTime = [S1] / [ProdEff]
    RunHours = ActTime * [Qty] + [SetupTime]
    EndTime = [StartTime] + RunHours
    mySQL = "INSERT INTO Schedule (FGPartNum,GBPartNum,Qty,Day,Dia,Length,Setup,CCYTime,ActTime,RunHours,StartTime,EndTime) " _
            & " VALUES(PartNum,GB1,Qty,bucketDay,Dia,Length,SetupTime,S1," _
            & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
    DoCmd.RunSQL mySQL
    ScheduleParts.MoveNext
Loop
ScheduleParts.Close
db.Close
Set ScheduleParts = Nothing
Set db = Nothing
 
End Sub
 

In testing, I have 9 records in my temp table. My code adds the first record from the temp table 9 times. My recordset "QueueInfo" is a saved query in the database. I did try to compare the records from the temp table to existing records in my destination table to prevent duplicates, but ran into a number of other issues.

I've been wrestling with this one for a few days now and haven't been able to figure it out. Any help you could provide would be appreciated.

1. Try eliminating these lines. You are inserting the values in the fields for all records; you don't use a values statement:
& " VALUES(PartNum,GB1,Qty,bucketDay,Dia,Length,SetupT ime,S1," _
& ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"

2. Change this: Dim ScheduleParts As Recordset
to: Dim ScheduleParts As DAO.Recordset

and Change: Dim db As Database
toL Dim db As DAO.Database

3. Change this: ScheduleParts.MoveFirst
to:
If Not (.EOF Or .BOF) Then
ScheduleParts.MoveFirst
End If

[kjworm]

1. Try eliminating these lines. You are inserting the values in the fields for all records; you don't use a values statement:
& " VALUES(PartNum,GB1,Qty,bucketDay,Dia,Length,SetupT ime,S1," _
& ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"

2. Change this: Dim ScheduleParts As Recordset
to: Dim ScheduleParts As DAO.Recordset

and Change: Dim db As Database
toL Dim db As DAO.Database

3. Change this: ScheduleParts.MoveFirst
to:
If Not (.EOF Or .BOF) Then
ScheduleParts.MoveFirst
End If

Thank you for your reply. I have made the changes and am still not getting the loop I need.

By removing the "values" portion of the SQL statement, do I need to replace it with a "Insert Into...Select...From"? I have commented out my SQL and have a MsgBox for the part number. It gives me the first record 9 times still...Any thoughts?

Dim ScheduleParts As DAO.Recordset
Dim mySQL As String
Dim ActTime As Single
Dim RunHours As Single
Dim EndTime As Date
Dim db As DAO.Database
 
Set db = CurrentDb()
Set ScheduleParts = db.OpenRecordset("QueueInfo")
 
If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
ScheduleParts.MoveNext
End If
Do Until ScheduleParts.EOF
    ActTime = [S1] / [ProdEff]
    RunHours = ActTime * [Qty] + [SetupTime]
    EndTime = [StartTime] + RunHours
    'mySQL = "INSERT INTO Schedule (FGPartNum,GBPartNum,Qty,Day,Dia,Length,Setup,CCYTime,ActTime,RunHours,StartTime,EndTime)"
    '& "(PartNum, GB1, Qty, bucketDay, Dia ,Length, " & SetupTime & "," & S1 & "," & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
    'DoCmd.RunSQL mySQL
MsgBox PartNum
    ScheduleParts.MoveNext
Loop
 

Thanks.

[puppydogbuddy]

oops! I wasn't thinking. You've combined a recordset with an sql statement. The sql statement is not part of the recordset loop..it will execute separately. The true recordset data needs .Edit and .Update commands.

Try the revised code below:

Dim ScheduleParts As DAO.Recordset
Dim mySQL As String
Dim ActTime As Single
Dim RunHours As Single
Dim EndTime As Date
Dim db As DAO.Database
 
Set db = CurrentDb()
Set ScheduleParts = db.OpenRecordset("QueueInfo")
 
If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
ScheduleParts.MoveNext
End If
Do Until ScheduleParts.EOF
    ScheduleParts.Edit
        ActTime = [S1] / [ProdEff]
        RunHours = ActTime * [Qty] + [SetupTime]
        EndTime = [StartTime] + RunHours
    ScheduleParts.Update
    ScheduleParts.MoveNext
Loop
 
mySQL = "INSERT INTO Schedule (FGPartNum,GBPartNum,Qty,Day,Dia,Length,Setup,CCYT  ime,ActTime,RunHours,StartTime,EndTime)"
    '& "(PartNum, GB1, Qty, bucketDay, Dia ,Length, " & SetupTime & "," & S1 & "," & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
    'DoCmd.RunSQL mySQL
MsgBox PartNum
 
CurrentDb.Execute mySQL, dbFailOnError
 ScheduleParts.Close
db.Close
Set ScheduleParts = Nothing
Set db = Nothing
 
End Sub

[puppydogbuddy]

ps: just noticed this

If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
ScheduleParts.MoveNext
End If


change to:

If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
     ScheduleParts.MoveFirst
Else
     Exit Sub
End If

[FishVal]

Hi,there.

Is this what you've mentioned from the very beginning?

Private Sub ScheduleParts_Click()
 
    Dim ScheduleParts As DAO.Recordset
    Dim mySQL As String
    Dim ActTime As Single
    Dim RunHours As Single
    Dim EndTime As Date
    Dim db As DAO.Database
 
    Set db = CurrentDb()
    Set ScheduleParts = db.OpenRecordset("QueueInfo")
 
    With ScheduleParts
 
        .MoveFirst
 
        Do Until .EOF
            ActTime = ![S1] / ![ProdEff]
            RunHours = ActTime * ![Qty] + ![SetupTime]
            EndTime = ![StartTime] + RunHours
            mySQL = "INSERT INTO Schedule " & _
                        "(FGPartNum, GBPartNum, Qty, Day, Dia, Length, " & _
                        "Setup, CCYTime, ActTime, RunHours, " & _
                        "StartTime, EndTime) " & _
                    "VALUES (" & _
                        ![PartNum] & "," & _
                        ![GB1] & "," & _
                        ![Qty] & "," & _
                        ![bucketDay] & "," & _
                        ![Dia] & "," & _
                        ![Length] & "," & _
                        ![SetupTime] & "," & _
                        ![S1] & "," & _
                        ActTime & "," & _
                        RunHours & ",#" & _
                        ![StartTime] & "#,#" & _
                        EndTime & "#);"
            DoCmd.RunSQL mySQL
            .MoveNext
        Loop
 
        .Close
 
    End With
 
    db.Close
 
    Set ScheduleParts = Nothing
    Set db = Nothing
 
End Sub
 

[kjworm]

FishVal - thank you for your comments. Unfortunately this results in the same single record being input 9 times.

puppydogbuddy - with your latest post, I'm getting runtime errors 3027, 3265, etc, depending on various methods I've tried to alter. This gives me a 3027 error with the debug pointing at the ScheduleParts.Edit:

Set ScheduleParts = db.OpenRecordset("QueueInfo", dbOpenDynaset, 0, dbOptimistic)
 
If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
    ScheduleParts.MoveFirst
Else
    Exit Sub
End If
Do Until ScheduleParts.EOF
    ScheduleParts.Edit
        ScheduleParts![ActTime] = S1 / [ProdEff]
        ScheduleParts![RunHours] = ScheduleParts![ActTime] * Qty + SetupTime
        ScheduleParts![EndTime] = [StartTime] + RunHours
    ScheduleParts.Update
    ScheduleParts.MoveNext
Loop
 

In the Insert statement, I am using values from the form that initiates the click event, recordset data, and the results of calculations. Is this possibly what is giving me problems?

[FishVal]

Clarify plz what actually this code is supposed to do.
Fom the above posts I can hardly guess whether you add [QueueInfo] records row-by-row to [Schedule] or add records row-by-row to [Schedule] and then add the whole dataset to [Schedule]?

[kjworm]

Clarify plz what actually this code is supposed to do.
Fom the above posts I can hardly guess whether you add [QueueInfo] records row-by-row to [Schedule] or add records row-by-row to [Schedule] and then add the whole dataset to [Schedule]?

I am attempting to take row by row values from the recordset "QueueInfo" and perform a couple of calculations and then add that data and the recordset data to the table "Schedule" as a unique record.

Basically, the user will be able to add parts to a queue and then add them to a schedule. They select the parts into the queue which is a temporary tabledef and the query "QueueInfo" pulls master list data from a table based on the selections in the queue. Then a calculation for total run time is made based on the masterlist data and the quantity to be made (which is queue data). I am trying to add all of this information to a "Schedule" table which will retain the history by part numbers made and quantity, etc. This is rather hard to explain! Does this answer your question?

[FishVal]

I am attempting to take row by row values from the recordset "QueueInfo" and perform a couple of calculations and then add that data and the recordset data to the table "Schedule" as a unique record.

Basically, the user will be able to add parts to a queue and then add them to a schedule. They select the parts into the queue which is a temporary tabledef and the query "QueueInfo" pulls master list data from a table based on the selections in the queue. Then a calculation for total run time is made based on the masterlist data and the quantity to be made (which is queue data). I am trying to add all of this information to a "Schedule" table which will retain the history by part numbers made and quantity, etc. This is rather hard to explain! Does this answer your question?

More or less this makes sense.
Now its time for silly question:
What prevent you to make all calculations in query? I mean each record calculation doesn't context dependant. You use [QueueInfo] fields and form controls' values which don't change in the process off record addition and could be easily retrieved via Forms collection.

[kjworm]

More or less this makes sense.
Now its time for silly question:
What prevent you to make all calculations in query? I mean each record calculation doesn't context dependant. You use [QueueInfo] fields and form controls' values which don't change in the process off record addition and could be easily retrieved via Forms collection.

I've considered that and don't have a problem doing that. I was looking at that just now. I would still need to get the "queue" records into the "schedule" table and do not want the user to have to add them one at a time. I'm hoping for the flexibility to add a number of records, check that they are correct, delete any that shouldn't be in the queue and then hit the go button to run the schedule.

Anyhow, is there a reference or other setting that needs to be set for a do..loop to work? I have this code only and it is still only showing me the first record 9 times:

  mySQL = ScheduleParts!PartNum 
          'this is a string
 
        With ScheduleParts
 
        .MoveFirst
 
           Do Until .EOF
              MsgBox mySQL
              .MoveNext
           Loop
              .Close
        End With
 

This is really beginning to become frustrating as this is fairly simple code and should work...

Thanks for your help!

[FishVal]

I've considered that and don't have a problem doing that. I was looking at that just now. I would still need to get the "queue" records into the "schedule" table and do not want the user to have to add them one at a time. I'm hoping for the flexibility to add a number of records, check that they are correct, delete any that shouldn't be in the queue and then hit the go button to run the schedule.

You are planning to require row-by-row confirmation from user?

Anyhow, is there a reference or other setting that needs to be set for a do..loop to work? I have this code only and it is still only showing me the first record 9 times:

  mySQL = ScheduleParts!PartNum 
          'this is a string
 
        With ScheduleParts
 
        .MoveFirst
 
           Do Until .EOF
              MsgBox mySQL
              .MoveNext
           Loop
              .Close
        End With
 

This is really beginning to become frustrating as this is fairly simple code and should work...

Thanks for your help!

Is that some kind of joke? :) You set the value of mySQL variable once before the loop. Is there some more code?

Anyway you should revise the syntax of you SQL expression

mySQL = "INSERT INTO Schedule (FGPartNum, GBPartNum, Qty, Day, Dia, Length, Setup, CCYTime, ActTime, RunHours, StartTime, EndTime) " _
            & " VALUES (PartNum, GB1, Qty, bucketDay, Dia, Length, SetupTime, S1," _
            & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
 

"PartNum, GB1, Qty, bucketDay, Dia, Length, SetupTime, S1" - string constant

[kjworm]

Here is my latest attempt that still results in the same first record in the recordset being inserted 9 times which is the recordcount of the recordset:

Private Sub ScheduleParts_Click()
 
    Dim ScheduleParts As DAO.Recordset
    Dim CheckQueue As DAO.Recordset
    Dim mySQL As String
    Dim ActTime As Single
    Dim RunHours As Single
    Dim EndTime As Date
    Dim db As DAO.Database
    Dim tblfldNames As String
    Dim fldValues As String
    Dim queueinfo As String
    Dim tblSchedule As String
 
    Set db = CurrentDb()
    Set ScheduleParts = db.OpenRecordset("QueueInfo")
    tblfldNames = "(FGPartNum, GBPartNum, Qty, Day, Dia, Length, Setup,     CCYTime, ActTime, RunHours, StartTime, EndTime)"
    fldValues = "PartNum,GB1,Qty,bucketDay,Dia,Length,SetupTime,S1"
 
ScheduleParts.MoveFirst
Do Until ScheduleParts.EOF
        Set CheckQueue = db.OpenRecordset("CheckQueue")
            CheckQueue.MoveFirst
                Do Until CheckQueue.EOF
                    queueinfo = CheckQueue!FGPartNum
                    tblSchedule = ScheduleParts!PartNum
                       If tblSchedule = queueinfo Then
                         ScheduleParts.MoveNext
                       Else
                         ActTime = S1 / [ProdEff]
                         RunHours = ActTime * Qty + SetupTime
                         EndTime = [StartTime] + RunHours
                         mySQL = "INSERT INTO Schedule " & tblfldNames _
                            & "VALUES (" & fldValues & "," _
                         & ActTime & "," & RunHours & ",#" & [StartTime] & "#,#" & EndTime & "#);"
 
                         DoCmd.RunSQL mySQL
                         ScheduleParts.MoveNext
                       End If
                       CheckQueue.MoveNext
                 Loop
                CheckQueue.Close
        Loop
 
    ScheduleParts.Close
    db.Close
    Set ScheduleParts = Nothing
    Set CheckQueue = Nothing
    Set db = Nothing
 
End Sub
 

Does anyone see anything wrong in this code? I don't really know what to ask, other than how to make it loop through the table that it is inserting values into and skipping that record in the source recordset if it already exists.

[puppydogbuddy]

Apparently, recordset is read only.

try changing:
Set ScheduleParts = db.OpenRecordset("QueueInfo")

To:
Set ScheduleParts = db.OpenRecordset("QueueInfo",dbOpenDynaset)


Do the same for your CheckQueue recordset

[puppydogbuddy]

Also, like I mentioned previously you need to invoke the edit or addNew, and update methods of the recordset within the loop.

[FishVal]

Well.

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

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 "ScheduleParts" 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 "ScheduleParts" 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!FGPartNum
tblSchedule = ScheduleParts!PartNum
If tblSchedule = queueinfo Then

try this:
queueinfo = CStr(CheckQueue!FGPartNum)
tblSchedule = CStr(ScheduleParts!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!FGPartNum
tblSchedule = ScheduleParts!PartNum
If tblSchedule = queueinfo Then

try this:
queueinfo = CStr(CheckQueue!FGPartNum)
tblSchedule = CStr(ScheduleParts!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.