SQL syntax for INSERT

G

Guest

Hello all,
I am trying to write a receipt number that is in a subform to a different
table. Problem is that if I reference the Form.subform.field.... it doesn't
work if there are muiltiple records in the subform.. so I'm trying to capture
the receipt number as a variable and then throw the variable in my SQL. I'm
getting an OVERFLOW error.


Here is my whole code... Any thoughts would be greatly appreciated.

'** CODE Start

On Error GoTo cmd_Cancel_Click_Error
Dim tempReceipt_Number As Integer

'Purpose-To record Receipt numbers that are canceled. This will show a
continuous
'count of receipt numbers in the Donations table. The word CANCELED will
appear in the notes field


Dim SQL As String

DoCmd.SetWarnings False

tempReceipt_Number = Me.Receipt_Number
'This captures correct number

SQL = "INSERT INTO Donations ([Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [tempReceipt_Number] & "AS [Receipt Number], " & _
"'Canceled' AS [Notes], " & _
"[Forms]![Opening]![EmployeeNum] AS [LastUserID];"

DoCmd.RunSQL SQL

MsgBox "Insert Complete for receipt " & Me.Receipt_Number
'This displays correct number

SQL = ""

'*** CODE CONTINUES for other things

Thanks again,
 
G

Guest

Still having problems, but I'm getting a step closer.

The code REM'd out works (2nd set), but I get a conversion error on code 1st
set of code.
In the table "Donations" here are the field properties.
[Donor ID Number], Number
[Receipt Number], Number
Notes, Memo
LastUserID, Text

** SQL Start

SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number], Notes,
LastUserID ) " & _
"SELECT [Forms]![temp].[Receipt_Number] AS [Donor ID Number], " & _
"[Forms]![temp].[Donor_ID_Number] AS [Receipt Number], " & _
"'Canceled' AS [Notes], " & _
"[Forms]![Opening]![EmployeeNum] AS [LastUserID];"



'SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number], Notes,
LastUserID ) " & _
' "SELECT 9111 AS [Donor ID Number], " & _
' "12345678 AS [Receipt Number], " & _
' "'Canceled' AS [Notes], " & _
' "[Forms]![Opening]![EmployeeNum] AS [LastUserID];"

'** SQL End
 
D

Douglas J. Steele

Try putting the form reference outside of the quotes. (and, for what it's
worth, you don't need to both with the Aliases in your SELECT query):

SQL = "INSERT INTO Donations ([Donor ID Number], " & _
"[Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [Forms]![temp].[Receipt_Number] & ", " & _
[Forms]![temp].[Donor_ID_Number] & ", " & _
"'Canceled', " & _
[Forms]![Opening]![EmployeeNum]

Both forms Temp and Opening must be open.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


David said:
Still having problems, but I'm getting a step closer.

The code REM'd out works (2nd set), but I get a conversion error on code
1st
set of code.
In the table "Donations" here are the field properties.
[Donor ID Number], Number
[Receipt Number], Number
Notes, Memo
LastUserID, Text

** SQL Start

SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number], Notes,
LastUserID ) " & _
"SELECT [Forms]![temp].[Receipt_Number] AS [Donor ID Number], " & _
"[Forms]![temp].[Donor_ID_Number] AS [Receipt Number], " & _
"'Canceled' AS [Notes], " & _
"[Forms]![Opening]![EmployeeNum] AS [LastUserID];"



'SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number], Notes,
LastUserID ) " & _
' "SELECT 9111 AS [Donor ID Number], " & _
' "12345678 AS [Receipt Number], " & _
' "'Canceled' AS [Notes], " & _
' "[Forms]![Opening]![EmployeeNum] AS [LastUserID];"

'** SQL End


David said:
Hello all,
I am trying to write a receipt number that is in a subform to a different
table. Problem is that if I reference the Form.subform.field.... it
doesn't
work if there are muiltiple records in the subform.. so I'm trying to
capture
the receipt number as a variable and then throw the variable in my SQL.
I'm
getting an OVERFLOW error.


Here is my whole code... Any thoughts would be greatly appreciated.

'** CODE Start

On Error GoTo cmd_Cancel_Click_Error
Dim tempReceipt_Number As Integer

'Purpose-To record Receipt numbers that are canceled. This will show a
continuous
'count of receipt numbers in the Donations table. The word CANCELED will
appear in the notes field


Dim SQL As String

DoCmd.SetWarnings False

tempReceipt_Number = Me.Receipt_Number
'This captures correct number

