Solved

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

Posted on 2004-08-20
7
12,508 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now