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)
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