Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using query results in VBA (Access XP)

    I maintain a database that is used by a Word Processing Center to track all work. It has three main tables: a table of documents that have been worked on (document type, owner, etc.), a table of jobs (contact person, date in, date out, etc.) and a table of "job/documents" (a record of each document associated with a particular job, who worked on that document, how long they took, etc.) So, in effect, it's a one-to-many-to many database (linked by JobNumbers and DocIDs.)

    I would like to write a piece of code that is triggered when the operator enters the new job and selects "Finalize Document" from a pick-list as the JobType. (It would be associated with the On Change event of the pick-list.) I want to query each document associated with that job (all of which are listed on a subform of the Job Entry Form) and tell me if ANY of the documents have previously been submitted as part of any other "Finalize Document job. IF any document was previously submitted for the same type of job, I want to present a continuous form listing each documents' DocID, the date it was submitted, and the comments associated with it.

    I have written an SQL query into my VBA module that works on the FIRST DocID in the list and which presents the results in a form, but I'm at a loss as to how I can (1) determine if ANY of the documents satisfy the query (and thus avoid the need for the whole process), and/or (2) iterate through each document listed on the subform so that I can list all pertainent documents at one time. (If I can get a list-array of each document returned by the query, I can construct another ad-hoc query in VBA and present a list of all applicable documents and their individual data in a datasheet.)

    So, does anyone know how I can get query results and store them in variables in my VBA module? I think I'd be good from there...
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using query results in VBA (Access XP)

    To get query results and store them in variables in my VBA module, i would open a recordset in code (using DAO or ADO), and set up an Array for each field you want to keep in code. Find the number of records in the recordset to determine the size of the array.
    Then loop through the recordset , and for each record in the recordset, and assign the values from the recordset to the array variables.

    To test whether there are any values at all you can just test for RS.EOF before you start the process. If you are at the end of file before you start, then there are no records.
    Regards
    John



  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using query results in VBA (Access XP)

    If you want to assign the results of a recordset to an array, you can save yourself some trouble by using ADO recordset GetRows method. Example:

    Public Sub TestGetRows()
    On Error GoTo Err_Handler

    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim tmp As Variant ' Array variable
    Dim n As Long
    Dim strMsg As String

    Set rst = New ADODB.Recordset
    strSQL = "SELECT EmployeeID, LastName, FirstName FROM Employees " & _
    "ORDER BY LastName, FirstName;"
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    ' Get all rows in recordset:
    tmp = rst.GetRows

    ' Test results:
    For n = 0 To UBound(tmp, 2) ' specify 2nd dimension!! (row count)
    ' GetRows returns 2-dimension, zero-based array:
    ' 1st subscript identifies the field, 2nd identifies the record number:
    Debug.Print tmp(0, n), tmp(1, n), tmp(2, n)
    Next

    rst.Close
    If Not IsEmpty(tmp) Then Erase tmp

    Exit_Sub:
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "GET ROWS ERROR MESSAGE"
    Resume Exit_Sub
    End Select

    End Sub

    This example opens a recordset based on Northwind Employees table and prints out the EmployeeID, LastName, and FirstName for each employee in alphabetical order:

    5 Buchanan Steven
    8 Callahan Laura
    1 Davolio Nancy

    (etc. for rest of records)

    As noted in Help, "Use the GetRows method to copy records from a Recordset into a two-dimensional array. The first subscript identifies the field and the second identifies the record number. The array variable is automatically dimensioned to the correct size when the GetRows method returns the data." This is a lot simpler (& probably more efficient) than looping thru recordset "manually", dimensioning array, etc. For more info on ADO GetRows method check "Help" files.

    HTH

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using query results in VBA (Access XP)

    That's good Mark.

    I am more familiar with DAO so I would have done it this way.

    <pre>Public Sub DAOTestGetRows()
    On Error GoTo Err_Handler

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
    Dim lngEmpID() As Long ' Array variable for empID
    Dim strEmpSurname() As String ' Array variable for Surnames
    Dim strEmpfirstname() As String ' Array variable for firstname
    Dim lngCount As Long
    Dim strMsg As String
    Dim n As Long

    Set db = CurrentDb()

    strSQL = "SELECT EmployeeID, LastName, FirstName FROM Employees " & _
    "ORDER BY LastName, FirstName;"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    If rs.EOF Then
    MsgBox ("There are no records. Handle this how you want.")
    Else
    rs.MoveLast
    rs.MoveFirst
    lngCount = rs.RecordCount
    ReDim lngEmpID(lngCount - 1)
    ReDim strEmpSurname(lngCount - 1)
    ReDim strEmpfirstname(lngCount - 1)
    n = 0
    Do While Not rs.EOF
    lngEmpID(n) = rs("EmployeeID")
    strEmpSurname(n) = rs("LastName")
    strEmpfirstname(n) = rs("FirstName")
    n = n + 1
    rs.MoveNext
    Loop

    ' Test results:
    For n = 0 To lngCount - 1
    Debug.Print lngEmpID(n), strEmpSurname(n), strEmpfirstname(n)
    Next
    End If
    rs.Close
    If Not IsEmpty(lngEmpID) Then Erase lngEmpID
    If Not IsEmpty(strEmpSurname) Then Erase strEmpSurname
    If Not IsEmpty(strEmpSurname) Then Erase strEmpSurname
    Set rs = Nothing
    Set db = Nothing

    Exit_Sub:
    Set rs = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "GET ROWS ERROR MESSAGE"
    Resume Exit_Sub
    End Select

    End Sub
    </pre>

    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •