Solved If rst.RecordCount = 0 but continue to next open record step (1 Viewer)

Number11

Member
Local time
Today, 08:42
Joined
Jan 29, 2020
Messages
607
I have this code that selects which query to export and if records are found within the give date ranges it runs fine, but if no records are found in the first run it stops and dopes not contine to the second query to export?


Set rst = CurrentDb.OpenRecordset("Test Data")
If rst.RecordCount = 0 Then

Dialog.Box "No Records Found - Checking Next Test Data...", vbInformation, "Database Message"
Else
rst.MoveLast
rst.MoveFirst

Set rst = CurrentDb.OpenRecordset("Test2 Data")
If rst.RecordCount = 0 Then
 

Minty

AWF VIP
Local time
Today, 08:42
Joined
Jul 26, 2013
Messages
10,353
You haven't got and end if after the first else.

In fact, as shown that code makes no sense - Indent it properly and show us it all?
 

Number11

Member
Local time
Today, 08:42
Joined
Jan 29, 2020
Messages
607
Private Sub CommandTEST_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Clear all records from Test Table"
DoCmd.OpenQuery "Test Build Table"
DoCmd.SetWarnings True
Dim rst
Dim XL As Excel.Application
Set XL = CreateObject("excel.application")
Dim vFile
vFile = "c:\Test..xlsx"
Set rst = CurrentDb.OpenRecordset("Not In Test Table")
If rst.RecordCount = 0 Then
Dialog.Box "No records found for Test1 - Checking Test2 now...", vbInformation, "Database Message"
Else
rst.MoveLast
rst.MoveFirst
With XL
.Visible = False
.Workbooks.Open vFile
.Sheets("A").Select
.Range("A2").Select
.ActiveCell.CopyFromRecordset rst
.ActiveWorkbook.SaveAs ("C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
.ActiveWorkbook.Close
.Application.Quit

vFile = ("C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
Set rst = CurrentDb.OpenRecordset("Not In Test2 Table")
If rst.RecordCount = 0 Then
Dialog.Box " No records found for Test2!", vbInformation, "Database Message"
Else
rst.MoveLast
rst.MoveFirst
With XL
.Visible = False
.Workbooks.Open vFile
.Sheets("B").Select
.Range("A2").Select
.ActiveCell.CopyFromRecordset rst
.ActiveWorkbook.SaveAs ("C:\Test Query1&2" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
.ActiveWorkbook.Close
.Application.Quit
DoCmd.SetWarnings False
DoCmd.OpenQuery " Clear all records from Test Table "
DoCmd.SetWarnings True
Kill "C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"
End With
End If
End With
End If
End Sub
 
Last edited:

Number11

Member
Local time
Today, 08:42
Joined
Jan 29, 2020
Messages
607
You haven't got and end if after the first else.

In fact, as shown that code makes no sense - Indent it properly and show us it all?
see code now in full thanks for your help!
 

Minty

AWF VIP
Local time
Today, 08:42
Joined
Jul 26, 2013
Messages
10,353
see code now in full thanks for your help!
Please use code tags, and use some indenting to see the various levels;
SQL:
Private Sub CommandTEST_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Clear all records from Test Table"
    DoCmd.OpenQuery "Test Build Table"
    DoCmd.SetWarnings True
    Dim rst
    Dim XL As Excel.Application
    Set XL = CreateObject("excel.application")
    Dim vFile
    vFile = "c:\Test..xlsx"
    Set rst = CurrentDb.OpenRecordset("Not In Test Table")
    If rst.RecordCount = 0 Then
        Dialog.Box "No records found for Test1 - Checking Test2 now...", vbInformation, "Database Message"
    Else
        rst.MoveLast
        rst.MoveFirst
        With XL
            .Visible = False
            .Workbooks.Open vFile
            .Sheets("A").Select
            .Range("A2").Select
            .ActiveCell.CopyFromRecordset rst
            .ActiveWorkbook.SaveAs ("C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
            .ActiveWorkbook.Close
            .Application.Quit

            vFile = ("C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
            Set rst = CurrentDb.OpenRecordset("Not In Test2 Table")
            If rst.RecordCount = 0 Then
                Dialog.Box " No records found for Test2!", vbInformation, "Database Message"
            Else
                rst.MoveLast
                rst.MoveFirst
                With XL
                    .Visible = False
                    .Workbooks.Open vFile
                    .Sheets("B").Select
                    .Range("A2").Select
                    .ActiveCell.CopyFromRecordset rst
                    .ActiveWorkbook.SaveAs ("C:\Test Query1&2" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
                    .ActiveWorkbook.Close
                    .Application.Quit
                    DoCmd.SetWarnings False
                    DoCmd.OpenQuery " Clear all records from Test Table "
                    DoCmd.SetWarnings True
                    Kill "C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"
                End With
            End If
        End With
    End If
End Sub

I'll have a quick look in a minute but think my original statement holds true.
 

Minty

AWF VIP
Local time
Today, 08:42
Joined
Jul 26, 2013
Messages
10,353
As suspected - you never get to the second part if the first part fails. See how this structure makes more sense;
SQL:
Private Sub CommandTEST_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Clear all records from Test Table"
    DoCmd.OpenQuery "Test Build Table"
    DoCmd.SetWarnings True
    Dim rst
    Dim XL As Excel.Application
    Set XL = CreateObject("excel.application")
    Dim vFile
    vFile = "c:\Test..xlsx"
    Set rst = CurrentDb.OpenRecordset("Not In Test Table")
    If rst.RecordCount = 0 Then
        Dialog.Box "No records found for Test1 - Checking Test2 now...", vbInformation, "Database Message"
    Else
        rst.MoveLast
        rst.MoveFirst
        With XL
            .Visible = False
            .Workbooks.Open vFile
            .Sheets("A").Select
            .Range("A2").Select
            .ActiveCell.CopyFromRecordset rst
            .ActiveWorkbook.SaveAs ("C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
            .ActiveWorkbook.Close
            .Application.Quit

            vFile = ("C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
        End With
    End If
       
    Set rst = CurrentDb.OpenRecordset("Not In Test2 Table")
    If rst.RecordCount = 0 Then
        Dialog.Box " No records found for Test2!", vbInformation, "Database Message"
    Else
        rst.MoveLast
        rst.MoveFirst
        With XL
            .Visible = False
            .Workbooks.Open vFile
            .Sheets("B").Select
            .Range("A2").Select
            .ActiveCell.CopyFromRecordset rst
            .ActiveWorkbook.SaveAs ("C:\Test Query1&2" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx")
            .ActiveWorkbook.Close
            .Application.Quit
            DoCmd.SetWarnings False
            DoCmd.OpenQuery " Clear all records from Test Table "
            DoCmd.SetWarnings True
            Kill "C:\Test Query1" & Format(Now(), "DD-MMM-YYYY hhmm ") & ".xlsx"
        End With
    End If

End Sub
I'm very confused by your use of vFile - what is it achieving?
 

Number11

Member
Local time
Today, 08:42
Joined
Jan 29, 2020
Messages
607
I'm very confused by your use of vFile - what is it achieving? its exporting the query to an excel template works all fine if both query have data but if test1 does not have any data it stops and does not move on to next export
 

Minty

AWF VIP
Local time
Today, 08:42
Joined
Jul 26, 2013
Messages
10,353
I'm very confused by your use of vFile - what is it achieving? its exporting the query to an excel template works all fine if both query have data but if test1 does not have any data it.
I understand that - and the second code I posted will always run the second step, regardless of the lack of data in the first step. Look at the indenting in your code, then mine and it should clearly highlight why yours doesn't get to the second part.

You are setting vFile to a file name but never using it anywhere as far as I can see?
 

Number11

Member
Local time
Today, 08:42
Joined
Jan 29, 2020
Messages
607
I understand that - and the second code I posted will always run the second step, regardless of the lack of data in the first step. Look at the indenting in your code, then mine and it should clearly highlight why yours doesn't get to the second part.

You are setting vFile to a file name but never using it anywhere as far as I can see?
Thanks that all work now :)
 

Users who are viewing this thread

Top Bottom