?
Solved

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

Posted on 2014-09-11
18
Medium Priority
?
2,711 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 600 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 1000 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 600 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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 1000 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 58
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 85
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 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 85
ID: 40319328
FWIW: You really don't need the SetFocus line ...
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 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