I cant save data from form to table.(Urgent)

[abie16]

I'm creating a database for my company. Here we have errors form to calculate the errors done. The data entered in this form need to update into the errors table. This is my code but i'm getting error "Run-time error. Syntax error in INSERT INTO statement".

Private Sub CmdSave_Enter()

Dim strselect As String
Dim strinsert As String
Dim flag As Boolean
Dim temp As String
Dim msgval As Integer


flag = True


'Below query Selects the recordset as per the conditions
strselect = "Select * FROM Errors where Date = date() AND Sub-Process='" & txtSub.Value & "'"


'Below Query inserts the Review time as a new record
strinsert = "INSERT INTO Errors (Date,LoginID,Sub-Process, Incorrect Data) values (date(),'" & Lgn & "', '" & txtSub.Value & "','" & txt1.Value & "')"



'Setting connection with different tables

Set cn = Application.CurrentProject.Connection 'Setting connection to the table Review_Time
Set rs = New ADODB.Recordset 'Selecting the records from the Review_Time Table
Set rs2 = New ADODB.Recordset 'For updating and inserting the records in the Review_Time Table


On Error GoTo errorhandler


errorhandler:

If flag = True Then

rs2.Open strinsert, cn, adOpenForwardOnly, adLockOptimistic

MsgBox "Reviewed Volume Added"

End If

Hope someone can guide me.Thank you in advance.

[MikeTheBike]

I'm creating a database for my company. Here we have errors form to calculate the errors done. The data entered in this form need to update into the errors table. This is my code but i'm getting error "Run-time error. Syntax error in INSERT INTO statement".

Private Sub CmdSave_Enter()

Dim strselect As String
Dim strinsert As String
Dim flag As Boolean
Dim temp As String
Dim msgval As Integer


flag = True


'Below query Selects the recordset as per the conditions
strselect = "Select * FROM Errors where Date = date() AND Sub-Process='" & txtSub.Value & "'"


'Below Query inserts the Review time as a new record
strinsert = "INSERT INTO Errors (Date,LoginID,Sub-Process, Incorrect Data) values (date(),'" & Lgn & "', '" & txtSub.Value & "','" & txt1.Value & "')"



'Setting connection with different tables

Set cn = Application.CurrentProject.Connection 'Setting connection to the table Review_Time
Set rs = New ADODB.Recordset 'Selecting the records from the Review_Time Table
Set rs2 = New ADODB.Recordset 'For updating and inserting the records in the Review_Time Table


On Error GoTo errorhandler


errorhandler:

If flag = True Then

rs2.Open strinsert, cn, adOpenForwardOnly, adLockOptimistic

MsgBox "Reviewed Volume Added"

End If

Hope someone can guide me.Thank you in advance.

Hi

I don't think you can "open" an ACTION query.

Try something like this

CurrentProject.Connection.Execute strinsert, ,adExecuteNoRecords

??

MTB

[abie16]

Thanks for the immediate reply MTB but it still dont work.

[MikeTheBike]

Thanks for the immediate reply MTB but it still dont work.

OK, so do you have the same error ??

My only suggestion without more information would be to enclose all references to the Date FIELD in square brackets ie [Date].

Does the SELECT query work?

BTW don't use key words or function names for field/variable names, it might not end in tears, but it will probably give problems at some point (speaking from experience here - naughty boy!).

MTB