DoCmd.RunSQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a text box that inserts a transaction amount into a Table on
double-click.

Private Sub txtTransaction_DblClick(Cancel As Integer)
DoCmd.RunSQL "insert into Manual (trans_amt)values _
(Forms![frm_NPC]![txtTransAmt])"
MsgBox "Amount entered", vbOKOnly
End Sub

I had "hard coded" to fields in the Manual table: cost_center and block. Now
it turns out we want to select that information from another table, call it
FinancialInfo. I have placed a combo box on my form to select the cost_center
from the FinancialInfo table, but I want the code now to insert not only the
trans_amt value on double click, but also the selected cost_center from the
form combo box (cboCC), and the block from the FinancialInfo table
corresponding to the selected cost_center (cboCC).

I tried to use DLookup, but I'm not quite sure how to incorporate both the
RunSQL insert and DLookup to do what I want. Can anyone give me some advice?
 
Hi,

first - get a block into some variable (varBlock) using DLookup, and then
put all values into SQL like this:
DoCmd.RunSQL "insert into Manual (trans_amt, block, cost_center ) values ("
& Forms![frm_NPC]![txtTransAmt] & "," & varBlock & "," &
Forms![frm_NPC]!cboCC & ")"

if any of these values is string - then you have to enclose it in quotes or
double quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
Alex Dybenko said:
Hi,

first - get a block into some variable (varBlock) using DLookup, and then
put all values into SQL like this:
DoCmd.RunSQL "insert into Manual (trans_amt, block, cost_center ) values ("
& Forms![frm_NPC]![txtTransAmt] & "," & varBlock & "," &
Forms![frm_NPC]!cboCC & ")"

if any of these values is string - then you have to enclose it in quotes or
double quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



Ray S. said:
I have a text box that inserts a transaction amount into a Table on
double-click.

Private Sub txtTransaction_DblClick(Cancel As Integer)
DoCmd.RunSQL "insert into Manual (trans_amt)values _
(Forms![frm_NPC]![txtTransAmt])"
MsgBox "Amount entered", vbOKOnly
End Sub

I had "hard coded" to fields in the Manual table: cost_center and block.
Now
it turns out we want to select that information from another table, call
it
FinancialInfo. I have placed a combo box on my form to select the
cost_center
from the FinancialInfo table, but I want the code now to insert not only
the
trans_amt value on double click, but also the selected cost_center from
the
form combo box (cboCC), and the block from the FinancialInfo table
corresponding to the selected cost_center (cboCC).

I tried to use DLookup, but I'm not quite sure how to incorporate both the
RunSQL insert and DLookup to do what I want. Can anyone give me some
advice?
Click to expand...
Click to expand...

OK Alex, I tried what you suggested:

Dim varBlock As String
varBlock = DLookup("[block]", "[FinancialInfo]", "[cost_center]='" &
Forms![frm_NPC]![cboCC] & "'")

