SQL in Code Help

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

I have this code under a button. When I click the button I get no error
message and the Table above don't get the data. Any ideas in looking at my
code what I may have typed wrong?

strSQL = "INSERT INTO tblSOLinkPO " & _
"(OrderID, ORDER_NUM, DETAIL_LINE_NUM, ITEM,ORD_QTY, POID, PO_NO,
PO_LINE_NO) " & _
"Values(txtOrd_ID, txtOrder_Num, txtLine_Num, txtItem, txtOrd_Qty,
txtPO_ID, txtPO_NO, txtPO_Line_Num);"
 
D

Dirk Goldgar

mattc66 via AccessMonster.com said:
I have this code under a button. When I click the button I get no
error message and the Table above don't get the data. Any ideas in
looking at my code what I may have typed wrong?

strSQL = "INSERT INTO tblSOLinkPO " & _
"(OrderID, ORDER_NUM, DETAIL_LINE_NUM, ITEM,ORD_QTY,
POID, PO_NO, PO_LINE_NO) " & _
"Values(txtOrd_ID, txtOrder_Num, txtLine_Num, txtItem,
txtOrd_Qty, txtPO_ID, txtPO_NO, txtPO_Line_Num);"
Click to expand...

Is there some line of code you haven't shown us that actually executes
the SQL statement you've built in strSQL? Some line that says, for
example,

DoCmd.RunSQL strSQL

?
 
J

John Vinson

I have this code under a button. When I click the button I get no error
message and the Table above don't get the data. Any ideas in looking at my
code what I may have typed wrong?

strSQL = "INSERT INTO tblSOLinkPO " & _
"(OrderID, ORDER_NUM, DETAIL_LINE_NUM, ITEM,ORD_QTY, POID, PO_NO,
PO_LINE_NO) " & _
"Values(txtOrd_ID, txtOrder_Num, txtLine_Num, txtItem, txtOrd_Qty,
txtPO_ID, txtPO_NO, txtPO_Line_Num);"
Click to expand...

Why should you expect this to do anything?

You're defining a string variable. It's just being stored in memory;
you're not running the query or doing anything with it - just creating
a string.

Even if you were to run the query (say, DoCmd.RunSQL strSQL) it would
still fail, because you're putting the NAMES of - I presume - form
controls into the Values() list. SQL knows nothing about these names;
you need to pass the *values* stored in those controls into the SQL
string.

Back to basics though - *WHY* are you doing this, at all, rather than
simply using a bound form? If you have OrderID as the Control Source
of the textbox txtOrd_ID, and so on, you need *no code at all*. Are
you intentionally doing this "the hard way", and if so, why?

John W. Vinson[MVP]
 
D

Dirk Goldgar

John Vinson said:
Even if you were to run the query (say, DoCmd.RunSQL strSQL) it would
still fail, because you're putting the NAMES of - I presume - form
controls into the Values() list. SQL knows nothing about these names;
you need to pass the *values* stored in those controls into the SQL
string.
Click to expand...

Actually, the DoCmd.RunSQL method is pretty clever, and it *will*
resolve control names under the right circumstances. That's one of the
ways it differs from the DAO Execute method. It can even do it if the
control names alone are used, without the full
"Forms!FormName!ControlName" syntax. So it's not impossible that
mattc66's SQL statement could work, if executed via the RunSQL method.
 
J

John Vinson

So it's not impossible that
mattc66's SQL statement could work, if executed via the RunSQL method.
Click to expand...

Interesting! I'll have to try that... I long ago got the qd.Execute
method in my mind as the "preferred" way to go, and haven't worked
with RunSQL much. Thanks Dirk!

John W. Vinson[MVP]
 
D

Dirk Goldgar

John Vinson said:
Interesting! I'll have to try that... I long ago got the qd.Execute
method in my mind as the "preferred" way to go, and haven't worked
with RunSQL much. Thanks Dirk!
Click to expand...

I, too, prefer .Execute to RunSQL, but there are definitely some nice
things about RunSQL. It's a shame there's no argument to turn off
warnings for just this call.
 
Top