Solved

How to do DoCmd.RunSql  Select?  Need to get values from table into VBA variables

Posted on 2004-08-20
7
13,227 Views
Last Modified: 2008-01-09
I have a form that has several text boxes and combo boxes on it.  There are 2 buttons on the form - the first one stores the field data in a table.  The second one I have not completed yet & need assistance with.

The second button is used to recall the data in the fields of the LAST row of the table back to the text boxes and combo boxes on the form.  I'm having trouble getting the DoCmd.RunSQl to gather my data into VBA variables.  I've ready that DoCmd.RunSql does not work with Select statements.  So my question is, then how do I need to write this?

Here is my curent code:
================

Private Sub cmd_Same_AS_Last_Click()
On Error GoTo Err_cmd_Same_AS_Last_Click

Dim A As Integer
Dim C As Integer
Dim M As Integer
Dim D As String

    '' Query the table for the values stored in the last row, placing them in variables
    A = DoCmd.RunSQL "select Atty_TK from qry_LastJob"
    C = DoCmd.RRunSQL("select Client from qry_LastJob")
    M = DoCmd.RRunSQL("select Matter from qry_LastJob")
    D = DoCmd.RunSQL("select Description from qry_LastJob")
   
    '' Set the value of the text boxes and combo boxes to the variables
    Me.cmbo_atty = A
    Me.cmbo_Client = C
    Me.cmbo_matter = M
    Me.Description = D
     
End Sub
0
Comment
Question by:cef_soothsayer
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 4

Expert Comment

by:naivad
ID: 11851312
you could create recordsets...

dim rs as adodb.recordset

rs.open "SELECT atty_TK FROM qry_LastJob", currentproject.connection

A = rs("atty_TK").value


rs.close
' can re-upen rs now

set rs = nothing

etc....  

You must have a reference to ADO selected
0
 
LVL 4

Accepted Solution

by:
naivad earned 50 total points
ID: 11851316
dim rs as recordset

set rs = currentdb.openRecordset("SELECT atty_TK FROM qry_LastJob")

A = rs("atty_TK").value

rs .close

set rs = nothing


This was the DAO version
0
 
LVL 1

Author Comment

by:cef_soothsayer
ID: 11851377
@ Navidad

Using the code
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Atty_TK FROM qry_LastJob")
A = rs("atty_TK").Value
rs.Close

I get a "Type Mismatch" errror on the SET line.
0
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!

 
LVL 8

Expert Comment

by:arcross
ID: 11851472
declare as DAO.recordset

and make sure you have DAO referecene checked

Álvaro
0
 
LVL 4

Expert Comment

by:naivad
ID: 11851474
then you might not have a reference to DAO.

try my first post
0
 
LVL 8

Expert Comment

by:arcross
ID: 11851534
You could create all in one recordset;

Dim rst as DAO.recordset  

set rst = currentdb.openrecordset("SELECT Atty_TK,Client,Matter,Description FROM qry_LastJob")

if not rst.bof and not rst.eof then

   Me.cmbo_atty = rst("Atty_TK")
    Me.cmbo_Client = rst("Client")
    Me.cmbo_matter = rst("Matter")
    Me.Description = rst("Description")
end if

Álvaro
     

0
 
LVL 1

Author Comment

by:cef_soothsayer
ID: 11851618

That got it!
Thanks!
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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