-1

I'm working with a couple tables, CTOL and CTOL_Asbuilt in Access. I'm trying to run a query to join these two tables together using VBA code. I ran the query in Access and it works. I'm using DAO for the database library to retrieve data from the local Access database (code is in the same database project as the database), and I'm new to VBA Access scripting. 

SELECT CTOL.ID, CTOL.BOM_PART_NAME, CTOL.CII, CTOL.[PART FIND NO], CTOL.CSN,
       CTOL.AFS, CTOL.EQP_POS_CD, CTOL.LCN, CTOL.POS_CT, CTOL.SERIAL_NO, 
       CTOL.PART_NO_LLP, [CTOL_Asbuilt].[PART-SN], [CTOL_Asbuilt].[PART-ATA-NO], 
       [CTOL_PW-E750207_Asbuilt].[PW-PART-NO]
FROM CTOL LEFT JOIN [CTOL_Asbuilt] ON CTOL.[PART FIND NO] = [CTOL_Asbuilt].[PART-ATA-NO];

This is the code below:

Option Compare Database
Option Explicit

'Const adOpenStatic = 3
'Const adLockOptimistic = 3

Function queryDatabase()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsQuery As DAO.Recordset


Dim part_find_no() As String
Dim eqp_pos() As Integer
'Dim strSQL As String
Dim i As Integer
Dim j As Integer
'Set objConnection = CurrentDb.OpenRecordset("CTOL")

Set db = CurrentDb

Set rsQuery = db.OpenRecordset("SicrProcess", dbOpenDynaset)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

I'm getting the following error when I run this code with a macro that calls the function:

Run time error '91':

Object variable or With block variable not set

I'm trying to use the code with the query to loop through two fields and increment the value of the EQP_POS_CD field when the PART FIND NO entry matches the last (else, it just moves to the next record until it reaches the end of the result set). I want to test-run this query to make sure that the code retrieves the result that is output by running the query manually in Access.

Can you help me in fixing this error so I can run my code to retrieve the data? Thanks!

Parfait
  • 104,375
  • 17
  • 94
  • 125
evvdogg
  • 37
  • 1
  • 9

2 Answers2

1
rs.Close

You cannot close something that is not open. Perhaps you meant it to be rsQuery.Close?

braX
  • 11,506
  • 5
  • 20
  • 33
  • Fixed it. Thanks. But it's not doing anything now when I run it. – evvdogg Jun 08 '17 at 15:34
  • @evvdogg well it *closes the recordset*, what'd you expect? FWIW you're opening the recordset and closing it before you do anything useful with the data that's in it. Why do you even need a recordset? – Mathieu Guindon Jun 08 '17 at 15:36
  • Indeed, you make no attempt at looping through recordset and incrementing the value. – Parfait Jun 08 '17 at 15:44
  • My apologies. I was trying to approach this step by step and wanted to test it each step of the way so I wouldn't end up with a ton of errors after attempting to run my code. I was aiming to just test run the query, but I guess I need to do something with it in order to generate an output. Bear with me, I'm not very familiar with database scripting. – evvdogg Jun 08 '17 at 16:38
0

Open a recordset and loop through records. 

Sub queryDatabase()
    On Error GoTo ErrProc

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim qdf As DAO.QueryDef
    Set qdf = db.QueryDefs("SicrProcess") 'set your query name here

    Dim rs As DAO.Recordset
    Set rs = qdf.OpenRecordset(dbOpenDynaset)

    Dim part_find_no() As String
    Dim eqp_pos() As Integer, i As Integer

    If rs.EOF Then GoTo Leave
    rs.MoveLast
    rs.MoveFirst

    For i = 1 To rs.RecordCount

        '...
        'Do work here
        '...

        rs.MoveNext
    Next i

Leave:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    qdf.Close
    Set qdf = Nothing
    Set db = Nothing
    On Error GoTo 0
    Exit Sub

ErrProc:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub
Kostas K.
  • 8,293
  • 2
  • 22
  • 28
  • Thank you. This is very helpful and a good place for me to start. Do I need a db.close in the Leave block as well? If I run this, will it loop through the records or do I need to add more? I want the end result to be looping through the records then incrementing the eqp_pos_no. I need to load both the PART FIND NO and EQP_POS_CD fields into arrays to loop through them and increment EQP_POS_CD where the PART FIND NO matches the previous PART FIND NO. How would I go about loading them into the arrays then looping? – evvdogg Jun 08 '17 at 16:35
  • Also, would it matter if it's a sub or function? I made it a function because a macro could only call functions. There isn't a specific way I need to call it though, so calling a sub would be just fine too. I'm wondering which one's more advantageous or convenient. Thanks for the feedback! – evvdogg Jun 08 '17 at 16:46