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



"Ray S." <(E-Mail Removed)> wrote in message
news:31A00B15-9FFE-40B5-94D4-(E-Mail Removed)...
>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?

"Alex Dybenko" wrote:

> 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." <(E-Mail Removed)> wrote in message
> news:31A00B15-9FFE-40B5-94D4-(E-Mail Removed)...
> >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?
>

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

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." <(E-Mail Removed)> wrote in message
news:FA0D1D59-0787-41D8-B81C-(E-Mail Removed)...
>
>
> "Alex Dybenko" wrote:
>
>> 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." <(E-Mail Removed)> wrote in message
>> news:31A00B15-9FFE-40B5-94D4-(E-Mail Removed)...
>> >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?
>>
>
> 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?

"Alex Dybenko" wrote:

> 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." <(E-Mail Removed)> wrote in message
> news:31A00B15-9FFE-40B5-94D4-(E-Mail Removed)...
> >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?
>
>
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.

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" wrote:

> > I'm not sure how I would copy and paste the query into a new SQL query,
> > since it depends on the variable.
>
> 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,

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