marku24
asked on
SQL Insert Statement Not Working in Access
I have the below statement which executes without an error but the value does not write into the table. Am I missing something?
Function WriteTransaction()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSql As String
Dim intPaymentID As Integer
Dim intMemberID As Integer
intPaymentID = [Form_Payment SubForm_Add].txtPaymentID_ Trans.Valu e
intMemberID = [Form_Payment SubForm_Add].txtMemberID_T rans.Value
Set db = CurrentDb()
sSql = "Select * from [tblJunction_MemberID2Paym entID]"
Set rs = db.OpenRecordset(sSql)
sSql = "INSERT INTO tblJunction_MemberID2Payme ntID (MemberID, PaymentID) VALUES (" & intMemberID & " , " & intPaymentID & ")"
db.Execute (sSql)
End Function
Function WriteTransaction()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSql As String
Dim intPaymentID As Integer
Dim intMemberID As Integer
intPaymentID = [Form_Payment SubForm_Add].txtPaymentID_
intMemberID = [Form_Payment SubForm_Add].txtMemberID_T
Set db = CurrentDb()
sSql = "Select * from [tblJunction_MemberID2Paym
Set rs = db.OpenRecordset(sSql)
sSql = "INSERT INTO tblJunction_MemberID2Payme
db.Execute (sSql)
End Function
ASKER
It still didn't work. it is very strange.
And what's the error? Can you show us the table structure. I suspect there could be some other fields that do not allow nulls, or one of the fields you are trying to insert is an AutoNumber.
ASKER
there are only 2 fields in the table I am writing to. Both are number fields (Integer). I can type the values into the field and the table accepts them. This is the first time I am using Access 2013, could it be that?
And what is the error?
Give this a try instead... you really only need the sSQL string and the Execute statement for what you are doing. I added "dbFailOnError" to the execute statement so that you WILL get a meaningful error if something is wrong with your SQL statement. Without dbFailOnError, that execute statement would run without error despite problems in the SQL... (it just wouldn't write anything to the table).
Function WriteTransaction()
Dim db As DAO.Database
Dim sSql As String
Dim intPaymentID As Long
Dim intMemberID As Long
intPaymentID = [Form_Payment SubForm_Add].txtPaymentID_Trans.Value
intMemberID = [Form_Payment SubForm_Add].txtMemberID_Trans.Value
Set db = CurrentDb()
sSql = "INSERT INTO tblJunction_MemberID2PaymentID (MemberID, PaymentID) VALUES (" & intMemberID & " , " & intPaymentID & ")"
db.Execute sSQL, dbFailOnError
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
worked awesome - Thanks
Open in new window