Submitt Button with Muti options?

**Welshgasman** · 12-04-2020, 03:21 PM

Don't repeat code.
Any time you do that the logic is off.

Think about what you need to do.?

It's a deposit, so you want to insert the credit.?

Then if the IF is true you want to carry out the second Insert.

This is basic logic here?

**moke123** · 12-04-2020, 03:26 PM

Originally Posted by d9pierce1

Can I write the code as this?

Code:

Private Sub CmdSubmit_Click()


Dim strSql As String
Dim db As DAO.Database
Set db = CurrentDb
   
    Me.TxtTransNumber = [TxtRandom]


    Select Case Me.TxtTransNumber


    Case "Deposit"
    
        If Me.ChkDepositRemburseExp = False Then
        
            strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                     "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
        Else
            strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                     "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
            strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                     "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  " & TxtTransDescription & " , '" & TxtMethod & "' , " & TxtTransAmount & ")"


        End If


    Case "Transfer", "Payment"


    Case "Purchase"


    Case "Record Bill"


    Case Else


    End Select
    
    db.Execute strSql, dbFailOnError
    
    Set db = Nothing
    
  End Sub

you would need to move the "db.Execute strSql, dbFailOnError" from the bottom of the procedure and insert it after each strSql statement.

**Micron** · 12-04-2020, 03:34 PM

"IN THIS FIRST CONDITION I WANT IT TO INSERT AS LONG AS ITS A DEPOSIT, AND THE SECOND CONDITION TO INSERT IF TRUE"

My wife can shout at me because I let her think she's the boss.
Not going to take it from you though. Ciao.

**d9pierce1** · 12-04-2020, 03:42 PM

No, It actually credits one account in the first condition and if the "If Statement" = True, then it debits a different account. Its a double entry system. So if I deposit into an income account, and its for a Rem Exp, It Credits my income account and debits my remburseable account. The same thing for a "Purchase" sort of, if i purchase something that is rembursable, then under purchase, it would take out of one account as a debit, and if True, credit the Rembursed Expense Account.

**d9pierce1** · 12-04-2020, 03:52 PM

LOL, wasnt shouting my friend, just wanted to make it so it stood out!

**d9pierce1** · 12-04-2020, 03:56 PM

Yep, thats what i hope to do

**d9pierce1** · 12-04-2020, 03:59 PM

I will try to see if i can do that accruately as "All of the Cases, actually do something different as far as credit and debit and there are two If Statments that are each different in themselves.
Wow, I knew it was going to be a battle but somehow will suceed!
Thanks again everyone for all the help

**Welshgasman** · 12-04-2020, 04:21 PM

Take one step at a time.
Get one case working and then move on to another. As you process the next one in tuen it should get more familiar to you.

Whenever you have a large problem, break it down to smaller steps. A lot of the time you will then see where some steps are common to all conditions?

Ignore my comment about duplicating code, that is probably a bit to much to expect at this stage.? If you have to duplicate it as above so you can understand it,do so.

**d9pierce1** · 12-04-2020, 04:23 PM

Do you mean like this? As far as the "db.Execute strSql, dbFailOnError"

Code:

Private Sub CmdSubmit_Click()


Dim strSql As String
Dim db As DAO.Database
Set db = CurrentDb
   
    Me.TxtTransNumber = [TxtRandom]


    Select Case Me.TxtTransNumber


    Case "Deposit"
    
        If Me.ChkDepositRemburseExp = False Then
        
            strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                     "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
            db.Execute strSql, dbFailOnError
	
 	Else
            strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                     "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
            db.Execute strSql, dbFailOnError
	    strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                     "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  " & TxtTransDescription & " , '" & TxtMethod & "' , " & TxtTransAmount & ")"
            db.Execute strSql, dbFailOnError
        End If


    Case "Transfer", "Payment


            strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                      "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " To " & CboToAccount.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
            db.Execute strSql, dbFailOnError
            strSql =  "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                       "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboFromAccount.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
	    db.Execute strSql, dbFailOnError


    Case "Purchase"


        If Me.ChkIsRemburseExp = False Then
        
            strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                                 "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
	     db.Execute strSql, dbFailOnError
 	Else
            strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                                 "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
            db.Execute strSql, dbFailOnError
	    strSql = "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                                     "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  " & TxtTransDescription & " , '" & TxtMethod & "' , " & TxtTransAmount & ")"
            db.Execute strSql, dbFailOnError
        End If


    Case "Record Bill"
            strSql "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                                     "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
            db.Execute strSql, dbFailOnError




    Case Else


    End Select
   
    Set db = Nothing
    
  End Sub

**Missinglinq** · 12-04-2020, 04:34 PM

I'm not going to jump into this...except to point out that the syntax

Originally Posted by Welshgasman

Code:

Private Sub CmdSubmit_Click()

...........................................
            If Me.TxtTransType = "Transfer" Or "Payment" Then
..........................................

End Sub

is incorrect. It needs to be

Code:

If Me.TxtTransType = "Transfer" Or Me.TxtTransType = "Payment" Then

Linq ;0)>

**Welshgasman** · 12-04-2020, 04:39 PM

I believe we are now using Select Case logic for txtTransType.

Originally Posted by Missinglinq

I'm not going to jump into this...except to point out that the syntax

is incorrect. It needs to be

Code:

If Me.TxtTransType = "Transfer" Or Me.TxtTransType = "Payment" Then

Linq ;0)>

**ssanfu** · 12-04-2020, 05:05 PM

@d9pierce1
RE: Post #18 & #20

You must know that all caps, especially in the context of a Forum, is the equivalent of shouting?
If you wanted something to stand out, there are other ways.

1) In this first condition I want it to insert as long as its a deposit, and the second condition to insert if TRUE"
2) In this first condition I want it to insert as long as its a deposit, and the second condition to insert if TRUE
3) In this first condition I want it to insert as long as its a deposit, and the second condition to insert if TRUE
4) *** In this first condition I want it to insert as long as its a deposit, and the second condition to insert if TRUE ***

5) *** In this first condition I want it to insert as long as its a deposit, and the second condition to insert if TRUE ***

6) Separated from other lines by a couple of blank lines

And I have to say, it kind of irked me also. I didn't think you meant anything by using all caps, but still, it hit me wrong for a second.......

---------------------------------------------------------------------------------------------------------------------

On the coding front:
In the code in your first post, the following IF will not work.

Code:

If Me.TxtTransType = "Transfer" Or "Payment" Then
   ' more code
End If

You MUST explicitly do the comparison

Code:

If Me.TxtTransType = "Transfer" Or Me.TxtTransType = "Payment" Then
   ' more code
End If

This is taken care of by using the "Select Case" option (and I would have suggested Select Case if not already suggested)

Good luck with your project.

**d9pierce1** · 12-04-2020, 05:25 PM

Thank you and i do oppoligise as it really wasnt ment as yelling!

In any event, Where are you fininding this code you are quoting?
If Me.TxtTransType = "Transfer" Or "Payment" Then

**Welshgasman** · 12-04-2020, 05:38 PM

Look at your original post?
Third IF statement

Originally Posted by d9pierce1

Thank you and i do oppoligise as it really wasnt ment as yelling!

In any event, Where are you fininding this code you are quoting?
If Me.TxtTransType = "Transfer" Or "Payment" Then

**d9pierce1** · 12-04-2020, 08:22 PM

correct, it get inserted no matter what...

Submitt Button with Muti options?

Thread Tools

Similar Threads

Case Select Question for muti forms

How to hide Access Options button?

Muti Value Export to Excel

Access Options Button

Toggle Button Options

Posting Permissions