SQL = "INSERT INTO Donations ([Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [tempReceipt_Number] & "AS [Receipt Number], " & _
"'Canceled' AS [Notes], " & _
"[Forms]![Opening]![EmployeeNum] AS [LastUserID];"

DoCmd.RunSQL SQL

MsgBox "Insert Complete for receipt " & Me.Receipt_Number
'This displays correct number

SQL = ""

'*** CODE CONTINUES for other things

Thanks again,
Click to expand...
Click to expand...
 
G

Guest

Thank you Douglas for your response,
all goes well until I exceute my DoCmd.RunSQL SQL

the system input box of "Enter Parameter Value" appears, the text in the
dialog box is the LastUserID value from the [Forms]![Opening]![EmployeeNum]

Then if I enter in a value or no value, I get a error that the record was
not added due to key violations.
here is what I have, with all of my msgbox's to check each step. Thoughts?

'** CODE START
Dim SQL As String

'DoCmd.SetWarnings False

[Forms]![temp].[Receipt_Number] = Me.Receipt_Number
[Forms]![temp].[Donor_ID_Number] = Me.Donor_ID_Number

MsgBox "Before SQL"

SQL = "INSERT INTO Donations ([Donor ID Number], " & _
"[Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [Forms]![temp].[Receipt_Number] & ", " & _
[Forms]![temp].[Donor_ID_Number] & ", " & _
"'Canceled', " & _
[Forms]![Opening]![EmployeeNum]

MsgBox [Forms]![Opening]![EmployeeNum]

MsgBox SQL


'SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number], Notes,
LastUserID ) " & _
' "SELECT 9111 AS [Donor ID Number], " & _
' "12345678 AS [Receipt Number], " & _
' "'Canceled' AS [Notes], " & _
' "[Forms]![Opening]![EmployeeNum] AS [LastUserID];"


MsgBox "Before RUN SQL"

DoCmd.RunSQL SQL

'** CODE END







Douglas J. Steele said:
Try putting the form reference outside of the quotes. (and, for what it's
worth, you don't need to both with the Aliases in your SELECT query):

SQL = "INSERT INTO Donations ([Donor ID Number], " & _
"[Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [Forms]![temp].[Receipt_Number] & ", " & _
[Forms]![temp].[Donor_ID_Number] & ", " & _
"'Canceled', " & _
[Forms]![Opening]![EmployeeNum]

Both forms Temp and Opening must be open.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


David said:
Still having problems, but I'm getting a step closer.

The code REM'd out works (2nd set), but I get a conversion error on code
1st
set of code.
In the table "Donations" here are the field properties.
[Donor ID Number], Number
[Receipt Number], Number
Notes, Memo
LastUserID, Text

** SQL Start

SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number], Notes,
LastUserID ) " & _
"SELECT [Forms]![temp].[Receipt_Number] AS [Donor ID Number], " & _
"[Forms]![temp].[Donor_ID_Number] AS [Receipt Number], " & _
"'Canceled' AS [Notes], " & _
"[Forms]![Opening]![EmployeeNum] AS [LastUserID];"



'SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number], Notes,
LastUserID ) " & _
' "SELECT 9111 AS [Donor ID Number], " & _
' "12345678 AS [Receipt Number], " & _
' "'Canceled' AS [Notes], " & _
' "[Forms]![Opening]![EmployeeNum] AS [LastUserID];"

'** SQL End


David said:
Hello all,
I am trying to write a receipt number that is in a subform to a different
table. Problem is that if I reference the Form.subform.field.... it
doesn't
work if there are muiltiple records in the subform.. so I'm trying to
capture
the receipt number as a variable and then throw the variable in my SQL.
I'm
getting an OVERFLOW error.


Here is my whole code... Any thoughts would be greatly appreciated.

'** CODE Start

On Error GoTo cmd_Cancel_Click_Error
Dim tempReceipt_Number As Integer

'Purpose-To record Receipt numbers that are canceled. This will show a
continuous
'count of receipt numbers in the Donations table. The word CANCELED will
appear in the notes field


Dim SQL As String

DoCmd.SetWarnings False

tempReceipt_Number = Me.Receipt_Number
'This captures correct number

SQL = "INSERT INTO Donations ([Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [tempReceipt_Number] & "AS [Receipt Number], " & _
"'Canceled' AS [Notes], " & _
"[Forms]![Opening]![EmployeeNum] AS [LastUserID];"

DoCmd.RunSQL SQL

MsgBox "Insert Complete for receipt " & Me.Receipt_Number
'This displays correct number

SQL = ""

'*** CODE CONTINUES for other things

Thanks again,
Click to expand...
Click to expand...
Click to expand...
 
