Do...Loop Problem - Inserting same record

**puppydogbuddy** · Oct 1 '07, 01:52 PM

Originally posted by 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:

[code=text]
Private Sub ScheduleParts_C lick()

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.OpenRecordse t("QueueInfo" )

ScheduleParts.M oveFirst
Do Until ScheduleParts.E OF
ActTime = [S1] / [ProdEff]
RunHours = ActTime * [Qty] + [SetupTime]
EndTime = [StartTime] + RunHours
mySQL = "INSERT INTO Schedule (FGPartNum,GBPa rtNum,Qty,Day,D ia,Length,Setup ,CCYTime,ActTim e,RunHours,Star tTime,EndTime) " _
& " VALUES(PartNum, GB1,Qty,bucketD ay,Dia,Length,S etupTime,S1," _
& ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
DoCmd.RunSQL mySQL
ScheduleParts.M oveNext
Loop
ScheduleParts.C lose
db.Close
Set ScheduleParts = Nothing
Set db = Nothing

End Sub
[/code]

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,bucketD ay,Dia,Length,S etupTime,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.M oveFirst
to:
If Not (.EOF Or .BOF) Then
ScheduleParts.M oveFirst
End If

**kjworm** · Oct 1 '07, 02:48 PM

Originally posted by puppydogbuddy

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,bucketD ay,Dia,Length,S etupTime,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.M oveFirst
to:
If Not (.EOF Or .BOF) Then
ScheduleParts.M oveFirst
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?

Code:

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** · Oct 1 '07, 04:01 PM

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:

Code:

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** · Oct 1 '07, 05:25 PM

ps: just noticed this

If Not (ScheduleParts. EOF Or ScheduleParts.B OF) Then
ScheduleParts.M oveNext
End If

change to:

Code:

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

**FishVal** · Oct 1 '07, 05:56 PM

Hi,there.

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

Code:

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** · Oct 1 '07, 06:57 PM

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.E dit:

Code:

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** · Oct 1 '07, 07:32 PM

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** · Oct 1 '07, 08:04 PM

Originally posted by 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]?

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** · Oct 1 '07, 08:44 PM

Originally posted by kjworm

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** · Oct 1 '07, 08:58 PM

Originally posted by FishVal

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:

Code:

  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** · Oct 1 '07, 09:28 PM

Originally posted by kjworm

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:

Code:

  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

Code:

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** · Oct 2 '07, 02:53 PM

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:

Code:

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** · Oct 2 '07, 03:04 PM

Apparently, recordset is read only.

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

To:
Set ScheduleParts = db.OpenRecordse t("QueueInfo",d bOpenDynaset)

Do the same for your CheckQueue recordset

**puppydogbuddy** · Oct 2 '07, 03:13 PM

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

Do...Loop Problem - Inserting same record

Do...Loop Problem - Inserting same record

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment