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...
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...
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?
you are missing some " and = signs in post 24.
If this helped, please click the star * at the bottom left and add to my reputation- Thanks
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
thanks, i seen that and fixed it but it, it doesnt even give me an error when compling
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.
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
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.
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
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba