Submitt Button with Muti options?

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

Hi all,
I have the following code on a submitt button. I am missing something between If statements so that if its not the first IF, then go to second If... and so on? Maybe i need a select case senario or ?????
Can you help please....

Code:

Private Sub CmdSubmit_Click()


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




If Me.TxtTransType = "Transfer" Or "Payment" Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
        "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " To " & CboToAccount.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
    DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
        "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboFromAccount.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
    DoCmd.SetWarnings True




If Me.TxtTransType = "Purchase" Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
        "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
If Me.ChkIsRemburseExp = True Then
    DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
        "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  " & TxtTransDescription & " , '" & TxtMethod & "' , " & TxtTransAmount & ")"
    DoCmd.SetWarnings True




If Me.TxtTransType = "Record Bill" Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
        "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
    DoCmd.SetWarnings True
End If
    End If
        End If
            End If
                End If
                    End If
End Sub

Thanks

Dave

**Micron** · 12-04-2020, 12:55 PM

To help, one would have to understand the requirements. You've nested them all, so the first condition must be true, then the next also, then the next also and so on. You are testing for transfer type being different several times yet those tests are nested. If it isn't Deposit, none of the rest of it will execute. If it is, then it cannot also be the other options, so none of those will execute.

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

Is there a way for if the first conditions isnt true, then it goes to the next condition?

**Welshgasman** · 12-04-2020, 01:13 PM

You might want to indent correctly, then it is easier to spot issues?

Code:

Private Sub CmdSubmit_Click()


    Me.TxtTransNumber = [TxtRandom]

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




            If Me.TxtTransType = "Transfer" Or "Payment" Then
                DoCmd.SetWarnings False
                DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                             "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " To " & CboToAccount.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
                DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                             "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboFromAccount.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
                DoCmd.SetWarnings True




                If Me.TxtTransType = "Purchase" Then
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                                 "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
                    If Me.ChkIsRemburseExp = True Then
                        DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                                     "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  " & TxtTransDescription & " , '" & TxtMethod & "' , " & TxtTransAmount & ")"
                        DoCmd.SetWarnings True




                        If Me.TxtTransType = "Record Bill" Then
                            DoCmd.SetWarnings False
                            DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                                         "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
                            DoCmd.SetWarnings True
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub

**Welshgasman** · 12-04-2020, 01:16 PM

You might want to indent correctly, then it is easier to spot issues?
If what you say is true, then the ifs should not be nested, and you shpuld exit the sub when true?

Code:

Private Sub CmdSubmit_Click()


    Me.TxtTransNumber = [TxtRandom]

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




            If Me.TxtTransType = "Transfer" Or "Payment" Then
                DoCmd.SetWarnings False
                DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                             "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " To " & CboToAccount.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
                DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                             "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboFromAccount.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
                DoCmd.SetWarnings True




                If Me.TxtTransType = "Purchase" Then
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                                 "VALUES (" & TxtTransID & ", " & CboFromAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
                    If Me.ChkIsRemburseExp = True Then
                        DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                                     "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  " & TxtTransDescription & " , '" & TxtMethod & "' , " & TxtTransAmount & ")"
                        DoCmd.SetWarnings True




                        If Me.TxtTransType = "Record Bill" Then
                            DoCmd.SetWarnings False
                            DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                                         "VALUES (" & TxtTransID & ", " & CboToAccount & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', '" & TxtTransType & " From " & CboCompany.Column(1) & "', '" & TxtMethod & "' , " & TxtTransAmount & ")"
                            DoCmd.SetWarnings True
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub

**d9pierce1** · 12-04-2020, 01:24 PM

Thank you for pointing that out on the indents and for the Exit Sub....
Do I put an exit sub after each of the conditions?
Thanks

**Welshgasman** · 12-04-2020, 01:32 PM

I would have gone for Select Case on the TranType?

Once you have run code for the true condition, there is no point checking anymore is there.?, so you may as well exit.?
If you go the Select Case route then there is no need for an exit sub.
If you go for the If route for TranType then each needs to be in it's own right, not nested as you have it now.?

EG: If Trantype is NOT Deposit then the rest of the code is not even entered?

**d9pierce1** · 12-04-2020, 01:35 PM

I will try out a select case, i wondered if that was the way to go!
Thank you

**moke123** · 12-04-2020, 01:42 PM

Gasman beat me to it but I was mocking up the code.
You could really clean up your code with select case and using a db variable or currentdb to eliminate turning warnings on and off.

something along these lines

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, 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

**Micron** · 12-04-2020, 01:53 PM

Is there a way for if the first conditions isnt true, then it goes to the next condition?

Sub DoStuff()
- do some test and if not true
Exit Sub
End Sub

I would probably do the Select Case block too, but as I've said, to me the requirements are not clear.
I mean if the first test of TxtTransType is not "Deposit" do we go on to If Me.ChkDepositRemburseExp or not?
If that's an independent test, then AFAIK, you cannot test two conditions like that in one Case.

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

Wow, this is really close. Sorry i didnt be more clear.

So, I actually want to have it insert both conditions, Basically i am crediting one acccount and debiting a different account If the Me.ChkDepositRemburseExp Condition is True, if not, then just do nothing for that condition.

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

Then I think you have it in post 9?

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

"IN THIS FIRST CONDITION I WANT IT TO INSERT AS LONG AS ITS A DEPOSIT, AND THE SECOND CONDITION TO INSERT IF TRUE"
I am trying to get if deposit to insert the first condition, and also insert the second condition if its true. It looks to me as if the chk is False, then it will insert first condition, but if true then insert the second condition?

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, Debit) " & _
"VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "', " & TxtTransDescription & " , '" & TxtMethod & "' , " & TxtTransAmount & ")"

End If

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

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

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

So the first Insert does not need to be within an IF.?

Submitt Button with Muti options?

Thread Tools

Submitt Button with Muti options?

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