insert select with addtional value - confused newbie

[flect]

Hi All

I'm trying to finally bridge the gap (or chasm rather) and start learning how to do SQL commands in VBA.

What i'm trying to do is a simple invoice system where my Form selects the products from tblProducts and then creates a record in tblOrderDetails with Product AutoID from the current form record, and a QTY from a textbox on the form.

So, i need to SELECT the product - in this case "StampAutoID" (PK of current record) to insert but I also need the VALUE for Qty from the text box - "txtQty"

Private Sub txtQty_AfterUpdate()
Dim strsql As String
strsql = "INSERT INTO tblOrderDetails (Product, Qty)" & _
"SELECT StampAutoID (**?!current record on form?!**), = '" & _
VALUE = '" & Forms!frmOrderFrame!frmProducts!txtQty & "'" 
DoCmd.RunSql strsql

I'm already guessing from the syntax that it's doomed from the get go;
but I really want to be able to get my head around it, as my brain turns to mush every time i try to learn something new

Any ideas?

 

[HiTechCoach]

Hi All

I'm trying to finally bridge the gap (or chasm rather) and start learning how to do SQL commands in VBA.

What i'm trying to do is a simple invoice system where my Form selects the products from tblProducts and then creates a record in tblOrderDetails with Product AutoID from the current form record, and a QTY from a textbox on the form.

So, i need to SELECT the product - in this case "StampAutoID" (PK of current record) to insert but I also need the VALUE for Qty from the text box - "txtQty"

Private Sub txtQty_AfterUpdate()
Dim strsql As String
strsql = "INSERT INTO tblOrderDetails (Product, Qty)" & _
"SELECT StampAutoID (**?!current record on form?!**), = '" & _
VALUE = '" & Forms!frmOrderFrame!frmProducts!txtQty & "'" 
DoCmd.RunSql strsql

I'm already guessing from the syntax that it's doomed from the get go;
but I really want to be able to get my head around it, as my brain turns to mush every time i try to learn something new

Any ideas?

I'm trying to finally bridge the gap (or chasm rather) and start learning how to do SQL commands in VBA.

The good news is the SQL does not change when you create the SQL statements with VBA.


The basic syntax to insert values is this:

INSERT INTO tblYourTable (longField1, TestField2) Values ( 1, "two")

So for you it would be something like:

strsql = "INSERT INTO tblOrderDetails (Product, Qty) " 
strsql = strsql & " Values ( "  & Me.tampAutoID & " , " &  Forms!frmOrderFrame!frmProducts!txtQty & ");" 

CurrnetDB.Execute  strsql

I would assume that the QTY field would be numeric so I didn't wrap it as a string.

I would also think that the table tblOrderDetails would need to have the tblOrders primary key so that it is related.


Hope this helps ...

 

[flect]

Thanks a lot! it's definitely got me on the right track.

 

[HiTechCoach]

Thanks a lot! it's definitely got me on the right track.

You're welcome!

Glad I could assist.