Using a SQL 'INSERT INTO' statement with a table having an AutoNumber Field

  • Thread starter Thread starter Jack G
  • Start date Start date
J

Jack G

I have a very simple table with just 2 columns: Employee-ID and Employee.
The first field is Autonumber Text. I am wanting to programatically add
records to this table and I tried to use this:

SQLstring = "INSERT INTO EMPTABLE ([Employee]) " & "VALUES ('" & NewName &
"');"
DoCmd.RunSQL SQLstring

where NewName is a string that was defined earlier.

I couldn't get this to work and finally decided it must because I need to
say what BOTH fields will be, not just the Employee field. If that's right,
then I don't know what to say for the first field which is an AutoNumber
field assigned by Access:

INSERT INTO EMPTABLE ([Employee-ID]) ([Employee]) " & "VALUES ('" & ???? &
NewName & "');

Any help would be appreciated.

Jack
 
Your first statement looks correct

SQLstring = "INSERT INTO EMPTABLE ([Employee]) VALUES ('" & NewName & "');"

What error do you get when you attempt to do this?

What is the value of NEWName? Is it null? Heck, what is NewName? Is it
the name of a control on a form? A variable that you have in your code
module?

If Employee-Id is an autonumber field, then it is not a text field , it is
storing unique numbers - normally of the type Long.
 
John, thanks for answering.

This is part of a procedure that reacts to the "Not in List" event when
typing in an unknown name in a combo box (with thanks to Martin Green). The
error I get is:

Run-time error '3134':
Syntex error in INSERT INTO statement

Here is the whole thing (the table name is 'Staff' and the fields are
'ProjectMgr-ID' and 'ProjectMgr'):

Private Sub ProjectMgr_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim SQLstring As String
Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")
If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Else
Response = acDataErrContinue
End If
End Sub

My first thought was I needed supply values for BOTH fields in my INSERT
INTO statement - but I couldn't find any help file or post to confirm or
debunk that. And if I DO need both, I don't know how to handle an
AutoNumber field anyway.

Jack


John Spencer said:
Your first statement looks correct

SQLstring = "INSERT INTO EMPTABLE ([Employee]) VALUES ('" & NewName &
"');"

What error do you get when you attempt to do this?

What is the value of NEWName? Is it null? Heck, what is NewName? Is
it the name of a control on a form? A variable that you have in your code
module?

If Employee-Id is an autonumber field, then it is not a text field , it is
storing unique numbers - normally of the type Long.

Jack G said:
I have a very simple table with just 2 columns: Employee-ID and Employee.
The first field is Autonumber Text. I am wanting to programatically add
records to this table and I tried to use this:

SQLstring = "INSERT INTO EMPTABLE ([Employee]) " & "VALUES ('" & NewName
& "');"
DoCmd.RunSQL SQLstring

where NewName is a string that was defined earlier.

I couldn't get this to work and finally decided it must because I need to
say what BOTH fields will be, not just the Employee field. If that's
right, then I don't know what to say for the first field which is an
AutoNumber field assigned by Access:

INSERT INTO EMPTABLE ([Employee-ID]) ([Employee]) " & "VALUES ('" & ????
& NewName & "');

Any help would be appreciated.

Jack
Click to expand...
Click to expand...
 
Autonumber field can only update itself.

try:
SQLstring = "INSERT INTO EMPTABLE ([Employee]) VALUES ('" & NewName &
"');"
CurrentDB.execute SQLstring, dbFailonerror
 
I added CurrentDB.execute SQLstring, dbFailonerror and I get the same
error message: "Run-time error '3134': Syntex error in INSERT INTO
statement". The Debugger indicated it was bombing on the new line and since
it looked a little strange to me, I added parentheses to get
CurrentDB.execute (SQLstring, dbFailonerror ) and then it told me "Compile
error: Expected: =" . According to the Debugger, my NewName and SQLstring
are populated as expected (no nulls) .

Any other ideas??

Jack
 
I really don't see anything wrong with the code. I've added the response =
acDataErrAdded (Hope I remembered that correctly)
and changed your single quotes to double quotes.

Private Sub ProjectMgr_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim SQLstring As String

Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")

If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES (""" & NewData & """) "
'Add these two lines for debugging
Debug.Print SQLString
Stop
' Copy and paste the resulting string into a blank query and see if it runs.
' If it gives you a syntax error, it may tell you the location of the syntax
error.

' Comment out the next line until you see what is happening.
' DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Jack G said:
John, thanks for answering.

This is part of a procedure that reacts to the "Not in List" event when
typing in an unknown name in a combo box (with thanks to Martin Green).
The error I get is:

Run-time error '3134':
Syntex error in INSERT INTO statement

Here is the whole thing (the table name is 'Staff' and the fields are
'ProjectMgr-ID' and 'ProjectMgr'):

Private Sub ProjectMgr_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim SQLstring As String
Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")
If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Else
Response = acDataErrContinue
End If
End Sub

My first thought was I needed supply values for BOTH fields in my INSERT
INTO statement - but I couldn't find any help file or post to confirm or
debunk that. And if I DO need both, I don't know how to handle an
AutoNumber field anyway.

Jack


John Spencer said:
Your first statement looks correct

SQLstring = "INSERT INTO EMPTABLE ([Employee]) VALUES ('" & NewName &
"');"

What error do you get when you attempt to do this?

What is the value of NEWName? Is it null? Heck, what is NewName? Is
it the name of a control on a form? A variable that you have in your
code module?

If Employee-Id is an autonumber field, then it is not a text field , it
is storing unique numbers - normally of the type Long.

Jack G said:
I have a very simple table with just 2 columns: Employee-ID and Employee.
The first field is Autonumber Text. I am wanting to programatically add
records to this table and I tried to use this:

SQLstring = "INSERT INTO EMPTABLE ([Employee]) " & "VALUES ('" & NewName
& "');"
DoCmd.RunSQL SQLstring

where NewName is a string that was defined earlier.

I couldn't get this to work and finally decided it must because I need
to say what BOTH fields will be, not just the Employee field. If that's
right, then I don't know what to say for the first field which is an
AutoNumber field assigned by Access:

INSERT INTO EMPTABLE ([Employee-ID]) ([Employee]) " & "VALUES ('" &
???? & NewName & "');

Any help would be appreciated.

Jack
Click to expand...
Click to expand...
Click to expand...
 
Eureka!

Unfortunately I had posted a slightly simplified version of my code -- the
actual table name was something like Acme-Staff, and it turns out that
Access just didn't like that hyphen! Thanks for teaching me something about
the debugger - I never (never!) would have found it without that suggestion!

Jack

John Spencer said:
I really don't see anything wrong with the code. I've added the response =
acDataErrAdded (Hope I remembered that correctly)
and changed your single quotes to double quotes.

Private Sub ProjectMgr_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim SQLstring As String

Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")

If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES (""" & NewData & """) "
'Add these two lines for debugging
Debug.Print SQLString
Stop
' Copy and paste the resulting string into a blank query and see if it
runs.
' If it gives you a syntax error, it may tell you the location of the
syntax error.

' Comment out the next line until you see what is happening.
' DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Jack G said:
John, thanks for answering.

This is part of a procedure that reacts to the "Not in List" event when
typing in an unknown name in a combo box (with thanks to Martin Green).
The error I get is:

Run-time error '3134':
Syntex error in INSERT INTO statement

Here is the whole thing (the table name is 'Staff' and the fields are
'ProjectMgr-ID' and 'ProjectMgr'):

Private Sub ProjectMgr_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim SQLstring As String
Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")
If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Else
Response = acDataErrContinue
End If
End Sub

My first thought was I needed supply values for BOTH fields in my INSERT
INTO statement - but I couldn't find any help file or post to confirm or
debunk that. And if I DO need both, I don't know how to handle an
AutoNumber field anyway.

Jack


John Spencer said:
Your first statement looks correct

SQLstring = "INSERT INTO EMPTABLE ([Employee]) VALUES ('" & NewName &
"');"

What error do you get when you attempt to do this?

What is the value of NEWName? Is it null? Heck, what is NewName? Is
it the name of a control on a form? A variable that you have in your
code module?

If Employee-Id is an autonumber field, then it is not a text field , it
is storing unique numbers - normally of the type Long.

I have a very simple table with just 2 columns: Employee-ID and
Employee. The first field is Autonumber Text. I am wanting to
programatically add records to this table and I tried to use this:

SQLstring = "INSERT INTO EMPTABLE ([Employee]) " & "VALUES ('" &
NewName & "');"
DoCmd.RunSQL SQLstring

where NewName is a string that was defined earlier.

I couldn't get this to work and finally decided it must because I need
to say what BOTH fields will be, not just the Employee field. If
that's right, then I don't know what to say for the first field which
is an AutoNumber field assigned by Access:

INSERT INTO EMPTABLE ([Employee-ID]) ([Employee]) " & "VALUES ('" &
???? & NewName & "');

Any help would be appreciated.

Jack
Click to expand...
Click to expand...
Click to expand...
 
Trying not to be patronising here Jack, so please excuse me if this comes
over badly.

1) In future please post your actual code as John or mcnews would probably
have spotted the hyphen as an error straightaway.
2) In table or field names it is best to just use alphanumeric and the _
character and avoid keywords (such as Date).
3) If you do come up against a name which breaks the naming conventions
remember to put square brackets [] around it.


--

Terry Kreft


Jack G said:
Eureka!

Unfortunately I had posted a slightly simplified version of my code -- the
actual table name was something like Acme-Staff, and it turns out that
Access just didn't like that hyphen! Thanks for teaching me something about
the debugger - I never (never!) would have found it without that suggestion!

Jack

John Spencer said:
I really don't see anything wrong with the code. I've added the response =
acDataErrAdded (Hope I remembered that correctly)
and changed your single quotes to double quotes.

Private Sub ProjectMgr_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim SQLstring As String

Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")

If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES (""" & NewData & """) "
'Add these two lines for debugging
Debug.Print SQLString
Stop
' Copy and paste the resulting string into a blank query and see if it
runs.
' If it gives you a syntax error, it may tell you the location of the
syntax error.

' Comment out the next line until you see what is happening.
' DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Jack G said:
John, thanks for answering.

This is part of a procedure that reacts to the "Not in List" event when
typing in an unknown name in a combo box (with thanks to Martin Green).
The error I get is:

Run-time error '3134':
Syntex error in INSERT INTO statement

Here is the whole thing (the table name is 'Staff' and the fields are
'ProjectMgr-ID' and 'ProjectMgr'):

Private Sub ProjectMgr_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim SQLstring As String
Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")
If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Else
Response = acDataErrContinue
End If
End Sub

My first thought was I needed supply values for BOTH fields in my INSERT
INTO statement - but I couldn't find any help file or post to confirm or
debunk that. And if I DO need both, I don't know how to handle an
AutoNumber field anyway.

Jack


Your first statement looks correct

SQLstring = "INSERT INTO EMPTABLE ([Employee]) VALUES ('" & NewName &
"');"

What error do you get when you attempt to do this?

What is the value of NEWName? Is it null? Heck, what is NewName? Is
it the name of a control on a form? A variable that you have in your
code module?

If Employee-Id is an autonumber field, then it is not a text field , it
is storing unique numbers - normally of the type Long.

I have a very simple table with just 2 columns: Employee-ID and
Employee. The first field is Autonumber Text. I am wanting to
programatically add records to this table and I tried to use this:

SQLstring = "INSERT INTO EMPTABLE ([Employee]) " & "VALUES ('" &
NewName & "');"
DoCmd.RunSQL SQLstring

where NewName is a string that was defined earlier.

I couldn't get this to work and finally decided it must because I need
to say what BOTH fields will be, not just the Employee field. If
that's right, then I don't know what to say for the first field which
is an AutoNumber field assigned by Access:

INSERT INTO EMPTABLE ([Employee-ID]) ([Employee]) " & "VALUES ('" &
???? & NewName & "');

Any help would be appreciated.

Jack
Click to expand...
Click to expand...
Click to expand...
 
try putting the statement on one line.
you may have syntax error that you are not seeing.
you have to have a space before your new line character _ for example.
 
Thanks, Terry.

I pasted in my code exactly, but then decided just before hitting the Post
button that there was no reason to include the company name, so I just
deleted that tiny piece -- little did I know! (As a side rant, I might have
expected Access to pop up a warning somewhere along the way about not using
those characters). One more lesson learned.

Jack

Terry Kreft said:
Trying not to be patronising here Jack, so please excuse me if this comes
over badly.

1) In future please post your actual code as John or mcnews would probably
have spotted the hyphen as an error straightaway.
2) In table or field names it is best to just use alphanumeric and the _
character and avoid keywords (such as Date).
3) If you do come up against a name which breaks the naming conventions
remember to put square brackets [] around it.


