Submitt Button with Muti options?

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

Thanks,
I thought we had all ready determined that was not the way to go so i didnt even look at that. I got off on this Case thing which havent been able to make work either.
Thanks, Sorry about that...

**davegri** · 12-04-2020, 09:08 PM

What's not working with the Select Case method that you showed in post #24? Are you getting an error, or it doesn't do the inserts properly?

**moke123** · 12-04-2020, 09:12 PM

you are missing some " and = signs in post 24.

**d9pierce1** · 12-05-2020, 01:22 AM

Hi Davegri
When i run the code in like post 24, it does absolutely nothing. NO errors, no messages, and does not insert into the table. I fixed the = signs and such in the code but still does notning
thanks

**d9pierce1** · 12-05-2020, 01:24 AM

thanks, i seen that and fixed it but it, it doesnt even give me an error when compling

**davegri** · 12-05-2020, 09:28 AM

Originally Posted by d9pierce1

Hi Davegri
When i run the code in like post 24, it does absolutely nothing. NO errors, no messages, and does not insert into the table. I fixed the = signs and such in the code but still does notning
thanks

That would indicate that txtTransferNumber is not equal to "Deposit" or "Payment" , "Transfer".
In fact, its name indicates that it's a number, not a text field. What is TxtRandom?
To find out, put a
Debug.print "TransNumber=" & Me.txtTransNumber
just before the Select Case line.

If that results in nothing in the immediate window, then the sub CmdSubmit_Click event is not being fired at all.

**Welshgasman** · 12-05-2020, 09:31 AM

Originally Posted by d9pierce1

Hi Davegri
When i run the code in like post 24, it does absolutely nothing. NO errors, no messages, and does not insert into the table. I fixed the = signs and such in the code but still does notning
thanks

Walk through the code with F8 after setting a breakpoint.

**d9pierce1** · 12-05-2020, 10:03 AM

Thank you,
I went back to the Do.Cmd RunSql along with the select case and it worked! Not sure why i cant get the "db.Execute strSql, dbFailOnError" to work but have had issues with that in the past.
So, here is the code i used and it does exactly what i wanted! Wow
Thank you all for all suggestions, assistance, and time!

Code:

Private Sub CmdSubmit_Click()


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


    Select Case Me.TxtTransType


    Case "Deposit"
    
        If Me.ChkDepositRemburseExp = False 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 & ")"
            'db.Execute strSql, dbFailOnError
    
    Else
             DoCmd.RunSQL "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
            DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                     "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  '" & TxtTransDescription & "' , '" & TxtMethod & "' , " & TxtTransAmount & ")"
            DoCmd.SetWarnings True
        End If


    Case "Transfer", "Payment"
            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 & ")"
        'db.Execute strSql, dbFailOnError
            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


    Case "Purchase"


        If Me.ChkIsRemburseExp = False 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 & ")"
         'db.Execute strSql, dbFailOnError
    Else
                DoCmd.RunSQL "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
                DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                        "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  '" & TxtTransDescription & "' , '" & TxtMethod & "' , " & TxtTransAmount & ")"
                DoCmd.SetWarnings True
        End If


    Case "Record Bill"
                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




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

**Welshgasman** · 12-05-2020, 10:22 AM

You keep chopping and changing what variable you are using in you select Case.

If you are testing transnumber for "Deposit" and that does not even look right?, then there is no strSQL, so nothing to execute?
Now you have changed it back to txtTranstype and the Case statement will work?

Good luck anyway.

Originally Posted by d9pierce1

Thank you,
I went back to the Do.Cmd RunSql along with the select case and it worked! Not sure why i cant get the "db.Execute strSql, dbFailOnError" to work but have had issues with that in the past.
So, here is the code i used and it does exactly what i wanted! Wow
Thank you all for all suggestions, assistance, and time!

Code:

Private Sub CmdSubmit_Click()


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


    Select Case Me.TxtTransType


    Case "Deposit"
    
        If Me.ChkDepositRemburseExp = False 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 & ")"
            'db.Execute strSql, dbFailOnError
    
    Else
             DoCmd.RunSQL "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
            DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Debit) " & _
                     "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  '" & TxtTransDescription & "' , '" & TxtMethod & "' , " & TxtTransAmount & ")"
            DoCmd.SetWarnings True
        End If


    Case "Transfer", "Payment"
            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 & ")"
        'db.Execute strSql, dbFailOnError
            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


    Case "Purchase"


        If Me.ChkIsRemburseExp = False 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 & ")"
         'db.Execute strSql, dbFailOnError
    Else
                DoCmd.RunSQL "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
                DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, TransNumber, Description, Method, Credit) " & _
                        "VALUES (" & TxtTransID & ", " & CboRE & ", #" & TxtTransDate & "#, '" & TxtTransNumber & "',  '" & TxtTransDescription & "' , '" & TxtMethod & "' , " & TxtTransAmount & ")"
                DoCmd.SetWarnings True
        End If


    Case "Record Bill"
                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




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

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