By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Assigning value to textbox from query?

P: n/a
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.OurItemNum1, tOrders.POQty1,
tOrders.OurItemNum2, tOrders.POQty2, tOrders.OurItemNum3, tOrders.POQty3,
tOrders.OurItemNum4, tOrders.POQty4, tRefVendors.Vendor FROM tRefVendors
INNER JOIN tOrders ON tRefVendors.VendorID=tOrders.VendorID 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.IndexID
GROUP BY tOrders.IndexID, tOrders.IndexID, tOrders.POQty1
HAVING (((tOrders.IndexID)=[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: =qItem1TotalAllocated!TotalAllocated

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!

Apr 21 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form is giving the #Name? error 'cuz it is looking for the column
named "qItem1TotalAllocated!TotalAllocated" 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("TotalAllocated","qItem1TotalAllocated")

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_AfterUpdate()
Me!txt65.Requery
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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBREhePoechKqOuFEgEQJfkQCg+dFxEufTdnAX3C8F3mXpUh imoJMAoLME
32aQVWW2DOiCZVkqLpga9NAh
=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.OurItemNum1, tOrders.POQty1,
tOrders.OurItemNum2, tOrders.POQty2, tOrders.OurItemNum3, tOrders.POQty3,
tOrders.OurItemNum4, tOrders.POQty4, tRefVendors.Vendor FROM tRefVendors
INNER JOIN tOrders ON tRefVendors.VendorID=tOrders.VendorID 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.IndexID
GROUP BY tOrders.IndexID, tOrders.IndexID, tOrders.POQty1
HAVING (((tOrders.IndexID)=[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: =qItem1TotalAllocated!TotalAllocated

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!

Apr 21 '06 #2

P: n/a
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" <me@privacy.com> wrote in message
news:%6*****************@newsread3.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form is giving the #Name? error 'cuz it is looking for the column
named "qItem1TotalAllocated!TotalAllocated" 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("TotalAllocated","qItem1TotalAllocated")

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_AfterUpdate()
Me!txt65.Requery
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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBREhePoechKqOuFEgEQJfkQCg+dFxEufTdnAX3C8F3mXpUh imoJMAoLME
32aQVWW2DOiCZVkqLpga9NAh
=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.OurItemNum1,
tOrders.POQty1,
tOrders.OurItemNum2, tOrders.POQty2, tOrders.OurItemNum3, tOrders.POQty3,
tOrders.OurItemNum4, tOrders.POQty4, tRefVendors.Vendor FROM tRefVendors
INNER JOIN tOrders ON tRefVendors.VendorID=tOrders.VendorID 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.IndexID
GROUP BY tOrders.IndexID, tOrders.IndexID, tOrders.POQty1
HAVING (((tOrders.IndexID)=[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: =qItem1TotalAllocated!TotalAllocated

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!

Apr 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.