Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-20
7
Medium Priority
?
13,442 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 200 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
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…

722 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