Need help finish SQL Insert Code:

[gold007eye]

Could someone please help me get this figured out? Any help would be greatly appreciated.

What I am trying to do is once the user clicks the "Save" button (thereby adding a new Bank Account to the system) I want to have a new record created in the "Bank Register" table automatically.

What I want the SQL code to do is to create a new record as Follows:

Date: Today's Date
Transaction Description "New Account"
Code: "NEW"
Account Number: Account # from the [Account Number] field on the "Bank
Account Information" (Which would still be open at the
time)

I have attached the code I am using below. My problem is that when I click save I am getting the following error message:

"Number of query values and destination fields are not the same"

Here is the list of fields in the "Bank Register" table that I am trying to add the new record to:

ID: (AutoNumber)
Cleared: (CheckBox)
Check Number: (TextBox)
Date:
Transaction Description:
Payment: (Currency)
Code:
Fees: (Currency)
Deposit: (Currency)
Sum: (Currency)
Account Number:

Private Sub Save_Click()
On Error GoTo Err_Save_Click

'---=== Create Dummy Record for New Account ===---
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "INSERT INTO [Bank Register]([Date],[Transaction Description],Code,[Account Number]) " & _
             "VALUES(Date() & 'New Account','NEW' & [Account Number])"
db.Execute MySQL, dbFailOnError
Set db = Nothing
'-------------------------------------------------

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
    
End Sub

 

[pbaldy]

Try

MySQL = "INSERT INTO [Bank Register]([Date],[Transaction Description],Code,[Account Number]) " & _
             "VALUES(Date(), 'New Account','NEW', " & [Account Number] & ")"

Date is not a good field name, as it can conflict with the built in Date function.

 

[gold007eye]

PERFECT! That did the trick. Thanks for the quick response.