Recordset/Insert Into SQL

[Jameo]

Hi all,

I am having a slight issue. I am trying to create an Insert Into SQL statement, and keep getting errors. I have managed to insert into my table using a recordset, but ideally would like to use an SQL statement. My working recordset statement is as follows:

Private Sub cmd_AddDF_Click()
  Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  Set MyDB = CurrentDb()
  Set MyRS = MyDB.OpenRecordset("tblDemandForcast", dbOpenDynaset)

On Error GoTo Err_cmd_AddDF_Click
If Not IsNull(DLookup("[GasDay]", _
                  "[tblDemandForcast]", _
                  "[GasDay]=#" & Me!txtRepDate & "#")) Then
                  
                  
MsgBox("Record already Exists.")
 
exit sub
 
else

    With MyRS
      .AddNew
           ![Gasday] = Me![txtRepDate]
           ![D] = Me![txtD0]
           ![D1] = Me![txtD1]
           ![D2] = Me![txtD2]
           ![D3] = Me![txtD3]
           ![D4] = Me![txtD4]
           ![D5] = Me![txtD5]
           ![D6] = Me![txtD6]
      .Update
    End With
  MyRS.Close
End If

Exit_cmd_AddDF_Click:
    Exit Sub
Err_cmd_AddDF_Click:
    MsgBox Err.Description
    Resume Exit_cmd_AddDF_Click
    
End Sub

The SQL statement I have been trying to use is as follows:

DoCmd.RunSQL "INSERT INTO [tblDemandForcast] " & _
                 "([GasDay] & ", " & [D] & ", " & [D1] & ", " & [D2] & ", " & [D3] & ", " & [D4] & ", " & [D5] & ", " & [D6]) " & _
                 "VALUES ('" & Me.txtRepDate & "','" & Me.txtD0 & "'," & Me.txtD1 & ", '" & Me.txtD2 & "','" & Me.txtD3 & "','" & Me.txtD4 & "','" & Me.txtD5 & "','" & Me.txtD6 & "')"

I must have the syntax wrong in the above statement, but after many hours and iterations I am no closer to having it working. I have tried with and without ## around the date txt box, and have tried using format() but to no avail. It would be great if someone could point me in the direction of my errors.

Many thanks

 

[SydneyGeek]

Try creating the SQL string first, then you can Debug.Print before trying to run it.
Something like:

Dim sSQL As String
 
... 
sSQL="Build the SQL statement here"
Debug.Print sSQL
DoCmd.RunSQL sSQL
...

Then you can check the syntax in the Immediate window
Denis

 

[Norie]

Did you try any of the suggestions I posted in the other thread?

You shouldn't have single apostrophes around the date.

Another thing I just noticed - you seem to be using [D], [D1]... as the names of the fields in the first part of the SQL.

I'm not sure how those will be interpreted by Access/VBA, but it's not going to be D, D1, D2...

If the field names are D, D1, D2 etc try this.

Dim strSQL As String
 
strSQL = "INSERT INTO [tblDemandForcast] ([GasDay],[D], [D1] , [D2] , [D3], [D4] , [D5] , [D6]) "
strSQL = strSQL & "VALUES (" & DateValue(Me.txtRepDate) & "," & _
                                    "'" & Me.txtD0 & "' ,'" & Me.txtD1 & "', '" & Me.txtD2 & "','" & Me.txtD3 & "','" & Me.txtD4 & "','" & Me.txtD5 & "','" & Me.txtD6 & "')"

 

[Jameo]

Hi Norrie,

Thanks, I did try your advice, but I couldn't get it working. I decided to post a new thread as I had got it working by using the recordset technique. I thought this would provide a good comparison between the two sets of code. As you will have noticed, I also changed all of the field headings to avoid any of the possible pitfalls you mentioned.

I have managed to get it working now though. My issue was that I didn't understand all the syntax involved in building up the string, and even though I tried to put the commas in speach marks, I think I was doing it incorrectly.

After much effort, pain and misery I finally found out the correct methodolgy, (thanks Sydney for suggesting the debug.print technique - really helped)

My SQL statement is now as follows:

Private Sub cmd_AddDF_Click()
    Dim SQLs As String, SQLss As String
    Dim MSG1 As Integer
 
    DoCmd.SetWarnings False
 
    SQLs = "INSERT INTO tblDemandForcast([GasDay],[D],[D1],[D2],[D3],[D4],[D5],[D6])" _
            & "VALUES (# " & Me.txtRepDate & " #, " & Me.txtD0 & ", " & Me.txtD1 & ", " _
            & Me.txtD2 & ", " & Me.txtD3 & "," & Me.txtD4 & "," & Me.txtD5 & "," & Me.txtD6 & ")"
    SQLss = "UPDATE tblDemandForcast SET [GasDay] = #" & Me.txtRepDate & "#, [D] = " _
            & Me.txtD0 & ", [D1] = " & Me.txtD1 & ", [D2] = " & Me.txtD2 & ", [D3]" _
            & "= " & Me.txtD3 & ", [D4] = " & Me.txtD4 & ", [D5] = " & Me.txtD5 & ", [D6] = " _
            & Me.txtD6 & " WHERE [GasDay]= #" & Me.txtRepDate & "#"
 
 
    If IsNull(DLookup("[GasDay]", _
                      "[tblDemandForcast]", _
                      "[GasDay]=#" & Me!txtRepDate & "#")) Then
 
        DoCmd.RunSQL SQLs
 
    Else
 
        MSG1 = MsgBox("Record already exists! Do you wish to update with new values?", vbYesNo)
 
        If MSG1 = vbYes Then
 
            DoCmd.RunSQL SQLss
 
        Else
 
            Exit Sub
        End If
 
    End If
   DoCmd.SetWarnings True
End Sub
Sub test()
Dim SQLs As String
 
Debug.Print SQLs
 
 
End Sub

Basically, I was offered the correct advice, but didn't have the intelligance to correctly interpret it

Thanks to the both of your for your patience.

For those in the future reading this and suffering the same problems, if you dont put put " & around a variable VBA does not interpret is a variable, but as a string! " & MyVariable & " is the way to go!