Link to home
Start Free TrialLog in
Avatar of marku24
marku24Flag for United States of America

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.Value
intMemberID = [Form_Payment SubForm_Add].txtMemberID_Trans.Value

Set db = CurrentDb()

sSql = "Select * from [tblJunction_MemberID2PaymentID]"

Set rs = db.OpenRecordset(sSql)

sSql = "INSERT INTO tblJunction_MemberID2PaymentID (MemberID, PaymentID) VALUES (" & intMemberID & " , " & intPaymentID & ")"

db.Execute (sSql)





End Function
Avatar of chaau
chaau
Flag of Australia image

You need to convert your int values to string:

sSql = "INSERT INTO tblJunction_MemberID2PaymentID (MemberID, PaymentID) VALUES (" & CStr(intMemberID) & " , " & CStr(intPaymentID) & ")"

Open in new window

Avatar of marku24

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.
Avatar of marku24

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marku24

ASKER

worked awesome - Thanks