[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to use a result from a sql string and populate a text box

Posted on 2011-05-05
10
Medium Priority
?
272 Views
Last Modified: 2012-08-13
Hello,

I am having some trouble with the following sql:

Private Sub CmbStores_AfterUpdate()

Dim str As String

str = "SELECT [Price] FROM tbl_PriceLevel WHERE [PriceCode] = " & Me.cmbStores.Column(1)
Me.txtUnitCost = str

End Sub

When I step through the code, me.cmbStores.Column(1) is correct but as soon as I go forward it populates the text box with actual sql statement.

I thought this would be fairly simple, but for some reason it is causing me problems.  Thanks in advance for your help.

0
Comment
Question by:ryanbass
10 Comments
 
LVL 6

Expert Comment

by:bartvd
ID: 35698503
You first have to execute the query. You will get a result, which can be added to the textbox.
0
 
LVL 58
ID: 35698547
If your only grabbing the one field, a Dlookup() is the easyist:

Me.txtUnitCost = Dlookup("[Price]","tbl_PriceLevel","[PriceCode] = " & chr$(34) & Me.cmbStores.Column(1) & chr$(34))

JimD.
0
 

Author Comment

by:ryanbass
ID: 35698701
So I added the following

docmd.RunsSQL str

I get the following error:  "A RunSQL action requires an argument consisting of a sql statement."  When I debug it, it looks good.

jDettman:

When I add this and comment out everything else:

Me.txtUnitCost = DLookup("[Price]", "tbl_PriceLevel", "[PriceCode] = " & Me.cmbStores.Column(1))

I get the error:  "You canceled the previous operation."

Thanks for your help.
0
Industry Leaders: 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 58
ID: 35699209
<<I get the error:  "You canceled the previous operation.">>

  What else are you doing in the rest of the procedure?

<<docmd.RunsSQL str

I get the following error:  "A RunSQL action requires an argument consisting of a sql statement."  When I debug it, it looks good.
>>

 RunSQL or CurrentDB().Execute are only for "action" queries; deletes, appends, updates, etc.  In other words those that don't return records.  If your returning records, then you need to use a recordset:

  Dim db as DAO.Database
  Dim rst as DAO.Recordset
  Dim strSQL as string

  strSQL = "SELECT [Price] FROM tbl_PriceLevel WHERE [PriceCode] = " & Me.cmbStores.Column(1)

  Set db = CurrentDB()
  set rst = db.Openrecordset(strSQL)

  If not rst.EOF then
   Me.txtUnitCost = rst![Price]
  End If

  rst.close
  Set rst = nothing

  Set db = nothing

  Which is why I said the Dlookup() was simpler.  A Dlookup() does all of the above.  The drawback is that it returns a single field and for each use of it, your opening a recordset, finding a record, closing, etc.  So if you need more then a field or two, then you want to open a recordset and find the record yourself.  At that point, you can have access to all the fields in one shot.

JimD.

 
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35699220
For ease of reading i have put the logic into a function.  If you add the function code to your form you can call it with...
    txtUnitCost = GetPrice(Me.cmbStores.Column(1))


Private Function GetPrice(priceCode As String) As String
    Dim sql As String
    Dim price As String
    Dim rs As Recordset
   
    sql = "SELECT [Price] FROM tbl_PriceLevel WHERE [PriceCode] = " & priceCode
       
   
    rs = CurrentDb.OpenRecordset(sql)
    If rs.BOF Then
        price = ""
    Else
        price = Format$(rs(0))
    End If
       
    GetPrice = price
End Function


0
 
LVL 58
ID: 35699281
BTW, is price code a numeric or text?  I assumed it was text.  If it's numeric, then the Dlookup() would be:

Me.txtUnitCost = Dlookup("[Price]","tbl_PriceLevel","[PriceCode] = " & Me.cmbStores.Column(1))

JimD.
0
 

Author Comment

by:ryanbass
ID: 35699438
This is the code that I currently have:

Private Sub CmbStores_AfterUpdate()

Dim str As String
Dim db As Database
Dim rs As DAO.Recordset

str = "SELECT [Price] FROM tbl_PriceLevel WHERE [PriceCode] = " & Me.cmbStores.Column(1)

Set db = CurrentDb()
Set rs = db.OpenRecordset(str)

If Not rs.EOF Then
    Me.txtUnitCost = rs![Price]
End If

rs.Close
Set rs = Nothing
Set db = Nothing

'Me.txtUnitCost = DLookup("[Price]", "tbl_PriceLevel", "[PriceCode] = " & "' Me.cmbStores.Column(1))'")

End Sub

When I run it, I get the error "Too few parameters.  Expected 1."  If I comment out everything except the dLookup command nothing happens on the form.  It won't populate anything in the text box and doesn't throw an error.  When I step through the code, it tells me me.txtUnitCost = Null.

Sorry if this is doesn't make much sense.  I am fairly green when it comes to code.

Thanks
0
 

Author Comment

by:ryanbass
ID: 35699464
Sorry - PriceCode is a text field.  I had it wrong in the earlier post.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35699557
<<Sorry if this is doesn't make much sense.  I am fairly green when it comes to code.>>

  Not a problem.  First, there is no reason the DLookup() should not work; you've got something else going on.

  In regards to the code, "Too few parameters.  Expected x" means that there is something in the SQL statement that Access can't figure out.  

<<Sorry - PriceCode is a text field.  I had it wrong in the earlier post. >>

 So first, change the SQL string statement to this:

str = "SELECT [Price] FROM tbl_PriceLevel WHERE [PriceCode] = " & chr$(34) & Me.cmbStores.Column(1) & chr$(34)

  Chr$(34), represents a quote ("), which is used to delimit text strings.  What we want the finial SQL string to look like is:

SELECT [Price] FROM tbl_PriceLevel WHERE [PriceCode] = "somecode"

  However since were doing this in code and working with a string, we need to resort to the Chr$(34)'s.  You could also do it like this:

str = "SELECT [Price] FROM tbl_PriceLevel WHERE [PriceCode] = '" & Me.cmbStores.Column(1) & "'")

  which gives you this:

SELECT [Price] FROM tbl_PriceLevel WHERE [PriceCode] = 'somecode'

  Little easier to understand, but harder to work with for a number of reasons (one of which is do you have '" or "' ?).  Pretty hard to tell with some fonts and sizes.

  Anyway, try that.  If you still get the parameter message, then the issue I'm sure is this:

 Me.cmbStores.Column(1)

  "Me" only works within a form or reports code module.  If your doing this in a standard module, it's not going to work.  In that case, you need to use a full reference:

  Forms![<myFormName>]![cmbStores].Column(1)

  There are a few other ways to do the above, but that will work no matter what.

  If you work with this and still can't make headway, it might be best to post a small sample DB with the form and the table(s) so we can see what your trying to do.

JimD.






0
 

Author Closing Comment

by:ryanbass
ID: 35699617
I changed the sql statement and it works perfect.  Again thanks for all your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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