Unbound Form "RunTime Error 3134" (1 Viewer)

haxan786

Registered User.
Local time
Today, 13:04
Joined
Jul 27, 2019
Messages
13
I have unbound form with insert date into two tables [DDL Master] & [DDL Detail]

on click data update is working but I am getting Runtime Error "3134" on new record on dbs.Execute (q2)

PHP:
Private Sub Command201_Click()

        Me.lblDate.Locked = False
        Me.Text142.Locked = False

    Dim rs As dao.Recordset
    Dim rs2 As dao.Recordset
    Dim dbs As Database

    Set dbs = CurrentDb

    'On Error Resume Next
    readingDate = lblDate.value
    q1 = "SELECT READINGID FROM [ddl master] where [reading date]=#" & readingDate & "#"
    Set rs = dbs.OpenRecordset(q1)

    If rs.EOF = False Then
        'UPDATE RECORD
        a = MsgBox("Do you want to update this record and overwrite previous data?", vbYesNo, "Thar Energy Ltd.")
        If a = 6 Then
            ReadingId = rs.Fields(0).value
                        i = 1
            Do While i < 25

                q1 = "SELECT * FROM [ddl detail] where TimeSlotId=" & i & " and [rEADINGiD]=" & ReadingId
                Set rs = dbs.OpenRecordset(q1)

                If rs.EOF = False Then
                    If IsNull(Me("txt" & i).value) Then
                        ddlreading = ""

                    Else
                        ddlreading = CInt(Me("txt" & i).value)
                        DDL = Me("L" & i).value
                        DDLF = Me("A" & i).value

                    End If
                    q1 = "update [DDL DETAIL] set [DDL Reading]=""" & ddlreading & """, [DDL]=""" & DDL & """, [DDLF]=""" & DDLF & """ where TimeSlotId=" & i & " and [rEADINGiD]=" & ReadingId


                    dbs.Execute (q1)
                    ddlreading = ""
                    DDL = ""
                    DDLF = ""


                Else
                    q1 = "INSERT INTO [DDL DETAIL] (readingid,timeslotid,[DDL Reading],[DDL],[DDLF]) values(" & ReadingId & "," & i & ",""" & ddlreading & """,""" & DDL & """,""" & DDLF & """ )"
                    dbs.Execute (q1)
'
                    
                End If
                i = i + 1
            Loop

    
            ddltime = Me("DDL Time").value
'            Option1.SetFocus
'            Option1.Enabled = True
'            Option1 = Me("DDL Remarks").value
'            Option1.Enabled = False
            cm = Me("DDL Comments").value
'            Option1 = Me("DDL Remarks").value
            q1 = "update [DDL Master] set [DDL By]=" & TempVars("EmployeeId") & ",[DDL Comments]='" & cm & "',[DDL Time]=#" & ddltime & "# where [rEADINGiD]=" & ReadingId
            'where before ,[DDL Remarks]='" & Option1 & "'
            dbs.Execute (q1)
            MsgBox "Record has been updated successfully!", vbOKOnly, "Thar Energy Ltd."
        End If
    Else
         'INSERT NEW RECORD
        ddltime = Me("DDL Time").value
        If ddltime = "" Then
                MsgBox "Please enter time"
                Me("DDL Time").SetFocus
                Exit Sub
            End If
       
            If IsNull(ddltime) Then
                MsgBox "Please enter time"
                Me("DDL Time").SetFocus
                Exit Sub
            End If
        cm = Me("DDL Comments").value
        ddlby = TempVars("EmployeeId")
        q1 = "INSERT INTO [DDL MASTER] ([Reading Date],[DDL Time],[DDL By],[DDL Comments]) values(#" & readingDate & "#,#" & ddltime & "#," & ddlby & ",'" & cm & "')"
        dbs.Execute (q1)
            
        q1 = "SELECT max(readingId) from [DDL Master]"
        Set rs = dbs.OpenRecordset(q1)

        i = 1
        Do While i < 25
            Me("txt" & i).SetFocus
            ddlreading = Me("txt" & i).value
'
            dd1 = Me("L" & i).value
            dd2 = Me("A" & i).value
            If ddlreading <> "" Then
               q2 = "INSERT INTO [DDL Detail] ([ReadingId],[TimeSlotId],[DDL Reading],[DDL],[DDLF]) values(" & ReadingId & "," & i & "," & CByte(ddlreading) & "," & dd1 & "," & dd2 & ")"
                dbs.Execute (q2)
            End If
            i = i + 1
        Loop
        MsgBox "Record has been saved!"
        Me.lblDate.Locked = True
        Me.Text142.Locked = True
    End If

End Sub
 

Micron

AWF VIP
Local time
Today, 05:04
Joined
Oct 20, 2018
Messages
3,476
In this situation, always debug.print the sql statement, copy from immediate window, paste into new query sql view and a) switch to datasheet view (which won't attempt to run it) or b) try to run it. Offending part usually gets highlighted in sql view. This assumes that you don't have a light bulb moment when you see the print out. Likely one or more missing quotes around a text value or # around dates.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:04
Joined
Oct 29, 2018
Messages
21,358
Hi. Sorry for being slow, but what is Error 3134?
 

Micron

AWF VIP
Local time
Today, 05:04
Joined
Oct 20, 2018
Messages
3,476
Hi. Sorry for being slow, but what is Error 3134?
syntax error in sql statement. I had to look it up because wouldn't you know, it's the only number I couldn't remember...;)
Wish so many people wouldn't assume we know what all of the numbers mean. There must be 7,000 of them.
 

Micron

AWF VIP
Local time
Today, 05:04
Joined
Oct 20, 2018
Messages
3,476
See? I didn't remember it correctly after all. Seems I can't recall it 20 minutes later, either.:eek:
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:04
Joined
Sep 21, 2011
Messages
14,050
syntax error in sql statement. I had to look it up because wouldn't you know, it's the only number I couldn't remember...;)
Wish so many people wouldn't assume we know what all of the numbers mean. There must be 7,000 of them.

Irks me as well. :D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:04
Joined
Oct 29, 2018
Messages
21,358
Runtime error 3134 "Syntax Error in INSERT INTO statement

this error popups when inserting new record
Hi. Thanks for the clarification. Since you have more than one INSERT statements in your code, can you please tell us which line is getting highlighted when you go to debug mode after seeing the error message?
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:04
Joined
Sep 21, 2011
Messages
14,050
Hi. Thanks for the clarification. Since you have more than one INSERT statements in your code, can you please tell us which line is getting highlighted when you go to debug mode after seeing the error message?

I must admit all that red is confusing me? :confused:

O/P stated q2?

As stated by Micron, a simple debug.print would probably show the error.?
 

isladogs

MVP / VIP
Local time
Today, 09:04
Joined
Jan 14, 2017
Messages
18,186
Hi. Sorry for being slow, but what is Error 3134?

Some time ago I created an app listing all Access error codes and descriptions.
This was partly because I got fed up by not knowing what a particular error number meant in forum questions like this.
See http://www.mendipdatasystems.co.uk/access-error-codes/4594398126 or https://www.access-programmers.co.uk/forums/showthread.php?t=294824



For anyone who cares, there are actually 2976 of them In A2010 but a few more in A2016/9

However when faced with questions like this, you can just type
Code:
err.raise 3134
into the immediate window and press enter.
Up pops a message box with the generic error description.
In this case its so generic its not very helpful but try e.g. Err.Raise 94 or err.raise 13 etc.
 

Attachments

  • Capture.PNG
    Capture.PNG
    15.9 KB · Views: 334
Last edited:

Micron

AWF VIP
Local time
Today, 05:04
Joined
Oct 20, 2018
Messages
3,476
This was partly because I got fed up by not knowing what a particular error number meant in forum questions like this.
My point is we shouldn't have to. If I'm in one of my moods and there's (e.g.) 100 lines of code and no code tags or an error number with no description, I just move on.
 

haxan786

Registered User.
Local time
Today, 13:04
Joined
Jul 27, 2019
Messages
13
Hi. Thanks for the clarification. Since you have more than one INSERT statements in your code, can you please tell us which line is getting highlighted when you go to debug mode after seeing the error message?

I finally got it. There was missing required field in the statement.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:04
Joined
Oct 29, 2018
Messages
21,358
I finally got it. There was missing required field in the statement.
Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:04
Joined
Sep 21, 2011
Messages
14,050
I finally got it. There was missing required field in the statement.

Care to post the correct statement as I thought you had 5 fields being updated and had 5 values.

Code:
q2 = "INSERT INTO [DDL Detail] ([ReadingId],[TimeSlotId],[DDL Reading],[DDL],[DDLF]) values(" & ReadingId & "," & i & "," & CByte(ddlreading) & "," & dd1 & "," & dd2 & ")"
 

Users who are viewing this thread

Top Bottom