Solved

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

Posted on 2014-09-11
18
1,693 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 26

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
 
LVL 26

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 26

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 26

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now