A Better Way

[DS]

Is there a better way to do this?
Thamks
DS

Private Sub ImageRemove_Click()

If Me.ListApply.ListCount = 0 Then
DoCmd.OpenForm "NoPayments"
ElseIf IsNull(Me.TxtPayID) Then
DoCmd.OpenForm "NoPaymentSelected"
ElseIf Me.ListApply.ListCount > 0 Then
If Me.TxtTypeID = 9 Then
DoCmd.SetWarnings False
Dim RemoveDepSQL As String
RemoveDepSQL = "UPDATE Deposits SET
[Applied]=False,[UsageDate]=Null " & _
"WHERE Deposits.[DepositID] = Forms!CheckPayment!TxtDepositID "
DoCmd.RunSQL (RemoveDepSQL)
DoCmd.SetWarnings True
DoCmd.SetWarnings False
Dim MySQL As String
MySQL = "DELETE * FROM PayApplied " & _
"WHERE [PaymentID] = " & Me![TxtPayID] & "And [SalesID] = " &
Me![SalesID] & ""
DoCmd.RunSQL (MySQL)
DoCmd.SetWarnings True
Me.ListApply.Requery
Forms!CheckPayment!TxtTotalPayments = Nz(DSum("[PaymentAmount]",
"PayApplied", "SalesID=Forms!CheckPayment!SalesID"), 0)
Else:
DoCmd.SetWarnings False
MySQL = "DELETE * FROM PayApplied " & _
"WHERE [PaymentID] = " & Me![TxtPayID] & "And [SalesID] = " &
Me![SalesID] & ""
DoCmd.RunSQL (MySQL)
DoCmd.SetWarnings True
Me.ListApply.Requery
Forms!CheckPayment!TxtTotalPayments = Nz(DSum("[PaymentAmount]",
"PayApplied", "SalesID=Forms!CheckPayment!SalesID"), 0)
End If
End If
End Sub

 

[Rob Oldfield]

Nothing major. But minor points:

db.execute is better then runsql
There's no point in code that switches warning on, then immediately off
(comment will be redundant if you do the one above instead)

Otherwise, looks fine to me. Anything in particular you were thinking of?

 

[DS]

Nothing major. But minor points:

db.execute is better then runsql
There's no point in code that switches warning on, then immediately off
(comment will be redundant if you do the one above instead)

Otherwise, looks fine to me. Anything in particular you were thinking of?

Is there a better way to do this?
Thamks
DS

Private Sub ImageRemove_Click()

If Me.ListApply.ListCount = 0 Then
DoCmd.OpenForm "NoPayments"
ElseIf IsNull(Me.TxtPayID) Then
DoCmd.OpenForm "NoPaymentSelected"
ElseIf Me.ListApply.ListCount > 0 Then
If Me.TxtTypeID = 9 Then
DoCmd.SetWarnings False
Dim RemoveDepSQL As String
RemoveDepSQL = "UPDATE Deposits SET
[Applied]=False,[UsageDate]=Null " & _
"WHERE Deposits.[DepositID] = Forms!CheckPayment!TxtDepositID "
DoCmd.RunSQL (RemoveDepSQL)
DoCmd.SetWarnings True
DoCmd.SetWarnings False
Dim MySQL As String
MySQL = "DELETE * FROM PayApplied " & _
"WHERE [PaymentID] = " & Me![TxtPayID] & "And [SalesID] = " &
Me![SalesID] & ""
DoCmd.RunSQL (MySQL)
DoCmd.SetWarnings True
Me.ListApply.Requery
Forms!CheckPayment!TxtTotalPayments = Nz(DSum("[PaymentAmount]",
"PayApplied", "SalesID=Forms!CheckPayment!SalesID"), 0)
Else:
DoCmd.SetWarnings False
MySQL = "DELETE * FROM PayApplied " & _
"WHERE [PaymentID] = " & Me![TxtPayID] & "And [SalesID] = " &
Me![SalesID] & ""
DoCmd.RunSQL (MySQL)
DoCmd.SetWarnings True
Me.ListApply.Requery
Forms!CheckPayment!TxtTotalPayments = Nz(DSum("[PaymentAmount]",
"PayApplied", "SalesID=Forms!CheckPayment!SalesID"), 0)
End If
End If
End Sub

No, nothing in particular. I'm always looking to learn though. Why is
Currentdb.execute better than RunSQL? That's interesting to me.
Thanks
DS

 

[Rob Oldfield]

No, nothing in particular. I'm always looking to learn though. Why is

Currentdb.execute better than RunSQL? That's interesting to me.
Thanks
DS

It's not a topic I know a great deal about. It's just generally
acknowledged that execute is better, albeit only slightly. A couple of
things:

You don't have to mess around turning warnings on and off.
Error handling is better.
Apparently (i.e. I have nothing to confirm that this is true) execute is a
touch quicker.

 

[DS]

It's not a topic I know a great deal about. It's just generally
acknowledged that execute is better, albeit only slightly. A couple of
things:

You don't have to mess around turning warnings on and off.
Error handling is better.
Apparently (i.e. I have nothing to confirm that this is true) execute is a
touch quicker.

Thanks for the info.
DS