Runtime error 3075 1

[THWatson]

Following is code on a splash form that opens when the database is invoked...

Private Sub Form_Timer()
   On Error GoTo Form_Timer_Error

DoCmd.Hourglass True
Me.TimerInterval = Me.TimerInterval - 50
If Me.TimerInterval = 0 Then
DoCmd.Hourglass False
DoCmd.Close
Dim stDocName As String

stDocName = "frmCustomers"

Dim i As Integer
[b]i = Nz(DMax("Outstanding", "qryBalanceActual"), 0)[/b]
If i >= 28 Then

    Select Case MsgBox("There are Customers with balances 28 or more days overdue." _
        & vbCrLf & "      Do you wish to check the balances owing?""" _
        , vbYesNo Or vbExclamation Or vbDefaultButton1, "Overdue Balances check")
                               
    Case vbYes
        DoCmd.OpenForm stDocName
        DoCmd.GoToControl "Page 3"
    Case vbNo
        DoCmd.OpenForm "frmMainMenu"
End Select
End If

End If

   On Error GoTo 0
   Exit Sub

Form_Timer_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Timer of VBA Document Form_frmSplash"
    
End Sub

Is there any reason this would result in Runtime Error 3075 on the user's computer? Debug highlights the line I have in bold print.

Thanks.

Tom

 

[MajP]

Tom,
For your Dcount, I would make a bare bones query from scratch with the absolute minimum fields. Even the qryBalanceActual pulls in a lot of fields that are unnecessary. However that query requires all of these other queries
qryInterestNSFTotal
qunInvoiceTotals
qryPaymentsTotals

These queries contain calculated fields and unused fields for this purpose. These calculations are running hundreds if not thousands of times. Your error could be in the other queries somewhere.

 

[THWatson]

MajP
Yep, I hear what you are saying. And the 3 queries you list also check other queries.

For example, qunInvoiceTotals brings together the Invoice details for Materials and the Invoice details for Labour.

But all of these work just fine.

That makes me wonder if it's the case that the small Netbook computer just isn't capable of handling that much data. Although on frmCustomers there is a tab for checking outstanding balances and issuing follow-up balance statements. This tab has a list box, and behind that list box uses the RowSource SQL as follows:

SELECT qryBalanceActual.CustomerID, qryBalanceActual.FullName, qryBalanceActual.ProjectNbr, qryBalanceActual.Balance AS [Balance Owing], qryBalanceActual.CompletionDateActual, qryBalanceActual.Outstanding AS [Days Outstanding]
FROM qryBalanceActual
WHERE (((qryBalanceActual.Balance)>1))
ORDER BY qryBalanceActual.FullName, qryBalanceActual.ProjectNbr;

It works just fine on this tab on the Netbook computer. So why okay there but not okay on frmSplash?

I will try and see whether or not I can reduce the number of things that have to happen...as you say "make a bare bones query from scratch."

Tom

 

[THWatson]

Since this code runs fine from the tab on the Customers form, and since the Error 3075 no longer Debugs to a line in the code, it makes me wonder if it's the Timer operation itself that creates the problem on the Netbook (those little machines have their advantage but there are also limitations).

I'll change the form and eliminate the Timer and see if that works.

Tom

 

[THWatson]

MajP
Here are the dilemmas as I see them. (Maybe I've been hunting around in this grove of trees for long enough that I can't see the forest clearly, but...)

1. I need to be able to produce the full balance, and flag only those customers where the balance has been owing for 28 days or more.
As I see it, the balance is Invoice minus Payments plus any Interest/NSF charges that have been applied. The query that produces that must also include the Customer name, the Project number, etc. Yep, this requires building queries, combining data, but how to make a bare bones query from scratch that doesn't include this.

As for these queries running hundreds, and perhaps thousands of times, down the road that's entirely possible. At the moment there are only 27 rows of Customer records.

I wondered about constructing a Make-Table query and then pulling from the table, but it's not clear to me this would change that much.

2. On both my computers all this stuff works just fine. It's only on the Netbook where this "checking for overdue balances" fails. Everything else works just fine. The Netbook has an Atom processor, 1 gig of ram and runs Windows XP.

3. With respect to the Runtime error 3075, it turns out that with a fresh reboot this error doesn't occur when the frmSplash is run. However, the rest of the routine that says there are customers with balances over 28 days doesn't happen either. I know that Access uses considerable system resources, all of which makes me wonder whether or not the Netbook is up to everything.

I may well have to wait until I can physically get the Netbook in my hands to figure out what is going on. If only I could reproduce the error here would be a huge help.

Tom

 

[MajP]

The error message is likely bogus and not the real problem. 3075 is a "syntax error" problem, but a syntax error could not process on one machine and work on another. You could just try to trap this error and then do a "resume next" and see if it works.

As far as number 1, you may need that query somewhere that contains all of that information, but I am suggesting a new query just for the purposes of determining if >28 days.