--

Terry Kreft


Jack G said:
Eureka!

Unfortunately I had posted a slightly simplified version of my code --
the
actual table name was something like Acme-Staff, and it turns out that
Access just didn't like that hyphen! Thanks for teaching me something about
the debugger - I never (never!) would have found it without that suggestion!

Jack

John Spencer said:
I really don't see anything wrong with the code. I've added the response =
acDataErrAdded (Hope I remembered that correctly)
and changed your single quotes to double quotes.

Private Sub ProjectMgr_NotInList(NewData As String, Response As
Integer)
Dim Answer As Integer
Dim SQLstring As String

Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")

If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES (""" & NewData & """) "
'Add these two lines for debugging
Debug.Print SQLString
Stop
' Copy and paste the resulting string into a blank query and see if it
runs.
' If it gives you a syntax error, it may tell you the location of the
syntax error.

' Comment out the next line until you see what is happening.
' DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

John, thanks for answering.

This is part of a procedure that reacts to the "Not in List" event
when
typing in an unknown name in a combo box (with thanks to Martin
Green).
The error I get is:

Run-time error '3134':
Syntex error in INSERT INTO statement

Here is the whole thing (the table name is 'Staff' and the fields are
'ProjectMgr-ID' and 'ProjectMgr'):

Private Sub ProjectMgr_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim SQLstring As String
Answer = MsgBox(Chr(34) & NewData & Chr(34) & " isn't listed in the Staff
Table." _
& vbCrLf & "If you're sure you didn't make a typo," _
& vbCrLf & "do you want to add it to the Staff List now?" _
, vbQuestion + vbYesNo, "Name Not Found")
If Answer = vbYes Then
SQLstring = "INSERT INTO Staff ([ProjectMgr]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLstring
DoCmd.SetWarnings True
Else
Response = acDataErrContinue
End If
End Sub

