Earlier Post

[Nick]

I posted this message earlier and I'm not having too much luck with a
response. If the solution to the code is too difficult, could someone
suggest an alternate method? Thank you very much.



Hello. I am attempting to implement a piece of functionality that will
auto-populate a text box based on the value of another text box. To
explain, I would like to populate the Qty_Inspected field based on the entry
from the Job_Qty field. The table sample_xref is my reference table and
itcontains both of these rows. Here is the code below.


Private Sub Qty_Inspected_Enter()
Me.Qty_Inspected.ControlSource = "SELECT sample_xref.Qty_Inspected FROM
sample_xref WHERE
(((sample_xref.Job_Qty)=[Forms]![QA_Data_Sheet]![Job_Qty]));"
End Sub


However, when I test this it returns a #NAME value in the text box. Could
someone give me some guidance? Any help would be greatly appreciated.

Thanks in advance,

Nick

 

[GreySky]

Instead of "Select ..." try using dlookup instead.

For example (air code):

Me.Qty_Inspected.ControlSource = "=DLookUp
('[sample_xref.Qty_Inspected]', 'sample_xref', '[Job_Qty]
=' & [Forms]![QA_Data_Sheet]![Job_Qty])"

If course if you want to be able to edit the control, you
might just set the control value instead of using the
controlsource. For example:

Qty_Inspected = DLookup
("[sample_xref.Qty_Inspected]", "sample_xref", "[Job_Qty]
=" & [Forms]![QA_Data_Sheet]![Job_Qty])

David Atkins, MCP

 

[SteveS]

Nick,

First, as to why this code:

Me.Qty_Inspected.ControlSource = "SELECT
sample_xref.Qty_Inspected FROM sample_xref WHERE
(((sample_xref.Job_Qty)=[Forms]![QA_Data_Sheet]!
[Job_Qty]));"

didn't/doesn't work.

The text control doesn't know what to do with the SQL
select statement.

It would work if you (using DAO - don't know about ADO)
used it to open a recordset, read the value, set
Me.Qty_Inspected equal to the value found and closed the
recordset.

If you wrote a function that would get the
Me.Qty_Inspected value from the table, then

Me.textbox2 Control Source could be set to:
=GetQtyInspected()


Other ideas to get the value for Qty_Inspected...

Are you storing the Qty_Inspected in the first table? Or
should I ask, is textbox2 an unbound control?

Could Job_Qty be a combo box? Are all of the values for
Job_Qty stored in the second table?

If Job_Qty could be a combo box (cboJob_Qty), then the
rowsource could be a query with 2 fields using the second
table, and the cboJob_Qty AfterUpdate event could auto-
populate textbox2 using:

Me.textbox2 = Me.cboJob_Qty.Column(1) (remember - zero
based. Column 1 is the second field)


Every time Job_Qty is changed, textbox2 gets updated
automatically. And the cboJob_Qty NotInList event could be
used to add new values to the second table.



I would make textbox2 (actually Qty_Inspected) an unbound
control, since the value is stored in the second table and
related to the first table by Job_Qty. In other
forms/reports, use a query to join the two tables on
Job_Qty to get Qty_Inspected.

Thats my 2 cents.... HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----
David, thanks for your help. However, it is still not working for some
reason.

I forgot to mention that the value I am attempting to auto-populate belongs
to a different table than the value corresponding to the first text-box.
For example, table 1 contains Job_Qty and other fields, table # contains
Job_Qty and its corresponding Qty_Inspected. So, based on the Job_Qty
relationship between the two tables, I would like to enter Job_Qty in
textbox1 and have textbox2 auto-populate with Qty_Inspected from table 2.

I hope I explained this a bit better. I appreciate the help; this has been
quite a frustrating piece of the project. Please let me know any and all
suggestions you guys might be able to offer.

Thanks,

Nick

Instead of "Select ..." try using dlookup instead.

For example (air code):

Me.Qty_Inspected.ControlSource = "=DLookUp
('[sample_xref.Qty_Inspected]', 'sample_xref', '[Job_Qty]
=' & [Forms]![QA_Data_Sheet]![Job_Qty])"

If course if you want to be able to edit the control, you
might just set the control value instead of using the
controlsource. For example:

Qty_Inspected = DLookup
("[sample_xref.Qty_Inspected]", "sample_xref", "[Job_Qty]
=" & [Forms]![QA_Data_Sheet]![Job_Qty])

David Atkins, MCP

.

 

[Nick]

Steve, thank you very much for the detailed explanation!

Nick

Nick,

First, as to why this code:

Me.Qty_Inspected.ControlSource = "SELECT
sample_xref.Qty_Inspected FROM sample_xref WHERE
(((sample_xref.Job_Qty)=[Forms]![QA_Data_Sheet]!
[Job_Qty]));"

didn't/doesn't work.

The text control doesn't know what to do with the SQL
select statement.

It would work if you (using DAO - don't know about ADO)
used it to open a recordset, read the value, set
Me.Qty_Inspected equal to the value found and closed the
recordset.

If you wrote a function that would get the
Me.Qty_Inspected value from the table, then

Me.textbox2 Control Source could be set to:
=GetQtyInspected()


Other ideas to get the value for Qty_Inspected...

Are you storing the Qty_Inspected in the first table? Or
should I ask, is textbox2 an unbound control?

Could Job_Qty be a combo box? Are all of the values for
Job_Qty stored in the second table?

If Job_Qty could be a combo box (cboJob_Qty), then the
rowsource could be a query with 2 fields using the second
table, and the cboJob_Qty AfterUpdate event could auto-
populate textbox2 using:

Me.textbox2 = Me.cboJob_Qty.Column(1) (remember - zero
based. Column 1 is the second field)


Every time Job_Qty is changed, textbox2 gets updated
automatically. And the cboJob_Qty NotInList event could be
used to add new values to the second table.



I would make textbox2 (actually Qty_Inspected) an unbound
control, since the value is stored in the second table and
related to the first table by Job_Qty. In other
forms/reports, use a query to join the two tables on
Job_Qty to get Qty_Inspected.

Thats my 2 cents.... HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----
David, thanks for your help. However, it is still not working for some
reason.

I forgot to mention that the value I am attempting to auto-populate belongs
to a different table than the value corresponding to the first text-box.
For example, table 1 contains Job_Qty and other fields, table # contains
Job_Qty and its corresponding Qty_Inspected. So, based on the Job_Qty
relationship between the two tables, I would like to enter Job_Qty in
textbox1 and have textbox2 auto-populate with Qty_Inspected from table 2.

I hope I explained this a bit better. I appreciate the help; this has been
quite a frustrating piece of the project. Please let me know any and all
suggestions you guys might be able to offer.

Thanks,

Nick

Instead of "Select ..." try using dlookup instead.

For example (air code):

Me.Qty_Inspected.ControlSource = "=DLookUp
('[sample_xref.Qty_Inspected]', 'sample_xref', '[Job_Qty]
=' & [Forms]![QA_Data_Sheet]![Job_Qty])"

If course if you want to be able to edit the control, you
might just set the control value instead of using the
controlsource. For example:

Qty_Inspected = DLookup
("[sample_xref.Qty_Inspected]", "sample_xref", "[Job_Qty]
=" & [Forms]![QA_Data_Sheet]![Job_Qty])

David Atkins, MCP

.