Solved

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

Posted on 2014-09-11
18
2,063 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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