Solved

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

Posted on 2014-09-11
18
2,287 Views
Last Modified: 2014-09-12
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,
0
Comment
Question by:Dovberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 150 total points
ID: 40318689
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
 
LVL 27

Accepted Solution

by:
MacroShadow earned 250 total points
ID: 40318691
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 150 total points
ID: 40318692
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:MacroShadow
ID: 40318696
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
 

Author Comment

by:Dovberman
ID: 40318706
<<You will have to use either ADO or DAO to process the recordset >>

Would you point me to an example?

Thanks,
0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
ID: 40318712
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
 
LVL 75
ID: 40318716
But what if that Recordset returns more than one record ?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40318720
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
 

Author Comment

by:Dovberman
ID: 40318733
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
 

Author Comment

by:Dovberman
ID: 40318755
Is there another object that I can set to hold the sql statement result?
0
 
LVL 57
ID: 40318984
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
 
LVL 84
ID: 40319016
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
 

Author Comment

by:Dovberman
ID: 40319111
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 40319118
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
 

Author Comment

by:Dovberman
ID: 40319170
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
 

Author Comment

by:Dovberman
ID: 40319199
Simple works. Thanks!

Private Sub cboFeatureList_AfterUpdate()
    Me.txtSyntax.SetFocus
    Me.txtSyntax = DLookup("[Fea_Syntax]", "tblFeature", "[Fea_No] = " & Me.cboFeatureList)
End Sub
0
 

Author Closing Comment

by:Dovberman
ID: 40319211
Thanks to all
0
 
LVL 84
ID: 40319328
FWIW: You really don't need the SetFocus line ...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question