D

Dirk Goldgar

David said:
Thank you Douglas for your response,
all goes well until I exceute my DoCmd.RunSQL SQL

the system input box of "Enter Parameter Value" appears, the text in
the dialog box is the LastUserID value from the
[Forms]![Opening]![EmployeeNum]

Then if I enter in a value or no value, I get a error that the record
was not added due to key violations.
here is what I have, with all of my msgbox's to check each step.
Thoughts?

'** CODE START
Dim SQL As String

'DoCmd.SetWarnings False

[Forms]![temp].[Receipt_Number] = Me.Receipt_Number
[Forms]![temp].[Donor_ID_Number] = Me.Donor_ID_Number

MsgBox "Before SQL"

SQL = "INSERT INTO Donations ([Donor ID Number], " & _
"[Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [Forms]![temp].[Receipt_Number] & ", " & _
[Forms]![temp].[Donor_ID_Number] & ", " & _
"'Canceled', " & _
[Forms]![Opening]![EmployeeNum]

MsgBox [Forms]![Opening]![EmployeeNum]

MsgBox SQL


'SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number],
Notes, LastUserID ) " & _
' "SELECT 9111 AS [Donor ID Number], " & _
' "12345678 AS [Receipt Number], " & _
' "'Canceled' AS [Notes], " & _
' "[Forms]![Opening]![EmployeeNum] AS [LastUserID];"


MsgBox "Before RUN SQL"

DoCmd.RunSQL SQL

'** CODE END
Click to expand...

Sounds like LastUserID and [Forms]![Opening]![EmployeeNum] are text, not
numeric. In that case, you'll need to put quotes in the SQL string
around the value you pick up from the form:

SQL = "INSERT INTO Donations ([Donor ID Number], " & _
"[Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [Forms]![temp].[Receipt_Number] & ", " & _
[Forms]![temp].[Donor_ID_Number] & ", " & _
"'Canceled', " & _
Chr(34) & [Forms]![Opening]![EmployeeNum] & Chr(34)
 
G

Guest

Thank you Douglas and Dirk.

Dirk Goldgar said:
David said:
Thank you Douglas for your response,
all goes well until I exceute my DoCmd.RunSQL SQL

the system input box of "Enter Parameter Value" appears, the text in
the dialog box is the LastUserID value from the
[Forms]![Opening]![EmployeeNum]

Then if I enter in a value or no value, I get a error that the record
was not added due to key violations.
here is what I have, with all of my msgbox's to check each step.
Thoughts?

'** CODE START
Dim SQL As String

'DoCmd.SetWarnings False

[Forms]![temp].[Receipt_Number] = Me.Receipt_Number
[Forms]![temp].[Donor_ID_Number] = Me.Donor_ID_Number

MsgBox "Before SQL"

SQL = "INSERT INTO Donations ([Donor ID Number], " & _
"[Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [Forms]![temp].[Receipt_Number] & ", " & _
[Forms]![temp].[Donor_ID_Number] & ", " & _
"'Canceled', " & _
[Forms]![Opening]![EmployeeNum]

MsgBox [Forms]![Opening]![EmployeeNum]

MsgBox SQL


'SQL = "INSERT INTO Donations ([Donor ID Number], [Receipt Number],
Notes, LastUserID ) " & _
' "SELECT 9111 AS [Donor ID Number], " & _
' "12345678 AS [Receipt Number], " & _
' "'Canceled' AS [Notes], " & _
' "[Forms]![Opening]![EmployeeNum] AS [LastUserID];"


MsgBox "Before RUN SQL"

DoCmd.RunSQL SQL

'** CODE END
Click to expand...

Sounds like LastUserID and [Forms]![Opening]![EmployeeNum] are text, not
numeric. In that case, you'll need to put quotes in the SQL string
around the value you pick up from the form:

SQL = "INSERT INTO Donations ([Donor ID Number], " & _
"[Receipt Number], Notes, LastUserID ) " & _
"SELECT " & [Forms]![temp].[Receipt_Number] & ", " & _
[Forms]![temp].[Donor_ID_Number] & ", " & _
"'Canceled', " & _
Chr(34) & [Forms]![Opening]![EmployeeNum] & Chr(34)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL code to insert data from one table into another 5
Invalid SQL on INSERT 3
SQL "insert into" syntax 4
Help with VBA sql code 2
Using SQL Query Code in Form Module to Return Record Count 2
Two SQL statements in form -- UPDATE works; INSERT doesn't 2
SQL INSERT INTO Syntax 2
SQL Syntax Error 6

Top