CODEWITHSUNDEEP
ms-accessvbasql-insert
IT_User
IT_User

Reputation: 779

Insert statement in access gives error

I have been working on creating an extremely simple database with only 4 tables and only a few pieces of information per column. One of my tables is called "Customer" and inside of this table there or 4 columns for information. enter image description here

I have a button on my "AddCustomerForm" that runs the following command

Private Sub cmdadd_Click()
CurrentDb.Execute "INSERT INTO Customer(Customer ID, Email, Identifier) " & _
 = VALUES(Customer ID, Email, Identifier)  
End Sub

My Add customer form looks like this: enter image description here

Could someone please point out what I am messing up? The error I receive is :

Syntax error.

Upvotes: 0

Views: 86

Answers (2)

dbmitch
dbmitch

Reputation: 5386

There's a few issues I see - is [Customer ID] an autonumber field? If so don't include it.

Also - if you're running a Manual Insert I assume your form is NOT bound to your table, though I begin to wonder why Customer ID is shown on the form as being editable?

Finally it looks like Location is a numeric ID belonging to ID field of the Location dropdown that fills in the Business ID field

This will help you debug your SQL and show us what's wrong

Add it to your button and show us the value shown in Immediate Window when the code halts

    Dim strSQL as string
    strSQL = "INSERT INTO Customer ([Customer ID], Email, Identifier) VALUES (" _
    & me.[Customer ID] & ",""" & Me.[Email] & """,""" & Me.[Identifier] & """)"
    Debug.print strSQL

    CurrentDb.Execute strSQL

If Customer ID is AutoNumber try this instead (assuming form is UNBOUND) and Location is ID value of first column of dropdown

    Dim strSQL as string
    strSQL = "INSERT INTO Customer (Email, Identifier) VALUES (" _
    & me.[Customer ID] & ",""" & Me.[Email] & """, & Me.[Identifier] & ")"
    Debug.print strSQL

    CurrentDb.Execute strSQL

Upvotes: 3

MoondogsMaDawg
MoondogsMaDawg

Reputation: 1714

Private Sub cmdadd_Click()
CurrentDb.Execute "INSERT INTO Customer ([Customer ID], Email, Identifier) VALUES([Forms]![MyFormName]![CustomerIDTextboxName], [Forms]![MyFormName]![EmailtextboxName], [Forms]![MyFormName]![IdentifierTextboxName]);"  
End Sub

Access requires brackets around any field name with a space. I also deleted the = before VALUES and changed the values to reference your form controls, which you will have to name appropriately. You also need a semi-colon to complete the statement and need to close your double-quotes.

This page might help with syntax.

Upvotes: 1

Related Questions