Textbox Control Source SQL problem

[dchendrickson]

I am using Access2002 / XP Pro.

I have an unbound textbox on a form that concatenates a
couple of items. I am trying to convert the expression
from a DLookup construct to an SQL statement in hopes
that the form's speed will improve. But when I create the
equivalent SQL, I get the #Name? error.

I have created the SQL in a temporary query and it
worked, then cut and paste into the textbox. I have
removed references in the WHERE statement and just
hardcoded a known value in order to debug and get a
response. Still get the error. Any ideas? Here is what I
have:

Old:
=DLookup("ConnectorLabel","tblConnector","ConnectorID = "
&[cboConnector]) & "-" & [txtPinLabel]

New:
=(SELECT ConnectorLabel FROM tblConnector WHERE
ConnectorID = Forms!frmCard![cboConnector] & "-" &
[txtPinLabel]

Thanks for your time and patience.

-dc

 

[Van T. Dinh]

No, the SQL String won't work.

The SQL String is simply a String. It doesn't return
anything until it is processed by the (JET) database
engine and the JET database engine does get involved with
the setting of the TextBox ControlSource.

The DLookUp is fine. Keep it this way. If you do about
10,000 DLookups at once, it will be slow (in computer
speed) but using it once will be fine.

HTH
Van T. Dinh
MVP (Access)

 

[dchendrickson]

Thanks Van. That all makes sense now.

-dc

-----Original Message-----
No, the SQL String won't work.

The SQL String is simply a String. It doesn't return
anything until it is processed by the (JET) database
engine and the JET database engine does get involved with
the setting of the TextBox ControlSource.

The DLookUp is fine. Keep it this way. If you do about
10,000 DLookups at once, it will be slow (in computer
speed) but using it once will be fine.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I am using Access2002 / XP Pro.

I have an unbound textbox on a form that concatenates a
couple of items. I am trying to convert the expression
from a DLookup construct to an SQL statement in hopes
that the form's speed will improve. But when I create the
equivalent SQL, I get the #Name? error.

I have created the SQL in a temporary query and it
worked, then cut and paste into the textbox. I have
removed references in the WHERE statement and just
hardcoded a known value in order to debug and get a
response. Still get the error. Any ideas? Here is what I
have:

Old:
=DLookup("ConnectorLabel","tblConnector","ConnectorID = "
&[cboConnector]) & "-" & [txtPinLabel]

New:
=(SELECT ConnectorLabel FROM tblConnector WHERE
ConnectorID = Forms!frmCard![cboConnector] & "-" &
[txtPinLabel]

Thanks for your time and patience.

-dc
.

.