SQL Insert into

[jamin14]

I want to use insert into attached to one of my buttons to transfer from one subform to another. has any1 got any example codes because mine just come up with errors

thank you

[pbaldy]

Why don't you post your code and we'll fix it?

[jamin14]

Private Sub Command22_Click()

"InsertInto F_Order_Line_Subform" _
& "(ProductID1) Values" _
"(ProductID),"
End SUB

this is all i have sorry i'm a complete beginner

[pbaldy]

For starters, with SQL you don't insert into the subform, you insert into the underlying table. Second, you've just got a string there; you haven't done anything with it. It would look like:

CurrentDb.Execute "INSERT INTO..."

Presuming the value is coming from the current form you'd have to concatenate any such values, like:

"...VALUES(" & Me.TextboxName & ")"

[jamin14]

Private Sub Command22_Click()
DoCmd.SetWarnings False '..cancel system warnings
DoCmd.RunSQL "INSERT INTO F_Order_line_subform (ProductID1,Text14,Description,,Price)" _
& " VALUES (" & [ProductID] & ", '" _
& [Category] & "' , '" _
& [Description] & "' , '" _
& [Price] & " ') ;"
Forms![F_Order_line_subform].[T_Product subform].Requery

this is my improved code but i'm getting a runtime error 3134 basically a syntax error any help with this would be greatly appreciated

thanks

[pbaldy]

For starters, the two commas together would be a problem. Is "F_Order_line_subform" really the name of a table? It appears to be a subform. Is "Text14" really the name of a field in the table? As I said, with SQL you don't insert into a form, you insert into a table. If you want to insert directly into a form, you can't use SQL.

[jamin14]

Private Sub Command22_Click()
DoCmd.SetWarnings False
MsgBox "INSERT INTO F_Order_line_subform (ProductID1, Text14, Description)" _
& " VALUES (" & [ProductID] & ", '" & [Category] & _
"', '" & [Description1] & "');"

DoCmd.RunSQL "INSERT INTO F_Order_line_subform(ProductID1, Text14, Description)" _
& " VALUES (" & [ProductID] & ", '" & [Category] & _
"', '" & [Description1] & "');"
End Sub

now it says it cannot find target table ! any help would be greatly appreciated

thanks

[jamin14]

Private Sub Command22_Click()
DoCmd.SetWarnings False
MsgBox "INSERT INTO T_Order_line (OrderID, ProductID)" _
& " VALUES (" & [OrderID] & ", '" & [ProductID] & "');"

DoCmd.RunSQL "INSERT INTO T_Order_line (OrderID, ProductID)" _
& " VALUES (" & [OrderID] & ", '" & [ProductID] & "');"

End Sub

i ve simplified it and put it to insert into a table but i am getting an error saying "microsft office access can't find the feild 'l' referred to in your expression

sorry to keep posting but your help has been great

thanks

[pbaldy]

Where are you trying to get the values from? Presuming a form, and the same form this code is running on, try this

& " VALUES (" & Me.OrderID & ", '" & Me.ProductID & "');"

Is ProductID a text data type in the table?

[jamin14]

Where are you trying to get the values from? Presuming a form, and the same form this code is running on, try this

& " VALUES (" & Me.OrderID & ", '" & Me.ProductID & "');"

Is ProductID a text data type in the table?

oRDER ID value comes from a form while product id is from a subform within the form.

product id is a number data type

me. did not work unfortunately

[pbaldy]

If product ID is a number, you don't want the single quotes surrounding the value. Can you post the db?

[jamin14]

thiss should work

[pbaldy]

I don't see that code anywhere in this sample. Did you try without the single quotes:

& " VALUES (" & Me.OrderID & ", " & Me.ProductID & ");"

Since the values on are different forms, you'll need to adjust the form references relative to where you intend to run the code from:

Forms Refer to Form and Subform properties and controls

[jamin14]

I don't see that code anywhere in this sample. Did you try without the single quotes:

& " VALUES (" & Me.OrderID & ", " & Me.ProductID & ");"

Since the values on are different forms, you'll need to adjust the form references relative to where you intend to run the code from:

Forms Refer to Form and Subform properties and controls

i changed my code but still get errors

Private Sub Command22_Click()
DoCmd.SetWarnings False
MsgBox "INSERT INTO T_Order_line (Description, ProductID)" _
& " VALUES (" & Me.Description1 & ", " & Me.ProductID1 & ");"


DoCmd.RunSQL "INSERT INTO T_Order_line (Description, ProductID)" _
& " VALUES (" & Me.Description1 & ", " & Me.ProductID1 & ");"

End Sub


i get on error runtime error 3075 , missing operator in expression sp1

any help would be appreciated

[pbaldy]

Is sp1 one of the values you're trying to insert? If so, that value is text and would need to be surrounded by single quotes. I think I saw both as numeric in the table though, so this could indicate a different problem. If you can upload a sample with this code in it, it would be easier to debug.