Update (to few parameters, exptected 1 error when fired?

[d9pierce1]

Hi all,
I am working on an update to fire in the afterUdate event on my form if I choose to change the value.

frmAccount
CurrentBalance is Currency

tblLedger
Credit or Debit is Currency

The goal is if record is not new, and i change the opening balance amount on my form frmAccount, then it will update the tblAccountLedger (Credit or Debit) field to the new value I put in CurrentBalance field.


Just not sure what that error means and how to repair it?

Private Sub CurrentBalance_AfterUpdate()
    Dim strsql As String
    Dim db As DAO.Database
    Set db = CurrentDb
Me.CurrentBalanceDate = Now()


If Not Me.NewRecord Then
Select Case Me.cboAccountTypeID


Case 1 'Asset
If Me.cboAccountTypeID = 1 Then
    CurrentDb.Execute "UPDATE tblAccountLedger SET Credit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError
End If


Case 2 'Equity
If Me.cboAccountTypeID = 2 Then
    CurrentDb.Execute "UPDATE tblAccountLedger SET Credit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError
End If


Case 3 'Expense
If Me.cboAccountTypeID = 3 Then
    CurrentDb.Execute "UPDATE tblAccountLedger SET Debit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError
End If


Case 4 'Income
If Me.cboAccountTypeID = 4 Then
    CurrentDb.Execute "UPDATE tblAccountLedger SET Credit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError
End If


Case 5 'Liability (LT)
If Me.cboAccountTypeID = 5 Then
    CurrentDb.Execute "UPDATE tblAccountLedger SET Debit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError
End If


Case 6 'Liability (ST)
If Me.cboAccountTypeID = 6 Then
    CurrentDb.Execute "UPDATE tblAccountLedger SET Debit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError
End If
End Select
End If
Set db = Nothing


End Sub

Thanks for assistance
Dave

[Welshgasman]

Concatenate the form controls with the sql string.
Then debug.print that sql string to make sure it is correct.

[davegri]

SET Credit = Forms!frmAccount.CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID

Maybe it needs to know where 'currentbalance' is.

[Micron]

It probably has no idea what CurrentBalance is - you need to concatenate it if it's a field or variable reference?
Also, you don't need IF within the Case block to check for (IF) what you already know, which is the value of the combo.
You're trying to run the exact same sql whose references will have the same values? Then you don't need all those Case statements. When not consecutive, can use
Case Me.cboAccountTypeID = 1 OR Me.cboAccountTypeID = 2 OR ...

When consecutive, can use
Case 1 To 3

However, if the combo values can only be 1 through 6, what is the point of that case block when you don't deal with any other cases? Am I missing something?

[d9pierce1]

Thanks davegri
Tried that, still same error?
Thanks

[d9pierce1]

Hi Micron,
OK,
The CurrentBalance is a currency field on my form.
The Credit or Debit is a currency field in the tblAccountLedger

I was using select case as that is the only way i know how.... and the If Not new Record I need as if new record I dont want it to run,

So if I get what you are saying,
I can use
Case Me.cboAccountTypeID = 1 and get rid of the If's
Then the code
Instead of what i used?
I didnt know you could do that., thank you!

Now I can change that but it still wont solve the issue with the error correct?
How do I concatenate the Credit = CurrentBalance?
And I do not know how to do it with out a case ? Is there another way as I would love to know
Thanks
This is what I imagined from what you said above...

Private Sub CurrentBalance_AfterUpdate()
    Dim strsql As String
    Dim db As DAO.Database
    Set db = CurrentDb


Me.CurrentBalanceDate = Now()


If Not Me.NewRecord Then
Select Case Me.cboAccountTypeID


Case Me.cboAccountTypeID = 1 'Asset
    CurrentDb.Execute "UPDATE tblAccountLedger SET Credit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError


Case Me.cboAccountTypeID = 2 'Equity
    CurrentDb.Execute "UPDATE tblAccountLedger SET Credit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError


Case Me.cboAccountTypeID = 3 'Expense
    CurrentDb.Execute "UPDATE tblAccountLedger SET Debit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError


Case Me.cboAccountTypeID = 4 'Income
    CurrentDb.Execute "UPDATE tblAccountLedger SET Credit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError


Case Me.cboAccountTypeID = 5 'Liability (LT)
    CurrentDb.Execute "UPDATE tblAccountLedger SET Debit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError


Case Me.cboAccountTypeID = 6 'Liability (ST)
    CurrentDb.Execute "UPDATE tblAccountLedger SET Debit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
    db.Execute strsql, dbFailOnError
End Select
End If
Set db = Nothing
End Sub

[Micron]

So if I get what you are saying,
I can use
Case Me.cboAccountTypeID = 1 and get rid of the If's

Not quite. You Select Case Me.cboAccountTypeID and Case = something
Your code doesn't make sense to me - perhaps some context is in the module but not evident here. I don't see what strSql is all about so perhaps the issue is there, not in the CurrentDb line as we've assumed. Why db.Execute on one line and CurrentDb.Execute on another? I don't see the difference between what you have and this

Private Sub CurrentBalance_AfterUpdate()
    Dim strsql As String
    Dim db As DAO.Database
    Set db = CurrentDb

Me.CurrentBalanceDate = Now()

If Not Me.NewRecord Then
   Select Case Me.cboAccountTypeID
     Case 1 To 6

       CurrentDb.Execute "UPDATE tblAccountLedger SET Debit = CurrentBalance WHERE AccountID=" & Forms!frmAccount!AccountID
       db.Execute strsql, dbFailOnError

   End Select
End If

Set db = Nothing
End Sub

It's the same UPDATE in every case? Would be different if you were using the value of the combo, but you're not. So I don't get it.

[Welshgasman]

3 are credits and 3 are debits but I agree, just two conditions would be enough.
I have no idea what strsql is meant to do either, it never gets set?

[d9pierce1]

Hi Micron,
Some are a debit and some are credit so there are differences in this.

My goal here is if I update the currentbalance on my form, it updates a different table (tblAccountLedger) to that currency value i entered, If not a new record.
If its a credit amount via Asset, Equity, or Income it goes into the credit field on my ledger, If its a debit amount via Expense, Libaility LT, or Liability ST, then it will go into the debit field in my ledger

I am trying to alter a code I got from June7 on here that updated a chkbox field on a different table and worked like a charm... Not such luck in altering it.

[Micron]

Yes so I missed the Debit Credit thing. So nice when you ask and someone points out your mistake or clarifies something. So yes, probably 2 Case statements is enough.
We could dance around this all day, but it's New Years eve and I have stuff to do. Suggest you post the db because fixing the Select block won't fix the original problem and I think we need to nip this one. Just my opinion, but I'd be glad to take a look for you. I imagine same goes for other responders.

[d9pierce1]

Hi Welsh...
I do agree, 2 conditions would surfice but I didnt know how to write that, as far as the strsql i would have taken it out excpet
the code I was given by June7 had it in there. If I take it out, i get a whole different error...

This is what I am trying to alter that June7 gave me.

    CurrentDb.Execute "UPDATE tblTransBillLedger SET IsPaid = True WHERE TransBillID=" & Forms!frmTransPayment!TransBillID
    db.Execute strsql, dbFailOnError

[Gicu]

Hi Dave,
Would you please try this:

Private Sub CurrentBalance_AfterUpdate()
Dim strsql As String
Dim db As DAO.Database
Set db = CurrentDb
Me.CurrentBalanceDate = Now()




If Not Me.NewRecord Then
    Select Case Me.cboAccountTypeID


    Case In (1,2,4) 'Asset, Equity or Income
         strsql ="UPDATE tblAccountLedger SET Credit = " & Me.CurrentBalance  & " WHERE AccountID=" & Me.AccountID   
    Case Else 'Or Case In (3,5,6) Expense,Liability LT and ST
         strsql ="UPDATE tblAccountLedger SET Debit = " & Me.CurrentBalance  & " WHERE AccountID=" & Me.AccountID
    End Select
    db.Execute strsql, dbFailOnError
End If


Set db = Nothing


End Sub

https://docs.microsoft.com/en-us/off...ase-statements

Cheers,

[d9pierce1]

Hi Gicu,
That looks fantastic yet I am getting the following in red in my db

Private Sub CurrentBalance_AfterUpdate()
Dim strsql As String
Dim db As DAO.Database
Set db = CurrentDb
Me.CurrentBalanceDate = Now()








If Not Me.NewRecord Then
    Select Case Me.cboAccountTypeID




    Case In (1,2,4) 'Asset, Equity or Income
         strsql = "UPDATE tblAccountLedger SET Credit = " & Me.CurrentBalance & " WHERE AccountID=" & Me.AccountID
    Case Else 'Or Case In (3,5,6) Expense,Liability LT and ST
         strsql = "UPDATE tblAccountLedger SET Debit = " & Me.CurrentBalance & " WHERE AccountID=" & Me.AccountID
    End Select
    db.Execute strsql, dbFailOnError
End If




Set db = Nothing




End Sub

Thanks
Dave

[Welshgasman]

You can't just use code, without understanding it, if you want to alter it.
Strsql would have held some sql statement for your previous problem. Pretty much like your Update statement. However that only updates an existing record, it does not create a new one, for that you would use Insert?
There is such a thing as an Upsert query I believe, which does both, but I have never created or used one.

[Micron]

there is no IN in Case statements. It's just (suggested)

Case 1,2,4 'Asset, Equity or Income

Case 3,5,6 'Expense, LT Liability or ST Liability

Ciao. Gotta go.

EDIT - corrected a rather rushed paste for Case 3,5,6