469,902 Members | 1,824 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,902 developers. It's quick & easy.

Do...Loop Problem - Inserting same record

26
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleParts_Click()
  2.  
  3. Dim ScheduleParts As Recordset
  4. Dim mySQL As String
  5. Dim ActTime As Single
  6. Dim RunHours As Single
  7. Dim EndTime As Date
  8. Dim db As Database
  9.  
  10. Set db = CurrentDb()
  11. Set ScheduleParts = db.OpenRecordset("QueueInfo")
  12.  
  13. ScheduleParts.MoveFirst
  14. Do Until ScheduleParts.EOF
  15.     ActTime = [S1] / [ProdEff]
  16.     RunHours = ActTime * [Qty] + [SetupTime]
  17.     EndTime = [StartTime] + RunHours
  18.     mySQL = "INSERT INTO Schedule (FGPartNum,GBPartNum,Qty,Day,Dia,Length,Setup,CCYTime,ActTime,RunHours,StartTime,EndTime) " _
  19.             & " VALUES(PartNum,GB1,Qty,bucketDay,Dia,Length,SetupTime,S1," _
  20.             & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
  21.     DoCmd.RunSQL mySQL
  22.     ScheduleParts.MoveNext
  23. Loop
  24. ScheduleParts.Close
  25. db.Close
  26. Set ScheduleParts = Nothing
  27. Set db = Nothing
  28.  
  29. End Sub
  30.  
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.
Oct 1 '07 #1
22 1792
puppydogbuddy
1,923 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleParts_Click()
  2.  
  3. Dim ScheduleParts As Recordset
  4. Dim mySQL As String
  5. Dim ActTime As Single
  6. Dim RunHours As Single
  7. Dim EndTime As Date
  8. Dim db As Database
  9.  
  10. Set db = CurrentDb()
  11. Set ScheduleParts = db.OpenRecordset("QueueInfo")
  12.  
  13. ScheduleParts.MoveFirst
  14. Do Until ScheduleParts.EOF
  15.     ActTime = [S1] / [ProdEff]
  16.     RunHours = ActTime * [Qty] + [SetupTime]
  17.     EndTime = [StartTime] + RunHours
  18.     mySQL = "INSERT INTO Schedule (FGPartNum,GBPartNum,Qty,Day,Dia,Length,Setup,CCYTime,ActTime,RunHours,StartTime,EndTime) " _
  19.             & " VALUES(PartNum,GB1,Qty,bucketDay,Dia,Length,SetupTime,S1," _
  20.             & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
  21.     DoCmd.RunSQL mySQL
  22.     ScheduleParts.MoveNext
  23. Loop
  24. ScheduleParts.Close
  25. db.Close
  26. Set ScheduleParts = Nothing
  27. Set db = Nothing
  28.  
  29. End Sub
  30.  
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
Oct 1 '07 #2
kjworm
26
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?

Expand|Select|Wrap|Line Numbers
  1. Dim ScheduleParts As DAO.Recordset
  2. Dim mySQL As String
  3. Dim ActTime As Single
  4. Dim RunHours As Single
  5. Dim EndTime As Date
  6. Dim db As DAO.Database
  7.  
  8. Set db = CurrentDb()
  9. Set ScheduleParts = db.OpenRecordset("QueueInfo")
  10.  
  11. If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
  12. ScheduleParts.MoveNext
  13. End If
  14. Do Until ScheduleParts.EOF
  15.     ActTime = [S1] / [ProdEff]
  16.     RunHours = ActTime * [Qty] + [SetupTime]
  17.     EndTime = [StartTime] + RunHours
  18.     'mySQL = "INSERT INTO Schedule (FGPartNum,GBPartNum,Qty,Day,Dia,Length,Setup,CCYTime,ActTime,RunHours,StartTime,EndTime)"
  19.     '& "(PartNum, GB1, Qty, bucketDay, Dia ,Length, " & SetupTime & "," & S1 & "," & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
  20.     'DoCmd.RunSQL mySQL
  21. MsgBox PartNum
  22.     ScheduleParts.MoveNext
  23. Loop
  24.  
