Solved

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

Posted on 2004-08-20
7
12,627 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

773 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