Code not running on "Got Focus"

[Leesha]

Hi,
I have a master DB that opens a secondary database using a timer to run invoices. When the secondary database opens, the invoice form loads and the cmd button to run the invoices is set to focus. The following code should run, however it is not. The only changes to the code is the now instead of linking to Access tables [tblInvoice AutoTemp] it now links to sql tables [dbo_tblInvoice AutoTemp]. This was originally written in Access 2003 and is now being used in Access 2010. Since I didn't write the code I'm not sure where to look to see if what the issue is. I've already verified that none of the invoices has been checked as having been generated.
Thanks!
Leesha

Code: Select all

    Dim strWhere As String
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilename As String

   'Prints Non-Beverage Cup Invoices
    ' Create the where-condition
    
    strWhere = " WHERE InvoiceGenerated = False AND BeverageStore = False"
    
    If Not IsNull(Me.txtStart) Then
        strWhere = strWhere & " AND BillingPeriodStartDate >= #" & Format(Me.txtStart, "yyyy-mm-dd") & "#"
    End If
    If Not IsNull(Me.txtEnd) Then
        strWhere = strWhere & " AND BillingPeriodEndDate <= #" & Format(Me.txtEnd, "yyyy-mm-dd") & "#"
    End If
    
   ' strSQL = "SELECT * FROM [dbo_tblInvoice AutoTemp]" & strWhere
' ***** Change 500 to the number of records to process *****
    strSQL = "SELECT TOP 400 * FROM [dbo_tblInvoice AutoTemp]" & strWhere

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    Do While Not rst.EOF
        glngInvoiceID = rst!InvoiceID
        strFilename = gstrPath & rst!WalmartNumber & "-" & _
            rst!Store_ID & Format(Me.txtStart, " m.yy") & ".pdf"
        ConvertReportToPDF "rptInvoiceAuto", , strFilename, , False
        
    ' Set InvoiceGenerated field to True
        rst.Edit
        rst!InvoiceGenerated = True
        rst.Update
        
        
        
        rst.MoveNext
   
    
    Loop
    
    
    'Prints beverage cup invoice
    
    'Prints Non-Beverage Cup Invoices
    ' Create the where-condition
    
    strWhere = " WHERE InvoiceGenerated = False AND BeverageStore = True"
    
    If Not IsNull(Me.txtStart) Then
        strWhere = strWhere & " AND BillingPeriodStartDate >= #" & Format(Me.txtStart, "yyyy-mm-dd") & "#"
    End If
    If Not IsNull(Me.txtEnd) Then
        strWhere = strWhere & " AND BillingPeriodEndDate <= #" & Format(Me.txtEnd, "yyyy-mm-dd") & "#"
    End If
    
   ' strSQL = "SELECT * FROM [dbo_tblInvoice AutoTemp]" & strWhere
' ***** Change 500 to the number of records to process *****
    strSQL = "SELECT TOP 400 * FROM [dbo_tblInvoice AutoTemp]" & strWhere

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    Do While Not rst.EOF
        glngInvoiceID = rst!InvoiceID
        strFilename = gstrPath & rst!WalmartNumber & "-" & _
            rst!Store_ID & Format(Me.txtStart, " m.yy") & ".pdf"
        ConvertReportToPDF "rptInvoiceAutoBevAuto", , strFilename, , False
        
    ' Set InvoiceGenerated field to True
        rst.Edit
        rst!InvoiceGenerated = True
        rst.Update
        
        
        
        rst.MoveNext
   
    
    Loop
    
    
  'Closes Database
'  DoCmd.Quit
      
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

[HansV]

Why would code run automatically when a command button get the focus? Is the code that you posted from the On Got Focus event procedure of the command button?

[Leesha]

That is how it is set up. There are over 2000 invoices to be run and whenever the database got to around 500 it would blow up. So, which your help :-) I set the main DB to open the secondary DB using the time function. When the DB opens the form loads, the focus is set to the cmd button and the code runs till 400 invoices are generated and then the DB closes. This happens up until the point where all the invoices have been run. Of course I built this originally in 2003 and didn't didn't have the new code for generating the pdf's from Access itself. I guess I should try to see if it will run all of the invoices without doing the above.

Leesha

[HansV]

What happens if you insert a line

MsgBox "Hello World!"

at the very beginning of the code? If you don't see the message box when the database is opened, the code is not run at all...

[Leesha]

I inserted the code andit runs but then nothing else happens. Typically in th past the DB would flash a screen every time an invoice was generated.
Leesha

[HansV]

Now move the MsgBox line to below the first

Code: Select all

    Do While Not rst.EOF

What happens then?

[Leesha]

Nothing happnes

[HansV]

That would mean that the recordset is empty. Can you check whether there are records in dbo_tblInvoice AutoTemp for which
- InvoiceGenerated is False
- BeverageStore is False
- BillingPeriodStartDate is on or after the date specified in txtStart (if any)
- BillingPeriodEndDate is on or before the date specified in txtEnd (if any)

[Leesha]

There are 1932 rows of date and the billingperiod start and end dates are correct and there are the invoicegenerated and beverage stores are all false.

[HansV]

Please remove the line MsgBox "Hello World".
Above the line

Code: Select all

    strSQL = "SELECT TOP 400 * FROM [dbo_tblInvoice AutoTemp]" & strWhere

insert:

Code: Select all

    MsgBox DCount("*", "[dbo_tblInvoice AutoTemp]", Mid(strWhere, 8)) & " records found"

What is the result?

[Leesha]

Well I'm really stumped because it says 0 records found. Yet, the listbox shows the records and when I open the table they are all there.

I tried running the original DB where the tables were linked to Access tables and the invoices start to generate. Does it have anything to do with linked sql tables vs the linked access tables?
Leesha

[HansV]

Yes, that might be the cause. Does it work if you use this?

Code: Select all

    Dim strWhere As String
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilename As String

    'Prints Non-Beverage Cup Invoices
    ' Create the where-condition

    strWhere = " WHERE InvoiceGenerated = False AND BeverageStore = False"

    If Not IsNull(Me.txtStart) Then
        strWhere = strWhere & " AND BillingPeriodStartDate >= '" & Format(Me.txtStart, "yyyy-mm-dd") & "'"
    End If
    If Not IsNull(Me.txtEnd) Then
        strWhere = strWhere & " AND BillingPeriodEndDate <= '" & Format(Me.txtEnd, "yyyy-mm-dd") & "'"
    End If

    ' TESTING ONLY!
    MsgBox DCount("*", "[dbo_tblInvoice AutoTemp]", Mid(strWhere, 8)) & " records found"

    ' ***** Change 500 to the number of records to process *****
    strSQL = "SELECT TOP 400 * FROM [dbo_tblInvoice AutoTemp]" & strWhere

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    Do While Not rst.EOF
        glngInvoiceID = rst!InvoiceID
        strFilename = gstrPath & rst!WalmartNumber & "-" & _
            rst!Store_ID & Format(Me.txtStart, " m.yy") & ".pdf"
        ConvertReportToPDF "rptInvoiceAuto", , strFilename, , False

        ' Set InvoiceGenerated field to True
        rst.Edit
        rst!InvoiceGenerated = True
        rst.Update
        rst.MoveNext
    Loop

    'Prints beverage cup invoice
    ' Create the where-condition

    strWhere = " WHERE InvoiceGenerated = False AND BeverageStore = True"

    If Not IsNull(Me.txtStart) Then
        strWhere = strWhere & " AND BillingPeriodStartDate >= '" & Format(Me.txtStart, "yyyy-mm-dd") & "'"
    End If
    If Not IsNull(Me.txtEnd) Then
        strWhere = strWhere & " AND BillingPeriodEndDate <= '" & Format(Me.txtEnd, "yyyy-mm-dd") & "'"
    End If

    ' ***** Change 500 to the number of records to process *****
    strSQL = "SELECT TOP 400 * FROM [dbo_tblInvoice AutoTemp]" & strWhere

    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    Do While Not rst.EOF
        glngInvoiceID = rst!InvoiceID
        strFilename = gstrPath & rst!WalmartNumber & "-" & _
            rst!Store_ID & Format(Me.txtStart, " m.yy") & ".pdf"
        ConvertReportToPDF "rptInvoiceAutoBevAuto", , strFilename, , False

        ' Set InvoiceGenerated field to True
        rst.Edit
        rst!InvoiceGenerated = True
        rst.Update
        rst.MoveNext
    Loop

    'Closes Database
    '  DoCmd.Quit

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

[Leesha]

Still came up saying no records found

[HansV]

Aargh! What if you leave txtStart and txtEnd blank?

[Leesha]

Still comes up no records found

[HansV]

That should be impossible - by leaving out the date criteria it shouldn't make a difference whether the data are in Access or in SQL Server.

What is the result of

Code: Select all

    MsgBox DCount("*", "[dbo_tblInvoice AutoTemp]") & " records found"

and of

Code: Select all

    MsgBox DCount("*", "[dbo_tblInvoice AutoTemp]", "InvoiceGenerated = False AND BeverageStore = False") & " records found"

[Leesha]

This code showed the total numbers

MsgBox DCount("*", "[dbo_tblInvoice AutoTemp]") & " records found"

[HansV]

And the other showed 0?

[Leesha]

Sorry Hans, yes the second one was zero

[HansV]

Grasping at straws by now...
What about this one?

Code: Select all

    MsgBox DCount("*", "[dbo_tblInvoice AutoTemp]", "InvoiceGenerated = 0 AND BeverageStore = 0") & " records found"