strsql insert into inserting double record into table?

[d9pierce1]

ProfileAccount -Test.zip
Hi all,
I have the code below and for some reason it is putting two records into my table for Credit, and one for Debit. It should only be putting one debit and one credit or just one credit if "IsPayrollDeduct = True"
But i just cannot understand this at all. I am using the similiar in all my other forms and it works just fine. This form, it puts two credits in no matter what and one debit. It should not do that. I have tried going
through it and setting up relationships and such with no success. What the heck? Is there something wrong with my code?
Thanks
Dave ---- I went ahead and removed necessary info from db and uploaded it for reference. If you go to frmAccountDetail and go to City Of Hominy under account search, that has an unpaid bill in it that you can s
see what is going on maybe? I am stumped....

Private Sub CmdPostPayment_Click()
    Dim strsql As String
    Dim db As DAO.Database
    Set db = CurrentDb
    
    If Me.IsPayrollDeduct = False Then
        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Debit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboFromAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError


        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Credit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboToAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError


Else
        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Credit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboToAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError
End If


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


End Sub

[CJ_London]

for some reason you are executing it twice, once within the if part and again after the if part

Easy to see if you step through the code

[d9pierce1]

Thanks, I really dont get this step through code. I open vba editor, go to my code in question, and press F8 or step into and all it does is ding. No nothing, just a bell in background.
I have reviewed many videos of this of how, but it doesnt appear to me i am doing it correctly and there are no errors so not sure what to even look for.
Thanks\
Dave

[davegri]

Private Sub CmdPostPayment_Click()
    Dim strsql As String
    Dim db As DAO.Database
    Set db = CurrentDb
    
    If Me.IsPayrollDeduct = False Then
        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Debit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboFromAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError


        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Credit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboToAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError


Else
        strsql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, DescriptionID, TransMethodID, Credit) " & _
            "VALUES (" & TransactionID & ", " & CboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & CboToAccount & ", " & DescriptionID & ", " & CboTransMethod & ", " & TransAmount & ")"
        Debug.Print strsql
        db.Execute strsql, dbFailOnError
End If


        Me.IsPaid = True
    CurrentDb.Execute "UPDATE tblTransBillLedger SET IsPaid = True WHERE TransBillID=" & Forms!frmTransPayment!TransBillID
    debug.print strsql    
    db.Execute strsql, dbFailOnError
    
    Me.RecordLock = True
    Call Form_Current

That red strsql line is still set to whatever it was in the if-else above.
Add the blue debug.print strsql and surprise!

[d9pierce1]

I have somewhat stepped through it I think? Not real sure as doesnt come up with anything but does stop where I put break point.
starting to figure this step through out....
When I debug.Print, it only shows the two inserts, all correct info but it puts two credits in ledger table for some reason unknow to me
and is really driving me nuts. Have spent all day on this one thing. I had this happen once before or similiar and it was due to not having
a relationship which i went through today and tried all sorts of relationships in the relship window. Nothing helped at all. So please tell me
why this is putting in two of the same records on the strsql statement? Something weird here and i am at a total loss for solutions.
Thanks

[d9pierce1]

Hello davegri
Thankk you so much.... Wow, I have literally spent all day on this...
What do I owe ya... Thank you so much
Dave

[davegri]

Just keep on truckin'. Thanks for the star!

[CJ_London]

stepping through means the code steps through each line by repeatedly pressing the F8 key - you can check values as you go by hovering over the variable - or do as davegri has done, add a debug line. Looks like you ignored my first sentence, he has pointed out exactly what I pointed out. The problem with just putting the debug line is you still need to know where to put it. But seems you rely heavily of davegri to help you out so I'll butt out and won't bother you again.

[d9pierce1]

Hi Ajax,
I really didnt know how to step through the code. I have since practiced now that I have an idea of how to do this. All i got was a ding when i tried it and have watched so many videos on it but just didnt make sense to me.
In any event, i was able to step through some code yesterday and will continue to practice that..
Thanks
Dave

[Welshgasman]

Hi Ajax,
I really didnt know how to step through the code. I have since practiced now that I have an idea of how to do this. All i got was a ding when i tried it and have watched so many videos on it but just didnt make sense to me.
In any event, i was able to step through some code yesterday and will continue to practice that..
Thanks
Dave

You really need to start learning then. It is brilliant at finding what I call silly errors, when it should be working.
I find with forms, you need to set a breakpoint and then click/enter whatever to get the code started.
You cannot just enter a forms code and press F8. That will give you a 'ding'
You can with Subs and Functions in modules though.
I also use the immediate window a fair bit to test oneliners like a Dlookup, Date format etc.
That is why there is a link for debugging in my signature???

Here is something I just used to try and help someone on UA

[d9pierce1]

Thank you welshgasman,
I will copy your link and hopefully it will be more descriptive then some of the vidoes i have watched so far.....
I really need one for dummies as when doing this, i figured out yesterday i had to run the form to get it to fire but not one video i watched said that.
I guess that would be common sense now that I look back at it.
Anyway, I will definately go over your links...
Thanks you,
Dave

[d9pierce1]

Hi welshgasman,
I watched the first video so far and gave me some insite. I do have a couple of questions.

First, it was nice to learn that the curser needs to be in code box to use run.... I didnt know this and it always came up run a macro.... Nice bit of info to know
Also, He has a debug.Print "sSql: " & sSql How does one know what to put after the debug.print as I have shown in red?
Thanks
Dave

[orange]

Dave,

Anything within the quotes is simply a string of your choosing.
Debug.print "This is a message for Dave " . The ampersand concatenates the value of a variable/control..

Debug.print "A short msg: " & Date

Could produce: A short msg: 27-Jan-2022 in the immediate window.

[Welshgasman]

Hi welshgasman,
I watched the first video so far and gave me some insite. I do have a couple of questions.

First, it was nice to learn that the curser needs to be in code box to use run.... I didnt know this and it always came up run a macro.... Nice bit of info to know
Also, He has a debug.Print "sSql: " & sSql How does one know what to put after the debug.print as I have shown in red?
Thanks
Dave

If you just debug.print variablename, then you need to know what each is?
If you add some context to it with "First sql is : " & strSQL1, then you know what you are looking at.?

Remember you could output a bunch of debug.prints before you hit your next breakpoint.

If you learn debugging, then you will set breakpoints at various locations, and let the code run between them with F5.
If you do not learn debugging, then you will struggle forever and forever, and it will only slow development down.