Thanks.
Oct 1 '07 #3
puppydogbuddy
1,923 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim ScheduleParts As DAO.Recordset
  2. Dim mySQL As String
  3. Dim ActTime As Single
  4. Dim RunHours As Single
  5. Dim EndTime As Date
  6. Dim db As DAO.Database
  7.  
  8. Set db = CurrentDb()
  9. Set ScheduleParts = db.OpenRecordset("QueueInfo")
  10.  
  11. If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
  12. ScheduleParts.MoveNext
  13. End If
  14. Do Until ScheduleParts.EOF
  15.     ScheduleParts.Edit
  16.         ActTime = [S1] / [ProdEff]
  17.         RunHours = ActTime * [Qty] + [SetupTime]
  18.         EndTime = [StartTime] + RunHours
  19.     ScheduleParts.Update
  20.     ScheduleParts.MoveNext
  21. Loop
  22.  
  23. mySQL = "INSERT INTO Schedule (FGPartNum,GBPartNum,Qty,Day,Dia,Length,Setup,CCYT  ime,ActTime,RunHours,StartTime,EndTime)"
  24.     '& "(PartNum, GB1, Qty, bucketDay, Dia ,Length, " & SetupTime & "," & S1 & "," & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
  25.     'DoCmd.RunSQL mySQL
  26. MsgBox PartNum
  27.  
  28. CurrentDb.Execute mySQL, dbFailOnError
  29.  ScheduleParts.Close
  30. db.Close
  31. Set ScheduleParts = Nothing
  32. Set db = Nothing
  33.  
  34. End Sub
Oct 1 '07 #4
puppydogbuddy
1,923 Expert 1GB
ps: just noticed this

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


change to:
Expand|Select|Wrap|Line Numbers
  1. If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
  2.      ScheduleParts.MoveFirst
  3. Else
  4.      Exit Sub
  5. End If
Oct 1 '07 #5
FishVal
2,653 Expert 2GB
Hi,there.

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleParts_Click()
  2.  
  3.     Dim ScheduleParts As DAO.Recordset
  4.     Dim mySQL As String
  5.     Dim ActTime As Single
  6.     Dim RunHours As Single
  7.     Dim EndTime As Date
  8.     Dim db As DAO.Database
  9.  
  10.     Set db = CurrentDb()
  11.     Set ScheduleParts = db.OpenRecordset("QueueInfo")
  12.  
  13.     With ScheduleParts
  14.  
  15.         .MoveFirst
  16.  
  17.         Do Until .EOF
  18.             ActTime = ![S1] / ![ProdEff]
  19.             RunHours = ActTime * ![Qty] + ![SetupTime]
  20.             EndTime = ![StartTime] + RunHours
  21.             mySQL = "INSERT INTO Schedule " & _
  22.                         "(FGPartNum, GBPartNum, Qty, Day, Dia, Length, " & _
  23.                         "Setup, CCYTime, ActTime, RunHours, " & _
  24.                         "StartTime, EndTime) " & _
  25.                     "VALUES (" & _
  26.                         ![PartNum] & "," & _
  27.                         ![GB1] & "," & _
  28.                         ![Qty] & "," & _
  29.                         ![bucketDay] & "," & _
  30.                         ![Dia] & "," & _
  31.                         ![Length] & "," & _
  32.                         ![SetupTime] & "," & _
  33.                         ![S1] & "," & _
  34.                         ActTime & "," & _
  35.                         RunHours & ",#" & _
  36.                         ![StartTime] & "#,#" & _
  37.                         EndTime & "#);"
  38.             DoCmd.RunSQL mySQL
  39.             .MoveNext
  40.         Loop
  41.  
  42.         .Close
  43.  
  44.     End With
  45.  
  46.     db.Close
  47.  
  48.     Set ScheduleParts = Nothing
  49.     Set db = Nothing
  50.  
  51. End Sub
  52.  
Oct 1 '07 #6
kjworm
26
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:

Expand|Select|Wrap|Line Numbers
  1. Set ScheduleParts = db.OpenRecordset("QueueInfo", dbOpenDynaset, 0, dbOptimistic)
  2.  
  3. If Not (ScheduleParts.EOF Or ScheduleParts.BOF) Then
  4.     ScheduleParts.MoveFirst
  5. Else
  6.     Exit Sub
  7. End If
  8. Do Until ScheduleParts.EOF
  9.     ScheduleParts.Edit
  10.         ScheduleParts![ActTime] = S1 / [ProdEff]
  11.         ScheduleParts![RunHours] = ScheduleParts![ActTime] * Qty + SetupTime
  12.         ScheduleParts![EndTime] = [StartTime] + RunHours
  13.     ScheduleParts.Update
  14.     ScheduleParts.MoveNext
  15. Loop
  16.  
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?
Oct 1 '07 #7
FishVal
2,653 Expert 2GB
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]?
Oct 1 '07 #8
kjworm
26
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?
Oct 1 '07 #9
FishVal
2,653 Expert 2GB
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.
Oct 1 '07 #10
kjworm
26
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:

Expand|Select|Wrap|Line Numbers
  1.   mySQL = ScheduleParts!PartNum 
  2.           'this is a string
  3.  
  4.         With ScheduleParts
  5.  
  6.         .MoveFirst
  7.  
  8.            Do Until .EOF
  9.               MsgBox mySQL
  10.               .MoveNext
  11.            Loop
  12.               .Close
  13.         End With
  14.  
This is really beginning to become frustrating as this is fairly simple code and should work...

Thanks for your help!
Oct 1 '07 #11
FishVal
2,653 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.   mySQL = ScheduleParts!PartNum 
  2.           'this is a string
  3.  
  4.         With ScheduleParts
  5.  
  6.         .MoveFirst
  7.  
  8.            Do Until .EOF
  9.               MsgBox mySQL
  10.               .MoveNext
  11.            Loop
  12.               .Close
  13.         End With
  14.  
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
Expand|Select|Wrap|Line Numbers
  1. mySQL = "INSERT INTO Schedule (FGPartNum, GBPartNum, Qty, Day, Dia, Length, Setup, CCYTime, ActTime, RunHours, StartTime, EndTime) " _
  2.             & " VALUES (PartNum, GB1, Qty, bucketDay, Dia, Length, SetupTime, S1," _
  3.             & ActTime & "," & RunHours & ",#" & StartTime & "#,#" & EndTime & "#);"
  4.  
"PartNum, GB1, Qty, bucketDay, Dia, Length, SetupTime, S1" - string constant
Oct 1 '07 #12
kjworm
26
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleParts_Click()
  2.  
  3.     Dim ScheduleParts As DAO.Recordset
  4.     Dim CheckQueue As DAO.Recordset
  5.     Dim mySQL As String
  6.     Dim ActTime As Single
  7.     Dim RunHours As Single
  8.     Dim EndTime As Date
  9.     Dim db As DAO.Database
  10.     Dim tblfldNames As String
  11.     Dim fldValues As String
  12.     Dim queueinfo As String
  13.     Dim tblSchedule As String
  14.  
  15.     Set db = CurrentDb()
  16.     Set ScheduleParts = db.OpenRecordset("QueueInfo")
  17.     tblfldNames = "(FGPartNum, GBPartNum, Qty, Day, Dia, Length, Setup,     CCYTime, ActTime, RunHours, StartTime, EndTime)"
  18.     fldValues = "PartNum,GB1,Qty,bucketDay,Dia,Length,SetupTime,S1"
  19.  
  20. ScheduleParts.MoveFirst
  21. Do Until ScheduleParts.EOF
  22.         Set CheckQueue = db.OpenRecordset("CheckQueue")
  23.             CheckQueue.MoveFirst
  24.                 Do Until CheckQueue.EOF
  25.                     queueinfo = CheckQueue!FGPartNum
  26.                     tblSchedule = ScheduleParts!PartNum
  27.                        If tblSchedule = queueinfo Then
  28.                          ScheduleParts.MoveNext
  29.                        Else
  30.                          ActTime = S1 / [ProdEff]
  31.                          RunHours = ActTime * Qty + SetupTime
  32.                          EndTime = [StartTime] + RunHours
  33.                          mySQL = "INSERT INTO Schedule " & tblfldNames _
  34.                             & "VALUES (" & fldValues & "," _
  35.                          & ActTime & "," & RunHours & ",#" & [StartTime] & "#,#" & EndTime & "#);"
  36.  
  37.                          DoCmd.RunSQL mySQL
  38.                          ScheduleParts.MoveNext
  39.                        End If
  40.                        CheckQueue.MoveNext
  41.                  Loop
  42.                 CheckQueue.Close
  43.         Loop
  44.  
  45.     ScheduleParts.Close
  46.     db.Close
  47.     Set ScheduleParts = Nothing
  48.     Set CheckQueue = Nothing
  49.     Set db = Nothing
  50.  
  51. End Sub
  52.  
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.
Oct 2 '07 #13
puppydogbuddy
1,923 Expert 1GB
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
Oct 2 '07 #14
puppydogbuddy
1,923 Expert 1GB
Also, like I mentioned previously you need to invoke the edit or addNew, and update methods of the recordset within the loop.
Oct 2 '07 #15
FishVal
2,653 Expert 2GB
Well.

