Moving records to another table then delete (1 Viewer)

[R3GUL8OR]

OK guys, ive done some searching and saw some stuff on this, but everytime I try something I get a INVALID SQL error message. So Im hoping you guys can help out cause Im lost right now.

I have a 2 tables, Training and Schedule. Training contains all the employees training records. Schedule is meant to be a temp table where the supervisors can enter the training and then once completed, can check the COMPLETED checkbox and hit the LOAD TO TRAINING RECORD command button and it moves ONLY the files for that trainee (a combo box) with a completed checkmark to the training table.

Ive tried this code sooooooooo many ways and its giving me a headache. My original way didnt work at all (had a lot of Do..Loop and With Statements, and then someone posted something about Archiving which is the code Ive gone off of now). Any help you guys can off would be awesome!

Heres what I got:

Private Sub cmdLoad_Click()

On Error GoTo Err_Load_Record_Click

UploadHistory

Exit_Load_Record_Click:
Exit Sub

Err_Load_Record_Click:
MsgBox Err.Description
Resume Exit_Load_Record_Click

End Sub

Sub UploadHistory()

Dim DB As Database
Dim WS As Workspace 'Current workspace (for transaction).
Dim strSql As String 'Sql Code.
Dim strMsg As String 'MsgBox message.
Dim bInTrans As Boolean 'Flag that transaction is active.

Set DB = CurrentDb()
Set WS = DBEngine(0)
WS.BeginTrans
bInTrans = True
Set DB = WS(0)

On Error GoTo Err_UploadHistory

'Execute the add.
strSql = "INSERT INTO [Training] " _
& "(TaskNumber, Date, Hours, TrainerLast, TraineeLast, Qualified) " _
& "SELECT " & "Schedule.Task, Schedule.Date, Schedule.Hours, Schedule.Trainer, " _
& "Schedule.Trainee, Schedule.Qualified FROM [Schedule] " _
& "WHERE (((Schedule.Trainee) = " & Me.TraineeCombo & " AND (Schedule.Completed)= 1));"

DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

'Execute the delete.
strSql = "DELETE FROM [Schedule] WHERE Trainee = " & Me.TraineeCombo & " AND Completed = 1;"

DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

'Get user confirmation to commit the change.
strMsg = "Upload " & DB.RecordsAffected & " record(s) from " & Me.TraineeCombo & "?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
WS.CommitTrans
bInTrans = False
End If

Exit_UploadHistory:
'Clean up
On Error Resume Next
Set DB = Nothing
If bInTrans Then 'Rollback if the transaction is active.
WS.Rollback
End If
Set WS = Nothing
Exit Sub

Err_UploadHistory:
MsgBox Err.Description, vbExclamation, "Upload failed: Error " & Err.Number
Resume Exit_UploadHistory

End Sub

 

[Dwight]

I would approach this differently. I would write 2 queries: 1) an append query to move the data from the temp table to the permanent table and 2) a delete query to delete these records.

Use the queries' Where statements to identify the checked records.

Run both of the queries from the buttons Click Event.

 

[R3GUL8OR]

i tried that earlier and it really didnt work well. maybe I just had the coding all wrong

 

[R3GUL8OR]

OK, well I found my error. It was in Me.TraineeCombo. I changed it and it started to work great, the VB code was going, it asked me to commit, then it went to the OnError call and stoped. No error came up though. Plus, it not only didnt add or delete anything to the tables, but it LOCKED the tables now. Grrrrr I really hate this project. Any Ideas? the new code is below:

Sub UploadHistory()

On Error GoTo Err_UploadHistory

Dim db As DAO.Database
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim strSQL1 As String 'SQL Code for append.
Dim strSQL2 As String 'SQL Code for delete.
Dim strMsg As String 'MsgBox message.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim TraineeSearch As String

'Set DB = CurrentDb()
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

TraineeSearch = Forms![Schedule]![TraineeCombo]

'Execute the add.
strSQL1 = "INSERT INTO Training " _
& "( TaskNumber, tDate, Hours, TrainerLast, TraineeLast, Qualified ) " _
& "SELECT Task, sDate, Hours, Trainer, Trainee, Qualified FROM Schedule " _
& "WHERE Trainee = '" & TraineeSearch & "' AND Completed= True;"
db.Execute strSQL1, dbFailOnError

'Execute the delete.
strSQL2 = "DELETE FROM Schedule WHERE Trainee = '" & TraineeSearch & "' AND Completed = True;"
db.Execute strSQL2, dbFailOnError

'Get user confirmation to commit the change.
strMsg = "Upload " & db.RecordsAffected & " record(s) from " & TraineeSearch & "?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

Me.Schedule_View.Requery

Exit_UploadHistory:
'Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub

Err_UploadHistory:
MsgBox Err.Description, vbExclamation, "Upload failed: Error " & Err.Number
Resume Exit_UploadHistory

End Sub

 

[R3GUL8OR]

damn Im dumb. Nevermind. I figured out the problem. I had the checkbox format as YES/No and the SQL code for it as TRUE/FALSE. Changed it all to yes/no and it all worked perfectly.