DoCmd.RunSQL "insert into Manual (trans_amt, cost_center, block) values
(" & Forms![frm_NPC]![TransAmt] & "," & Forms![frm_NPC]!cboCC & "," &
varBlock & ")"

I switched the order of block and cost_center to be in same order as in table.

The values are picked up OK. I can see that in the VB code window. But, when
stepping through, the block value is not actually passed to the table.
Rather, I get a popup asking for the "parameter value". Whatever I then type
into the box is what is actually passed to the table. What is wrong?
 
Hi,
if any of these values is string - then you have to enclose it in quotes
or
double quotes
Click to expand...

so, if varBlock is string - then your sql wiill look like:

DoCmd.RunSQL "insert into Manual (trans_amt, cost_center, block) values
(" & Forms![frm_NPC]![TransAmt] & "," & Forms![frm_NPC]!cboCC & ",'" &
varBlock & "')"

i added 2 single quotes. perhaps you have to do the same with cost_center

any time you can paste result SQL into new query SQL and run there - so you
get an idea what is wrong

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Ray S. said:
Alex Dybenko said:
Hi,

first - get a block into some variable (varBlock) using DLookup, and then
put all values into SQL like this:
DoCmd.RunSQL "insert into Manual (trans_amt, block, cost_center ) values
("
& Forms![frm_NPC]![txtTransAmt] & "," & varBlock & "," &
Forms![frm_NPC]!cboCC & ")"

if any of these values is string - then you have to enclose it in quotes
or
double quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



Ray S. said:
I have a text box that inserts a transaction amount into a Table on
double-click.

Private Sub txtTransaction_DblClick(Cancel As Integer)
DoCmd.RunSQL "insert into Manual (trans_amt)values _
(Forms![frm_NPC]![txtTransAmt])"
MsgBox "Amount entered", vbOKOnly
End Sub

I had "hard coded" to fields in the Manual table: cost_center and
block.
Now
it turns out we want to select that information from another table,
call
it
FinancialInfo. I have placed a combo box on my form to select the
cost_center
from the FinancialInfo table, but I want the code now to insert not
only
the
trans_amt value on double click, but also the selected cost_center from
the
form combo box (cboCC), and the block from the FinancialInfo table
corresponding to the selected cost_center (cboCC).

I tried to use DLookup, but I'm not quite sure how to incorporate both
the
RunSQL insert and DLookup to do what I want. Can anyone give me some
advice?
Click to expand...
Click to expand...

OK Alex, I tried what you suggested:

Dim varBlock As String
varBlock = DLookup("[block]", "[FinancialInfo]", "[cost_center]='" &
Forms![frm_NPC]![cboCC] & "'")

DoCmd.RunSQL "insert into Manual (trans_amt, cost_center, block) values
(" & Forms![frm_NPC]![TransAmt] & "," & Forms![frm_NPC]!cboCC & "," &
varBlock & ")"

I switched the order of block and cost_center to be in same order as in
table.

The values are picked up OK. I can see that in the VB code window. But,
when
stepping through, the block value is not actually passed to the table.
Rather, I get a popup asking for the "parameter value". Whatever I then
type
into the box is what is actually passed to the table. What is wrong?
Click to expand...
 
Alex Dybenko said:
Hi,

first - get a block into some variable (varBlock) using DLookup, and then
put all values into SQL like this:
DoCmd.RunSQL "insert into Manual (trans_amt, block, cost_center ) values ("
& Forms![frm_NPC]![txtTransAmt] & "," & varBlock & "," &
Forms![frm_NPC]!cboCC & ")"

if any of these values is string - then you have to enclose it in quotes or
double quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



Ray S. said:
I have a text box that inserts a transaction amount into a Table on
double-click.

Private Sub txtTransaction_DblClick(Cancel As Integer)
DoCmd.RunSQL "insert into Manual (trans_amt)values _
(Forms![frm_NPC]![txtTransAmt])"
MsgBox "Amount entered", vbOKOnly
End Sub

I had "hard coded" to fields in the Manual table: cost_center and block.
Now
it turns out we want to select that information from another table, call
it
FinancialInfo. I have placed a combo box on my form to select the
cost_center
from the FinancialInfo table, but I want the code now to insert not only
the
trans_amt value on double click, but also the selected cost_center from
the
form combo box (cboCC), and the block from the FinancialInfo table
corresponding to the selected cost_center (cboCC).

I tried to use DLookup, but I'm not quite sure how to incorporate both the
RunSQL insert and DLookup to do what I want. Can anyone give me some
advice?
Click to expand...
Click to expand...
Alex,

Actually, both the cost_center and the block are strings. But, the
cost_center is transferring perfectly just the way the query is written. The
block pops up with the value found in the DLookup (the correct value) in a
dialog that asks for the parameter value.

I'm not sure how I would copy and paste the query into a new SQL query,
since it depends on the variable.
 
I'm not sure how I would copy and paste the query into a new SQL query,
since it depends on the variable.
Click to expand...

say you have such statement:
DoCmd.RunSQL "insert into Manual (trans_amt, cost_center, block) values
(" & Forms![frm_NPC]![TransAmt] & "," & Forms![frm_NPC]!cboCC & ",'" &
varBlock & "')"

stop your code at this statement
now copy everything, but DoCmd.RunSQL, go to debug window (ctrl+g), type ?
there and paste SQL, so you will get

?"insert into Manual (trans_amt, cost_center, block) values
(" & Forms![frm_NPC]![TransAmt] & "," & Forms![frm_NPC]!cboCC & ",'" &
varBlock & "')"

and press enter
now you will get combined SQL, which you can paste to query SQL

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
Alex Dybenko said:
I'm not sure how I would copy and paste the query into a new SQL query,
since it depends on the variable.
Click to expand...

say you have such statement:
DoCmd.RunSQL "insert into Manual (trans_amt, cost_center, block) values
(" & Forms![frm_NPC]![TransAmt] & "," & Forms![frm_NPC]!cboCC & ",'" &
varBlock & "')"

stop your code at this statement
now copy everything, but DoCmd.RunSQL, go to debug window (ctrl+g), type ?
there and paste SQL, so you will get

?"insert into Manual (trans_amt, cost_center, block) values
(" & Forms![frm_NPC]![TransAmt] & "," & Forms![frm_NPC]!cboCC & ",'" &
varBlock & "')"

and press enter
now you will get combined SQL, which you can paste to query SQL

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
Click to expand...
Alex,

WOW! Thanks, that is so cool. Everything works great, and as always, I have
learned more than I asked for.
 
Back
Top