Inserting data from form into a table different from the form's ta

[Guest]

I have a form that loads data into a table. Once the form is updated, I want
to enter the data into another table as well the the form's default table. I
am using the after update event. My code is as follows:

Private Sub Form_AfterUpdate()
'Create a field to hold the sql code'
Dim sqlcode As String

'Build the sql code
sqlcode = "insert into inventory(itemcode, qty, cost, freightin, tax,
receiptdate)" & _
"values [forms]![freceipts]![itemcode]" & _
"[forms]![freceipts]![qty]" & _
"[forms]![freceipts]![cost]" & _
"[forms]![freceipts]![freightin]" & _
"[forms]![freceipts]![tax]" & _
"[forms]![freceipts]![receiptdate]"
'Insert a record
DoCmd.RunSQL sqlcode
End Sub

The sql code is not working and I haven't been able to debug it. I have
been running sql against Oracle, but am new to Access.

-
You do not have to do great things if you do small things with great love.
Mother Theresa

 

[Steve Schapel]

Ann,

You forgot the commas between the items in the values list, and also you
are trying to enter the references to the form controls as literal
values, and also you forget to but ()s around the values list. Try it
more like this...

sqlcode = "INSERT INTO inventory ( itemcode, qty, cost, freightin, tax,
receiptdate )" & _
" VALUES ( '" & Me.itemcode & "'," & Me.qty & "," & Me.cost & "," & _
Me.freightin & "," & Me.tax & ",#" & Me.receiptdate &
"# )"

I have assumed itemcode is text, receiptdate is a date, and the other
fields are numerical.

Having said that, I should also comment that saving the same data to two
separate locations is a very, very unusual thing to do.

 

[John Doe]

I have a form that loads data into a table. Once the form is updated, I want
to enter the data into another table as well the the form's default table. I
am using the after update event. My code is as follows:

Private Sub Form_AfterUpdate()
'Create a field to hold the sql code'
Dim sqlcode As String

'Build the sql code
sqlcode = "insert into inventory(itemcode, qty, cost, freightin, tax,
receiptdate)" & _
"values [forms]![freceipts]![itemcode]" & _
"[forms]![freceipts]![qty]" & _
"[forms]![freceipts]![cost]" & _
"[forms]![freceipts]![freightin]" & _
"[forms]![freceipts]![tax]" & _
"[forms]![freceipts]![receiptdate]"
'Insert a record
DoCmd.RunSQL sqlcode
End Sub

The sql code is not working and I haven't been able to debug it. I have
been running sql against Oracle, but am new to Access.

-
You do not have to do great things if you do small things with great love.
Mother Theresa

I thing this might help...

In the code above, there is a continuation line but there is not a
space between the words at the end of the one line and the beginning
of the next line. That is, you have this:

.... receiptdate)" & _
"values [forms]![freceipts]![itemcode]" & _

Which will get concatenated into the SQL string as follows:

.... receiptdate)values [forms]![freceipts]![itemcode]

Note that there needs to be a space before the word "values" or the
interpreter won't know what to do. This is true on each line.

So I think you need something like this"

.... receiptdate) " & _
"values [forms]![freceipts]![itemcode]" & _

(a space at the end of the first line) or else the following:

.... receiptdate)" & _
" values [forms]![freceipts]![itemcode]" & _

(a space at the beginning of the following line.)

I forget this a lot.