MS Access How to fill a text box with the results of a SQL statement

I am trying to fill a text box with the results of a SQL statement  
Private Sub cboFeatureList_AfterUpdate()
    Me.txtSyntax.SetFocus
    Me.txtSyntax.ControlSource = "SELECT Fea_Syntax FROM Feature WHERE Fea_No = " & Me.cboFeatureList & " "
End Sub

The textbox text is #Name?

What am I missing?

Thanks,
DovbermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, you can't really do that ... connect a query or SQL Statement to a Text box.
What you can do is ... create an expression using DLookup() to retrieve a *single* value from a Table or a stored Query.

mx
0
MacroShadowCommented:
Use DLookup:
Private Sub cboFeatureList_AfterUpdate()
    Me.txtSyntax.SetFocus
    Me.txtSyntax = DLookup("Fea_Syntax", "Feature", "Fea_No = " & Me.cboFeatureList & " ")
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You can also use a  List Box with that SQL as the Row Source ... to display multiple results from the recordset defined by the SQL statement.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MacroShadowCommented:
The reason you can't use a SQL statement to pull a value into a textbox is because in Access as apposed to SQL Server, a query always returns a recordset. You will have to use either ADO or DAO to process the recordset even if it returns only one value.
0
DovbermanAuthor Commented:
<<You will have to use either ADO or DAO to process the recordset >>

Would you point me to an example?

Thanks,
0
MacroShadowCommented:
Using DAO:
Dim rs as DAO.Recordset
Set rs = CurrentDB.OpenRecordset("SELECT Fea_Syntax FROM Feature WHERE Fea_No = " & Me.cboFeatureList & " ")
Me.txtSyntax = rs!Fea_Syntax
rs.Close
Set rs = Nothing

Open in new window

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
But what if that Recordset returns more than one record ?
0
MacroShadowCommented:
Of course assuming that it will return only one record. After all the OP wants the value in a text box not a list or combo box.
0
DovbermanAuthor Commented:
If it returns more than one record, then I will use the findfirst method.

Textbox is read only. What property sets it to Read/write?

Thanks
0
DovbermanAuthor Commented:
Is there another object that I can set to hold the sql statement result?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If your looking to give a user a list of choices, a combo or list box control allows you to display a list and hold a value separate from that.

You can also use a form to display the results of an SQL statement, such as a continuous from.

Jim.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
f it returns more than one record, then I will use the findfirst method.
Then there's really no need to use the Recordset method. Just use DLookup, as Joe and Macroshadow suggested earlier.

Textbox is read only. What property sets it to Read/write?
This is generally controlled by Access, unless you've specifically set the Enabled and Locked properties. For example, Access will not allow you to edit a Textbox with a controlsource like this:

=DLookup("ID", "Table", "WhereClause")

You'd have to clear that value, and use one of the Form events to set the VALUE of the TExtbox:

Sub Form _Current()
  Me.YourListbox = DLookup(blah blah blah)
End Sub

Is there another object that I can set to hold the sql statement result?
As others have said, a Combo or Listbox would do what you want. YOu can use an SQL statements as the RowSource of either of those items.
0
DovbermanAuthor Commented:
Great explanations.

I will use Dlookup since the lookup is for the primary key on a single table.

Private Sub cboFeatureList_AfterUpdate()
     Dim intFea_No As Integer
     Dim varSyntax As Variant
     intFea_No = Me.cboFeatureList
     varSyntax = DLookup("[Fea_Syntax]", "Feature", "[Fea_No] = " & intFea_No)
     Me.txtSyntax.SetFocus
     Me.txtSyntax.Text = varSyntax
   
End Sub

I am almost there. Attempting to run triggers a break in debug mode.

An error message flashes something about not being able to save ?

When I reset, the expected text appears in the text box.

How can I get the error message to display?

Thanks,
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
All you really need is this in the AfterUpdate event:

Me.txtSyntax = DLookup("[Fea_Syntax]", "Feature", "[Fea_No] = " & intFea_No)

Not sure about the error message - you'd have to post the details of that here.
0
DovbermanAuthor Commented:
do you mean
Me.txtSyntax = DLookup("[Fea_Syntax]", "Feature", "[Fea_No] = " & Me.cboFeatureList)


I use Visual Studio for most of my work.

How do I get the error message to display using MS Access?

Thanks,
0
DovbermanAuthor Commented:
Simple works. Thanks!

Private Sub cboFeatureList_AfterUpdate()
    Me.txtSyntax.SetFocus
    Me.txtSyntax = DLookup("[Fea_Syntax]", "tblFeature", "[Fea_No] = " & Me.cboFeatureList)
End Sub
0
DovbermanAuthor Commented:
Thanks to all
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
FWIW: You really don't need the SetFocus line ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.