Solved

How to print the result from a SQL command in acces

Posted on 2004-08-10
5
486 Views
Last Modified: 2012-05-05
i have a combo box where I select  card number.

Then on click I have a function "OnChange" that update  some textbox corresponding to the selected card number.

like Me.TextZone1 = DoCmd.RunSQL "SELECT [description] FROM [division] WHERE [NoDeCarte]=" & Me.ComboBox & " LIMIT=1"

But I want the result of the query to be printed in the textbox
0
Comment
Question by:BrunoTremblay
[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
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11763699
Short code:
Me.TextZone1 = CurrentDb.OpenRecordset("SELECT [description] FROM [division] WHERE [NoDeCarte]=" & 
Me.ComboBox & " LIMIT=1").Fields("description").Value

Note that you might however do it with more lines to check errors etc;:

dim objRecord as Recordset
set objRecord = CurrentDb.OpenRecordset("SELECT [description] FROM [division] WHERE [NoDeCarte]=" & 
Me.ComboBox & " LIMIT=1")
if objRecord.eof and objRecord.bof then
   '-> no record found with the criteria...
else
   Me.TextZone1 = objrecord.Fields("description").Value
end if
objRecords.close
set objRecord = nothing

Cheers
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 11763721

Dim rs As DAO.Recordset

mysql = "SELECT [description] FROM [division] WHERE [NoDeCarte]=" & Me.ComboBox & " LIMIT=1;"
Set rs = CurrentDb.OpenRecordset(mysql)
rs.MoveFirst
Me.TextZone1 = rs.fields(0)
rs.Close


regards, franz
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 11763772
do u need to put a AND in fromnt of LIMIT?


also how many records are you expecting to return?

if its lots, then use Franz's example, if its just the one record expected, then u could use DLOOKUP

Me.TextZone1 = DLOOKUP("description","division","NoDeCarte=" & Me.ComboBox & " AND LIMIT=1")

if NoDeCarte is a string

Me.TextZone1 = DLOOKUP("description","division","NoDeCarte='" & Me.ComboBox & "' AND LIMIT=1")
0
 

Author Comment

by:BrunoTremblay
ID: 11763898
DLookup really do it for me
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11763922
no probs

just something else u need to know

if you dont find a match, DLOOKUP returns a null and that may fail
so wrap it with NZ or validate the return

Me.TextZone1 = NZ(DLOOKUP("description","division","NoDeCarte='" & Me.ComboBox & "' AND LIMIT=1"),"")



0

Featured Post

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!

Question has a verified solution.

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

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…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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 …

761 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