VBA - code runs first time but not again without closing and re-opening access

[RCBricker]

Hi all. I have some code (below) that is slowly being built to take data from a number of query recordsets and moves them into a specific excel workbook (each data set gets its own newly created sheet) and then will (eventually once I figure it all out) create a reconciliation report on the data.

My problem is that when I open access and go to my code and hit F5 it generally does what I want it to (still trying to get it to only make worksheets in excel when there is data in the query recordset and still can't get the field names to appear in any of the worksheets) but once it has exited the sub, if I hit F5 again it opens that correct excel file, but it does not do anything else it is supposed to do.

Here is the code...any ideas?

****************(BTW I was thinking that it was creating more than one instance of excel. Not sure and if it is how would I get it to stop doing that...the excel file must be open at the end of the code so the user can look for and resolve variances in the reconciliation)**********************

Option Explicit
Public oXWBcode As Object
Public db As DAO.Database
Public rstF, rstQ, rstT As DAO.Recordset
Public qrydf, qrydfOLD As DAO.QueryDef
Public strANNUAL, strMONTHc, strMONTHf, strANNUALfy, strSQL, strQRYname, _
    strTBLname, strSQLold, strTBLfdname, strXwbcodepath As String
Public LTBLct, icols As Long
Public fld As DAO.Field

Public Sub AuRDMonth()
On Error GoTo ErrCapture

Dim strMONTH As String
Dim i As Variant
Dim intRST As Integer
Dim strdbFP As String
Dim oEXCEL, oXWB, oXWS, oXWScode, oXWSqry, oXrng As Object
Dim bXO As Boolean
Dim Lrow, Lcol As Long
    
    Set db = CurrentDb
With db
    'DoCmd.OpenForm "RPT_DCAS_AuRD_REC"
    strANNUAL = "2015"
    strANNUALfy = "FY" & Right(strANNUAL, 2)
    strMONTH = "December"
    
    'strANNUAL = Forms![rpt_dcas_aurd_rec]![TXTannual]
    'strANNUALfy = "FY" & Right(strANNUAL, 2)
    'strMONTH = Forms![rpt_dcas_aurd_rec]![CMboMonth].Column(1)

    Select Case strMONTH
        Case "October"
            strMONTHc = "10"
            strMONTHf = "01"
            strANNUAL = strANNUAL - 1
        Case "November"
            strMONTHc = "11"
            strMONTHf = "02"
            strANNUAL = strANNUAL - 1
        Case "December"
            strMONTHc = "12"
            strMONTHf = "03"
            strANNUAL = strANNUAL - 1
        Case "January"
            strMONTHc = "01"
            strMONTHf = "04"
        Case "February"
            strMONTHc = "02"
            strMONTHf = "05"
        Case "March"
            strMONTHc = "03"
            strMONTHf = "06"
        Case "April"
            strMONTHc = "04"
            strMONTHf = "07"
        Case "May"
            strMONTHc = "05"
            strMONTHf = "08"
        Case "June"
            strMONTHc = "06"
            strMONTHf = "09"
        Case "July"
            strMONTHc = "07"
            strMONTHf = "10"
        Case "August"
            strMONTHc = "08"
            strMONTHf = "11"
        Case "September"
            strMONTHc = "09"
            strMONTHf = "12"
    End Select
    
    strdbFP = CurrentProject.Path & "\"

    'Start Excel
    On Error Resume Next
    Set oEXCEL = CreateObject("Excel.Application")  'Bind to existing instance of Excel
    Set oXWBcode = oEXCEL.Workbooks.Open("G:\Financial Reconciliations\DB Code file\Code for DB recs.xlsm")
    
    strXwbcodepath = oXWBcode.Path & "\" & oXWBcode.Name
    
    'Debug.Print strXwbcodepath

    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
On Error GoTo ErrCapture
        Set oEXCEL = CreateObject("Excel.Application")
        bXO = False
    Else    'Excel was already running
        bXO = True
    End If

    oEXCEL.ScreenUpdating = False
    oEXCEL.Visible = True
    
    Set oXWScode = oXWBcode.Sheets(1)

    With oXWScode
        Cells(1, 1).Value = "Monthly"
        Cells(2, 1).Value = strANNUAL
        Cells(3, 1).Value = strMONTH
        Cells(4, 1).Value = strdbFP
    End With
    
    strSQL = "SELECT * FROM TBL_Systems"
    Set rstF = db.OpenRecordset(strSQL, dbOpenSnapshot)
    If (rstF.BOF And rstF.EOF) Then
        MsgBox "No records in Systems Table"
        GoTo errEXIT
    End If
    
    Do While Not rstF.EOF
        
        strTBLname = "TBL_" & rstF.system.Value

        strSQL = "SELECT * FROM TBL_" & rstF.Fields("System") _
            & " WHERE AddFieldFileDate = '" _
            & strANNUAL & strMONTHc & "'"
        strSQLold = "SELECT * FROM TBL_" & rstF.Fields("System")
        strQRYname = "QRY_" & rstF.system.Value
        db.QueryDefs(strQRYname).SQL = strSQL
          
            Set rstQ = db.OpenRecordset(strQRYname)

            If Not rstQ.Recordset > 0 Then
                With oXWBcode
                    Set oXWSqry = oXWBcode.Worksheets.Add(after:= _
                        Worksheets(Worksheets.Count))
                        
                    oXWSqry.Name = rstF.Fields("System") & " Detail"
                End With
            End If
            'i = 1
            With rstQ
                If .RecordCount <> 0 Then
                    For icols = 0 To rstQ.Fields.Count - 1
                    
                        'oXWBcode.oXWSqry.Cells(1, icols + 1).Value _
                        = i
                        oXWBcode.oXWSqry.Cells(1, icols + 1).Value _
                        = rstQ.Fields(icols).Name
                        'i = i + 1
                    Next
                    
                    oXWBcode.oXWSqry.Range("A2").CopyFromRecordset rstQ
                    oXWBcode.oXWSqry.Range(oXWSqry.Cells(1, 1), oXWSqry.Cells _
                        (1, rstQ.Fields.Count)).Columns.AutoFit
                    oXWBcode.oXWSqry.Range("A1").Select
                End If
            End With
        db.QueryDefs(strQRYname).SQL = strSQLold
        rstF.MoveNext
    Loop

    
    
    

    
    



MsgBox "done"

 End With
errEXIT:

    On Error Resume Next
    oEXCEL.Visible = True   'Make excel visible to the user
    Set oXWSqry = Nothing
    Set oXWBcode = Nothing
    oEXCEL.ScreenUpdating = True
    Set oEXCEL = Nothing
    rstF.Close
    rstQ.Close
    db.Close
    Set db = Nothing
    Set rstF = Nothing
    Set rstQ = Nothing
        Exit Sub
    
ErrCapture:
    MsgBox "MS Access has generated the following error" & vbCrLf _
        & vbCrLf & "Error Number: " & Err.Number & vbCrLf _
        & "Error Source: CreateQry" & vbCrLf & "Error Description: " & _
        Err.Description, vbCritical, "An Error has Occured!"
    
    Resume errEXIT
    
End Sub

 

[Micron]

Seems like you have a few issues all at once. Here's some input:
Public strANNUAL, strMONTHc, strMONTHf, strANNUALfy, strSQL, strQRYname, _
strTBLname, strSQLold, strTBLfdname, strXwbcodepath As String
This is not the way to declare variables on one line, and you have several instances (at least 6) of this. The only thing here dim'd as a string is strXwbcodepath. The rest are variants for which Access will have to determine the type based on what's being assigned to it, which could cause issues.
Correct: Dim (or Public or Private) str1 as String, str2 as String, str3 as String, etc. I do not mix types on one line.

With db
'DoCmd.OpenForm "RPT_DCAS_AuRD_REC"
strANNUAL = "2015"
strANNUALfy = "FY" & Right(strANNUAL, 2)

With / End with blocks are meant for dealing with properties or methods of an object. Much of your code within the with statements doesn't apply. It's more like

With frmMyForm
  .txtbox1 = "test"
  .txtbox1.visible = false
  .recorsource = "sql statement"
  .etc
End With

Set oEXCEL = CreateObject("Excel.Application") 'Bind to existing instance of Excel . Where was this already created if it is 'existing'?

trying to get it to only make worksheets in excel when there is data in the query recordset
Test if the recordset count > 0:

If rs.recordcount>0 then
   do stuff
end if

This I don't get: If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
If there was no error, why would the result be 'could not get instance'? If there is an error, your code destroys and closes everything and makes Excel visible if open (as long as the program is resident). This may be where the problem lies on subsequent attempts to run the code since it appears you leave it up to the user to close the file. Perhaps it is still open? Did you step through the code to see what's happening?

 

[RCBricker]

I saw variables declared this way elsewhere and was like 'didn't know you could do that... tried it and seemed to work. I tested your comment that only one was a string and you are correct. I will no longer dim in this manner. Thank you.

I see you point on the with statement. So if you are doing actions with an object that are not all clumped together you simply address the object (ie db.openrecordset......whatever) each time it occurs out on its own?

if excel is already open it doesn't need to open excel just the workbook in question. I used that code when I found it about a year ago. I needed code to open a specific file and that is what I have. so....

I do not suppose I could get you to address why my code only runs once without having to close access...?

your comments are helpful.

RIch

 

[Micron]

I see you point on the with statement. So if you are doing actions with an object that are not all clumped together you simply address the object (ie db.openrecordset......whatever) each time it occurs out on its own?

Call me a nit-picker, but 'actions' could be interpreted as another way of saying 'method', which is often an attribute of an object. Docmd is an object with many methods, for example. More precise to answer your question as yes, as it applies to the properties AND/OR methods of that object and any of it's children. I say this with little experience grouping methods, but I don't see why not. I guess my need has always been to manipulate several properties, not methods.

if excel is already open it doesn't need to open excel just the workbook in question. I used that code when I found it about a year ago. I needed code to open a specific file and that is what I have. so....

I believe the call to 'create' the application object creates a new instance. If it doesn't need to be seen, just create, make it .visible = False and .screenupdating = False. Be sure to turn these back on regardless if exit is normal or after error. However, if you want to deal with one that is already open, I think you need API calls. I use the filescripting object (a project reference that you probably have not loaded) to test for access to a folder first in case a user does not have appropriate folder rights.

I do not suppose I could get you to address why my code only runs once without having to close access...?

See my last paragraph. Without more info, I have no idea. Open file, set a break on the start of procedure, step through code in break mode and watch the sequence. Try running again using the same procedure to call it and watch again. Mouse over any variables to see if they have values that pop up in bubble help. Objects will not show anything, but you can query object properties in the immediate window during execution, such as ?myObject.name, or ?rs.recordset count to verify they have been set and not just declared. Make sure the code line has been executed - it has not if the highlight is still on it during execution. You might find that on second pass, it does something very unexpected.

 

[RCBricker]

Yeah. Sorry read your post before lunch then answered it after lunch.

The user has to see the results so they can complete the manual portion of their task. as for folder rights, hmmm hadn't occurred to test that as the database is being created for and will only be distributed to a specific team.

I will look at taking out the if excel instance is open portion of the code and see if that is what is causing the error. I guess I could save and close the file, clear all excel (as they are supposed to only work these reconciliations one at a time so as to not mix up their variance reports) and then re-open the appropriate file, but that seems long winded to me.

 

[Micron]

I would not 'take it out'. I would comment out the block(s) in case you need them later, but I would step through first as suggested. I only mean for this to be done as many times as it takes to solve the issue - not permanently. In the end, I think you want to:
- test if the file is open and exit with message if true (but solve the other issues first)
- create the instance and load the file
- show application instance
- exit sub or function
User closes file from Excel.
At least that's the impression I get.

 

[RCBricker]

well I fixed the variable declarations and I have some issues with my code to get the code to now assign variables to the appropriate type of variable rather than to variants. I will have to come back with new code if the error continues after I fixed the code.

Rich