Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to print the result from a SQL command in acces

Posted on 2004-08-10
5
Medium Priority
?
521 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
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 2000 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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Implementing simple internal controls in the Microsoft Access application.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

564 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