Add records using VBA - Access 2007

[mburch2000]

I am using a form to add records to a table named "MASTER LIST". This forms pulls up existing records, allows the user to change certain fields, and then the user clicks an Add button. The Add button is suppose to create a new record based on the information on the form. Basically I am creating a new record based on the old record, but with certain fields updated. The Add button is driven by VBA code, see below, but it is not creating a new record.

Private Sub Add_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    On Error Resume Next
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    CurrentDb.Execute "INSERT INTO [MASTER LIST] (" _
        & "(Policy_Number," _
        & "(Title," _
        & "(Policy_Year," _
        & "(Schedule," _
        & "(Last_MPRM_ReviewDate," _
        & "(Next_MPRM_ReviewDate," _
        & "(Last_FEP_ReviewDate," _
        & "(Next_FEP_ReviewDate," _
        & "(Comments," _
        & "(FEP_PolicyStatement," _
        & "(Policy_HistoryChanges," _
        & "(Orig_BlueWeb_PubDate," _
        & "(Last_BlueWeb_PubDate," _
        & "(Orig_FEPBlueOrg_PubDate," _
        & "(Last_FEPBlueOrg_PubDate," _
        & "(Keywords) VALUES " _
        & "('" & Policy_Number.Value & "','" & Title.Value & "'," & Policy_Year.Value & ",'" & Schedule.Value & "','" & Last_MPRM_ReviewDate.Value & "','" & Next_MPRM_ReviewDate.Value & "','" & Last_FEP_ReviewDate.Value & "','" & Next_FEP_ReviewDate.Value & "','" & Comments.Value & "','" & FEP_PolicyStatement.Value & "','" & Policy_HistoryChanges.Value & "',#" & Format(Orig_BlueWeb_PubDate.Value, "mm/dd/yyyy") & "#,#" & Format(Last_BlueWeb_PubDate.Value, "mm/dd/yyyy") & "#,#" & Format(Orig_FEPBlueOrg_PubDate.Value, "mm/dd/yyyy") & "#,#" & Format(Last_FEPBlueOrg_PubDate.Value, "mm/dd/yyyy") & "#,'" & Keywords.Value & "');"
 
    rs.Close
    db.Close
    DoCmd.Close
 
End Sub 

Most fields are text, there is one field (Policy_Year) that is a number, and four fields at the end that are date fields. Can someone tell me if this code has an issue?

Thanks

[Rabbit]

You have a bunch of extraneous parentheses.

The insert syntax is:

INSERT INTO tableName (
   Field1,
   Field2,
   Field3
) VALUES (
   ...
)

What you have is:

INSERT INTO tableName (
   (Field1,
   (Field2,
   (Field3
) VALUES (
   ...
)

[mburch2000]

Hey Rabbit,

I tried, but my code still didn't work. I decided to try it a better way and it WORKED! The code is much cleaner, see below and thanks for your help.

Private Sub Add_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
 
    On Error Resume Next
 
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [MASTER LIST]")
    rs.AddNew
    rs![Policy_Number] = Me.Policy_Number.Value
    rs![Title] = Me.Title.Value
    rs![Policy_Year] = Me.Policy_Year.Value
    rs![Schedule] = Me.Schedule.Value
    rs![Last_MPRM_ReviewDate] = Me.Last_MPRM_ReviewDate.Value
    rs![Next_MPRM_ReviewDate] = Me.Next_MPRM_ReviewDate.Value
    rs![Last_FEP_ReviewDate] = Me.Last_FEP_ReviewDate.Value
    rs![Next_FEP_ReviewDate] = Me.Next_FEP_ReviewDate.Value
    rs![Comments] = Me.Comments.Value
    rs![FEP_PolicyStatement] = Me.FEP_PolicyStatement.Value
    rs![Policy_HistoryChanges] = Me.Policy_HistoryChanges.Value
    rs![Orig_BlueWeb_PubDate] = Me.Orig_BlueWeb_PubDate.Value
    rs![Last_BlueWeb_PubDate] = Me.Last_BlueWeb_PubDate.Value
    rs![Orig_FEPBlueOrg_PubDate] = Me.Orig_FEPBlueOrg_PubDate.Value
    rs![Last_FEPBlueOrg_PubDate] = Me.Last_FEPBlueOrg_PubDate.Value
    rs![Keywords] = Me.Keywords.Value
    rs.Update
 
    rs.Close
    Set rs = Nothing
    db.Close
    DoCmd.Close
End Sub 

[ADezii]

Be careful with the

On Error Resume Next

on Code Line# 5. Out of curiosity why are you using this approach instead of an Error Trap?

[NeoPa]

I have reset the Best Answer post because :

1. We generally discourage OPs (Original Posters - Those who ask the question) from awarding themselves the Best Answer because it's just too easy. Unless they come up with an appropriate answer that no-one else has helped towards. Otherwise it's a bit like cheating.
2. The answer must be relevant to the question. An alternative solution that doesn't actually deal with the originally reported problem can be a valid and helpful post, but will normally not be considered as Best Answer.

Rabbit's post, on the other hand, deals directly with the underlying problem originally reported as the question of this thread and so is perfectly appropriate.