Better Coding 2 SQL Commands

[DS]

I have to run 2 SQL commands, This is what I came up with but I'm sure
there is a cleaner way. It works but cleaner would be better!
Thanks
DS

Private Sub Command4_Click()

CurrentDb.Execute "INSERT INTO JobsDept(DeptID,JobID) VALUES(" &
Forms!DeepJob!DepartmentID & ", " & Me.List0.Column(0) & ")"


Dim MySQL As String
DoCmd.SetWarnings False
MySQL = "UPDATE JobNames SET [Assigned] = True " & _
"WHERE [JobNameID] = Forms!JobPicker![Text6]; "
DoCmd.RunSQL (MySQL)
DoCmd.SetWarnings True

DoCmd.Close acForm, "JobPicker"
Forms!DeepJob.List44.Requery
End Sub

 

[Vincent Johns]

I have to run 2 SQL commands, This is what I came up with but I'm sure
there is a cleaner way. It works but cleaner would be better!
Thanks
DS

Private Sub Command4_Click()

CurrentDb.Execute "INSERT INTO JobsDept(DeptID,JobID) VALUES(" &
Forms!DeepJob!DepartmentID & ", " & Me.List0.Column(0) & ")"


Dim MySQL As String
DoCmd.SetWarnings False
MySQL = "UPDATE JobNames SET [Assigned] = True " & _
"WHERE [JobNameID] = Forms!JobPicker![Text6]; "
DoCmd.RunSQL (MySQL)
DoCmd.SetWarnings True

DoCmd.Close acForm, "JobPicker"
Forms!DeepJob.List44.Requery
End Sub

What are you trying to clean up? It looks as if you're making changes
to two different Tables. It's not unreasonable to have to use two SQL
statements to do that, so I don't see any obvious way to combine them.
Or were you considering doing something else?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Graham R Seach]

I don't understand why you use the Execute method AND the RunSQL method in
the same procedure. Just use the Execute method. Also, since you have two
queries, I'd be inclined to wrap them in a transaction, so if either of them
fails, the whole lot is rolled back.

Private Sub Command4_Click()
Dim ws As Workspace
Dim db As Database
Dim sSQL As String

Set ws = DbEngine(0)
Set db = CurrentDb
ws.BeginTrans

On Error Goto Proc_Err

sSQL = "INSERT INTO JobsDept (DeptID, JobID) " & _
"VALUES(" & Forms!DeepJob!DepartmentID & _
", " & Me.List0.Column(0) & ")"

db.Execute sSQL, dbFailOnError

sSQL = "UPDATE JobNames " & _
"SET [Assigned] = True " & _
"WHERE [JobNameID] = Forms!JobPicker![Text6]"

db.Execute sSQL, dbFailOnError
ws.CommitTrans

DoCmd.Close acForm, "JobPicker"
Forms!DeepJob.List44.Requery

Proc_Exit:
Set db = Nothing
Exit Sub

Proc_Err:
ws.Rollback
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

If JobPicker is the name of the form in which this code runs, then you can
refer to it thus:
DoCmd.Close acForm, Me.Name

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Graham R Seach]

Oh, I forgot...

Proc_Exit:
Set db = Nothing
Set ws = Nothing
Exit Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I don't understand why you use the Execute method AND the RunSQL method in
the same procedure. Just use the Execute method. Also, since you have two
queries, I'd be inclined to wrap them in a transaction, so if either of
them fails, the whole lot is rolled back.

Private Sub Command4_Click()
Dim ws As Workspace
Dim db As Database
Dim sSQL As String

Set ws = DbEngine(0)
Set db = CurrentDb
ws.BeginTrans

On Error Goto Proc_Err

sSQL = "INSERT INTO JobsDept (DeptID, JobID) " & _
"VALUES(" & Forms!DeepJob!DepartmentID & _
", " & Me.List0.Column(0) & ")"

db.Execute sSQL, dbFailOnError

sSQL = "UPDATE JobNames " & _
"SET [Assigned] = True " & _
"WHERE [JobNameID] = Forms!JobPicker![Text6]"

db.Execute sSQL, dbFailOnError
ws.CommitTrans

DoCmd.Close acForm, "JobPicker"
Forms!DeepJob.List44.Requery

Proc_Exit:
Set db = Nothing
Exit Sub

Proc_Err:
ws.Rollback
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

If JobPicker is the name of the form in which this code runs, then you can
refer to it thus:
DoCmd.Close acForm, Me.Name

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have to run 2 SQL commands, This is what I came up with but I'm sure
there is a cleaner way. It works but cleaner would be better!
Thanks
DS

Private Sub Command4_Click()

CurrentDb.Execute "INSERT INTO JobsDept(DeptID,JobID) VALUES(" &
Forms!DeepJob!DepartmentID & ", " & Me.List0.Column(0) & ")"


Dim MySQL As String
DoCmd.SetWarnings False
MySQL = "UPDATE JobNames SET [Assigned] = True " & _
"WHERE [JobNameID] = Forms!JobPicker![Text6]; "
DoCmd.RunSQL (MySQL)
DoCmd.SetWarnings True

DoCmd.Close acForm, "JobPicker"
Forms!DeepJob.List44.Requery
End Sub