Expand|Select|Wrap|Line Numbers
  1. ........................
  2. mySQL = "INSERT INTO Schedule " & tblfldNames _
  3.                             & "VALUES (" & fldValues & "," _
  4.                          & ActTime & "," & RunHours & ",#" & [StartTime] & "#,#" & EndTime & "#);"
  5.  
  6. Debug.Print mySQL                'Add this line here                         
  7. DoCmd.RunSQL mySQL         'Toggle breakpoint here
  8. ........................
  9.  
When execution stop on breakpoint, copy content of mySQL from VBA Immediate window, paste it to Query builder and try to execute.
Oct 2 '07 #16
kjworm
26
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?
Oct 2 '07 #17
FishVal
2,653 Expert 2GB
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. :)
Oct 2 '07 #18
puppydogbuddy
1,923 Expert 1GB
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)
Oct 2 '07 #19
kjworm
26
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?
Oct 2 '07 #20
kjworm
26
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...

Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb()
  2.     Set ScheduleParts = db.OpenRecordset("QueueInfo")
  3.     DoCmd.SetWarnings False
  4. ScheduleParts.MoveFirst
  5. Do Until ScheduleParts.EOF
  6.         Set CheckQueue = db.OpenRecordset("CheckQueue")
  7.             CheckQueue.MoveLast
  8.                 Do Until CheckQueue.EOF
  9.                     queueinfo = CStr(CheckQueue!FGPartNum)
  10.                     tblSchedule = CStr(ScheduleParts!PartNum)
  11.                        If tblSchedule = queueinfo Then
  12.                          ScheduleParts.MoveNext
  13.                        Else
  14.                          ActTime = S1 / [ProdEff]
  15.                          RunHours = ActTime * Qty + SetupTime
  16.                          EndTime = [StartTime] + RunHours
  17.                          tblfldNames = "(FGPartNum, GBPartNum, Qty, Day, Dia, Length, Setup, CCYTime, ActTime, RunHours, StartTime, EndTime)"
  18.                          fldValues = "'" & ScheduleParts!PartNum & "','" & ScheduleParts!GB1 & "'," & ScheduleParts!Qty & "," & ScheduleParts!bucketDay & "," & ScheduleParts!Dia & "," & ScheduleParts!Length & "," & ScheduleParts!SetupTime & "," & ScheduleParts!S1 & ", "
  19.                          mySQL = "INSERT INTO Schedule " & tblfldNames _
  20.                             & " VALUES (" & fldValues _
  21.                             & ActTime & "," & RunHours & ",#" & [StartTime] & "#,#" & EndTime & "#);"
  22.  
  23.                          DoCmd.RunSQL mySQL
  24.                          ScheduleParts.MoveNext
  25.                          CheckQueue.MoveNext
  26.                        End If
  27.                 Loop
  28.                 CheckQueue.Close
  29.         Loop
  30.  
  31.     ScheduleParts.Close
  32.     db.Close
  33.     Set ScheduleParts = Nothing
  34.     Set CheckQueue = Nothing
  35.     Set db = Nothing
  36.     DoCmd.SetWarnings True
  37.  
Thank you puppydogbuddy and FishVal!
Oct 2 '07 #21
FishVal
2,653 Expert 2GB
Glad you've figured it out yourself.
You are welcome.

Best regards,
Fish.
Oct 2 '07 #22
puppydogbuddy
1,923 Expert 1GB
You are welcome. Glad you got it resolved.
Oct 2 '07 #23

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Karzy | last post: by
reply views Thread by Sue Adams | last post: by
1 post views Thread by Søren Larsen | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.