Go Premium for a chance to win a PS4. Enter to Win

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,708 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

782 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