My first thought was I needed supply values for BOTH fields in my INSERT
INTO statement - but I couldn't find any help file or post to confirm or
debunk that. And if I DO need both, I don't know how to handle an
AutoNumber field anyway.

Jack


Your first statement looks correct

SQLstring = "INSERT INTO EMPTABLE ([Employee]) VALUES ('" & NewName &
"');"

What error do you get when you attempt to do this?

What is the value of NEWName? Is it null? Heck, what is NewName? Is
it the name of a control on a form? A variable that you have in your
code module?

If Employee-Id is an autonumber field, then it is not a text field , it
is storing unique numbers - normally of the type Long.

I have a very simple table with just 2 columns: Employee-ID and
Employee. The first field is Autonumber Text. I am wanting to
programatically add records to this table and I tried to use this:

SQLstring = "INSERT INTO EMPTABLE ([Employee]) " & "VALUES ('" &
NewName & "');"
DoCmd.RunSQL SQLstring

where NewName is a string that was defined earlier.

I couldn't get this to work and finally decided it must because I need
to say what BOTH fields will be, not just the Employee field. If
that's right, then I don't know what to say for the first field
which
is an AutoNumber field assigned by Access:

INSERT INTO EMPTABLE ([Employee-ID]) ([Employee]) " & "VALUES ('" &
???? & NewName & "');

Any help would be appreciated.

Jack
Click to expand...
Click to expand...
Click to expand...
 
Back
Top