Microsoft Access VBA INSERT SQL statement

Question

I have created a database within Microsoft access and with it a form.
I have a button attached to the form and when it's clicked I want to insert a new record into the table customerHeader.
There are four fields within that table one being an autonumber. In my SQL statement, I don't include the orderNumber since it is an autofield.
When I try to click the button and the on click event is executed, I get an error saying that Microsoft Access cannot append the records in the append query.

Any ideas, I have looked everywhere and I have not been able to find the solution.

Private Sub addOrder_Click()
    Dim mySql As String
    Dim rowNum As Integer
    Dim recCount As Long
    Dim orderNumber As Long
    Dim myBool As Boolean


    Dim todayDate As Date

    todayDate = CDate(Date)

    myBool = False


    MsgBox todayDate

    rowNum = Form.CurrentRecord
    rowNum = CInt(rowNum - 1)



    'DoCmd.GoToRecord , , acNewRec
    mySql = "INSERT INTO orderHeader (orderDate,custNumber,printed) VALUES (" & todayDate & "," & rowNum & "," & myBool & ")"

    DoCmd.RunSQL mySql

    Me!orderNum.Requery

On its own the SQL does work, although you may want to add # around the date - (#" & todayDate & "#," (you may also need to muck around with the date formatting depending on your locality - I hate dates in Access). What's the exact message it gives? Usually it says - MS Access set x field(s) to Null due to a type conversion failure, and it didn't add x record(s)..... — Darren Bartrup-Cook
It specifically says "Microsoft access set 0 field(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations". I will try to add # to the date — Ryan Wing

Gustav Gustav · Accepted Answer · 2017-04-07T14:26:02

You don't have to much around - just use the ISO sequence for the format of the string expression for the date value:

mySql = "INSERT INTO orderHeader (orderDate,custNumber,printed) VALUES (#" & Format(todayDate, "yyyy\/mm\/dd") & "#," & rowNum & "," & myBool & ")"

However, it it's today, simply use:

mySql = "INSERT INTO orderHeader (orderDate,custNumber,printed) VALUES (Date()," & rowNum & "," & myBool & ")"

Microsoft Access VBA INSERT SQL statement

1 Answers