Assigning value to textbox from query?

Hi everyone,

I have a form with a combo box on it. When you select a value (a PO#) from
the combo box, the bound field is the indexID of the selected PO. On the
same form, I have a text box that I'd like to feed from a query I've built.
The query does some math and comes up with a number (qty allocated) for the
selected PO. There are two tricky parts here. That query I've built needs
to filter on the indexID that gets selected from the combo box on the form.
Next, on the text box in the form, I've assigned the control source to be
the query's value. Since there is only one field in the result of the
query, there isn't much to choose from. When I launch the form, I get
#Name? in the text box. Very annoying since I don't know where else to
look. Here are the 3 components:

Combo box on the form (cmboOEPOList):
Bound Column is 1 (the IndexID)
SELECT tOrders.IndexID , tOrders.PONum, tOrders.OurItem Num1, tOrders.POQty1,
tOrders.OurItem Num2, tOrders.POQty2, tOrders.OurItem Num3, tOrders.POQty3,
tOrders.OurItem Num4, tOrders.POQty4, tRefVendors.Ven dor FROM tRefVendors
INNER JOIN tOrders ON tRefVendors.Ven dorID=tOrders.V endorID WHERE
((([tOrders.PONum]) Like "*oe*")) ORDER BY tOrders.PONum;

Query to get my qty allocated:
SELECT Sum(tPartsSent. Item1Allocated) AS TotalAllocated
FROM tOrders INNER JOIN tPartsSent ON tOrders.IndexID = tPartsSent.Inde xID
GROUP BY tOrders.IndexID , tOrders.IndexID , tOrders.POQty1
HAVING (((tOrders.Inde xID)=[Forms]![FormPartsSent]![cmboOEPOList]));

txt65
txtBox on Form where I'm trying to display the value of the qtyallocated
from the query where it filters on the indexID of the combo box
Control Source: =qItem1TotalAll ocated!TotalAll ocated

Does anyone know why in the world this isn't connecting and instead, I get
#Name? in the textbox txt65? I'd really apreciate any help I can get here.
This newsgroup is typically awesome in terms of getting me out of these
tricky binds. Thanks!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form is giving the #Name? error 'cuz it is looking for the column
named "qItem1TotalAll ocated!TotalAll ocated" in the form's RecordSource.
Unfortunately, we can't refer to the column in a query like that from a
TextBox. The easiest thing to do is use the DLookup() function:

Control Source: =DLookup("Total Allocated","qIt em1TotalAllocat ed")

You'll have to Requery the Text Box whenever you change the ComboBox's
PO# so it will always have the correct value.. E.g.:

Private Sub cmboOEPOList_Af terUpdate()
Me!txt65.Requer y
End Sub

You might want to rename txt65 to something more descriptive. When you
come back to fix/change this form in about a year will you remember what
txt65 is?
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREhePoechKq OuFEgEQJfkQCg+d FxEufTdnAX3C8F3 mXpUhimoJMAoLME
32aQVWW2DOiCZVk qLpga9NAh
=Znzs
-----END PGP SIGNATURE-----
MX1 wrote:

Hi everyone,

I have a form with a combo box on it. When you select a value (a PO#) from
the combo box, the bound field is the indexID of the selected PO. On the
same form, I have a text box that I'd like to feed from a query I've built.
The query does some math and comes up with a number (qty allocated) for the
selected PO. There are two tricky parts here. That query I've built needs
to filter on the indexID that gets selected from the combo box on the form.
Next, on the text box in the form, I've assigned the control source to be
the query's value. Since there is only one field in the result of the
query, there isn't much to choose from. When I launch the form, I get
#Name? in the text box. Very annoying since I don't know where else to
look. Here are the 3 components:

Combo box on the form (cmboOEPOList):
Bound Column is 1 (the IndexID)
SELECT tOrders.IndexID , tOrders.PONum, tOrders.OurItem Num1, tOrders.POQty1,
tOrders.OurItem Num2, tOrders.POQty2, tOrders.OurItem Num3, tOrders.POQty3,
tOrders.OurItem Num4, tOrders.POQty4, tRefVendors.Ven dor FROM tRefVendors
INNER JOIN tOrders ON tRefVendors.Ven dorID=tOrders.V endorID WHERE
((([tOrders.PONum]) Like "*oe*")) ORDER BY tOrders.PONum;

Query to get my qty allocated:
SELECT Sum(tPartsSent. Item1Allocated) AS TotalAllocated
FROM tOrders INNER JOIN tPartsSent ON tOrders.IndexID = tPartsSent.Inde xID
GROUP BY tOrders.IndexID , tOrders.IndexID , tOrders.POQty1
HAVING (((tOrders.Inde xID)=[Forms]![FormPartsSent]![cmboOEPOList]));

txt65
txtBox on Form where I'm trying to display the value of the qtyallocated
from the query where it filters on the indexID of the combo box
Control Source: =qItem1TotalAll ocated!TotalAll ocated

Does anyone know why in the world this isn't connecting and instead, I get
#Name? in the textbox txt65? I'd really apreciate any help I can get here.
This newsgroup is typically awesome in terms of getting me out of these
tricky binds. Thanks!

I don't know you, but I know you solved my problem!!!!!

Many, Many Thanks to You! I actually stayed up late to see if anyone would
respond. Thanks again!

"MGFoster" <[e-mail] > wrote in message
news:%6******** *********@newsr ead3.news.pas.e arthlink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form is giving the #Name? error 'cuz it is looking for the column
named "qItem1TotalAll ocated!TotalAll ocated" in the form's RecordSource.
Unfortunately, we can't refer to the column in a query like that from a
TextBox. The easiest thing to do is use the DLookup() function:

Control Source: =DLookup("Total Allocated","qIt em1TotalAllocat ed")

You'll have to Requery the Text Box whenever you change the ComboBox's
PO# so it will always have the correct value.. E.g.:

Private Sub cmboOEPOList_Af terUpdate()
Me!txt65.Requer y
End Sub

You might want to rename txt65 to something more descriptive. When you
come back to fix/change this form in about a year will you remember what
txt65 is?
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREhePoechKq OuFEgEQJfkQCg+d FxEufTdnAX3C8F3 mXpUhimoJMAoLME
32aQVWW2DOiCZVk qLpga9NAh
=Znzs
-----END PGP SIGNATURE-----
MX1 wrote:

Hi everyone,

I have a form with a combo box on it. When you select a value (a PO#)
from
the combo box, the bound field is the indexID of the selected PO. On the
same form, I have a text box that I'd like to feed from a query I've
built.
The query does some math and comes up with a number (qty allocated) for
the
selected PO. There are two tricky parts here. That query I've built
needs
to filter on the indexID that gets selected from the combo box on the
form.
Next, on the text box in the form, I've assigned the control source to be
the query's value. Since there is only one field in the result of the
query, there isn't much to choose from. When I launch the form, I get
#Name? in the text box. Very annoying since I don't know where else to
look. Here are the 3 components:

Combo box on the form (cmboOEPOList):
Bound Column is 1 (the IndexID)
SELECT tOrders.IndexID , tOrders.PONum, tOrders.OurItem Num1,
tOrders.POQty1,
tOrders.OurItem Num2, tOrders.POQty2, tOrders.OurItem Num3, tOrders.POQty3,
tOrders.OurItem Num4, tOrders.POQty4, tRefVendors.Ven dor FROM tRefVendors
INNER JOIN tOrders ON tRefVendors.Ven dorID=tOrders.V endorID WHERE
((([tOrders.PONum]) Like "*oe*")) ORDER BY tOrders.PONum;

Query to get my qty allocated:
SELECT Sum(tPartsSent. Item1Allocated) AS TotalAllocated
FROM tOrders INNER JOIN tPartsSent ON tOrders.IndexID =
tPartsSent.Inde xID
GROUP BY tOrders.IndexID , tOrders.IndexID , tOrders.POQty1
HAVING (((tOrders.Inde xID)=[Forms]![FormPartsSent]![cmboOEPOList]));

txt65
txtBox on Form where I'm trying to display the value of the qtyallocated
from the query where it filters on the indexID of the combo box
Control Source: =qItem1TotalAll ocated!TotalAll ocated

Does anyone know why in the world this isn't connecting and instead, I
get
#Name? in the textbox txt65? I'd really apreciate any help I can get
here.
This newsgroup is typically awesome in terms of getting me out of these
tricky binds. Thanks!