Runtime error 3075 1

THWatson · Oct 10, 2010

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

Code:

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

TheAceMan1 · Oct 10, 2010

How are ya THWatson . . .

Error 3075 indicates some problem with the query. Post the SQL!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

TheAceMan1 · Oct 10, 2010

THWatson . . .

By chance does [blue]qryBalanceActual[/blue] require a parameter? If true then you can't use the [blue]DMax[/blue] function.

Microsoft said:
[blue][tt]DMax(expr, domain, [criteria])

domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for [purple]a query that does not require a parameter.[/purple][/tt][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

THWatson · Oct 11, 2010

The AceMan1

Hi AceMan...here's the SQL

Code:

SELECT qryBalance.ProjectID, qryBalance.ProjectNbr, qryBalance.FullName, qryBalance.CustomerID, qryBalance.InvoiceNbr, qryBalance.Balance, qryBalance.CompletionDateActual, qryBalance.[SumOfTotal Invoice Cost], qryBalance.TotalInterestNSF, qryBalance.Payments, IIf([Balance],DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date()),"") AS Outstanding
FROM qryBalance
WHERE (((qryBalance.Balance)>1));

Tom

THWatson · Oct 11, 2010

The AceMan1
I should also have posted the SQL from the query "qryBalance" which "qryBalanceActual" uses and selects only invoices where the balance is greater than $1.

Code:

SELECT qunInvoiceTotals.CompletionDateActual, qunInvoiceTotals.ProjectID, qunInvoiceTotals.FullName, Sum(qunInvoiceTotals.[Total Invoice Cost]) AS [SumOfTotal Invoice Cost], qunInvoiceTotals.ProjectNbr, qunInvoiceTotals.InvoiceNbr, qryInterestNSFTotal.TotalInterestNSF, Format(Nz([SumOfAmount],0),"Currency") AS Payments, Round([SumOfTotal Invoice Cost]+Nz([TotalInterestNSF],0)-Nz([Payments],0),2) AS Balance, qunInvoiceTotals.CustomerID, DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date()) AS Outstanding
FROM qryInterestNSFTotal RIGHT JOIN (qryPaymentsTotals RIGHT JOIN qunInvoiceTotals ON qryPaymentsTotals.ProjectID = qunInvoiceTotals.ProjectID) ON qryInterestNSFTotal.ProjectID = qunInvoiceTotals.ProjectID
GROUP BY qunInvoiceTotals.CompletionDateActual, qunInvoiceTotals.ProjectID, qunInvoiceTotals.FullName, qunInvoiceTotals.ProjectNbr, qunInvoiceTotals.InvoiceNbr, qryInterestNSFTotal.TotalInterestNSF, Format(Nz([SumOfAmount],0),"Currency"), qunInvoiceTotals.CustomerID;

Tom

MajP · Oct 11, 2010

Tom,

I am not sure, but in this line you return an empty string if balance = false
IIf([Balance],DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date()),"")

So that column is now textual. Now you take a dmax on a column that is text. I think it still should work but what happens if you replace the empty string "" with Null or 0?

THWatson · Oct 11, 2010

MajP
Yes, changing the "" to 0 works.

The interesting thing is that what I originally had works just fine on both my laptop and my desktop. The database is for one of my sons-in-law and his "on the side" electrical business. He has a Netbook, and for whatever reason he gets the error on his Netbook.

So I have to find out whether or not making the chance you suggest fixes things at his end.

Tom

MajP · Oct 11, 2010

I am not sure if that is really a fix because it should work

If expr identifies numeric data, the DMin and DMax functions return numeric values. If expr identifies string data, they return the string that is first or last alphabetically.
The DMin and DMax functions ignore Null values in the field referenced by expr. However, if no record satisfies criteria or if domain contains no records, the DMin and DMax functions return a Null

However, there does exist a possibility that this can give you a runtime error

i = Nz(DMax("Outstanding", "qryBalanceActual"), 0)

It is possible that the outstanding field is being cast to a string field because of the ""
IIf([Balance],DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date()),"") AS Outstanding

That means the Dmax returns a string (I think). Therefore I = "" or any other string would give a type mismatch error.

To be safe how about a simpler query that just returns a single record, the max of outstanding.

SELECT Top 1
DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date()) AS Outstanding
FROM
qryBalance
WHERE
qryBalance.Balance>1
Order by
DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date())

But this is also confusing
IIf([Balance],... Balance is a boolean field?
qryBalance.Balance>1 Balance is numeric?

THWatson · Oct 11, 2010

MajP
What I am trying to accomplish here is this:

When the database opens, show all invoices where the balance owing is great than $1.00 and the number of days outstanding is greater than 28 days.

Balance is a currency field.

The IIf line yields the number of days Outstanding.

Tom

MajP · Oct 11, 2010

IIf([Balance],DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date()),"")

AKAIK, that resolves to
If [Balance] is True Then
Outstanding = DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date())
Else
Outstanding = ""

What do you really want to say about balance?
iif(Balance > 1

If Balance is a currency I cannot figure out how that even works. It should only calculate a date if balance = -1 (true).

PHV · Oct 11, 2010

It should only calculate a date if balance = -1 (true)
I think it calculate if balance<>0 (not false)

THWatson · Oct 11, 2010

Well, I see what you are saying, but actually a balance is calculated.

If I change the Balance column criteria to <>0, I get 6 rows showing. One row has a balance of $291.80, outstanding for 77 days. Three of the remaining 5 rows have a negative balance, ($0.01). One remaining row has a positive balance of $0.01, and the last has a positive balance of $0.02.

The small negative or positive balances have to do with rounding. Sometimes, the user makes calculations in the field and the customer pays...and then when he enters the stuff into the computer the rounding ends up differently.

All I want to show is the one customer that has a Balance of $291.80 ... plus, of course, any other customers where there is a balance more than $1 and overdue by 28 days or more.

Tom

THWatson · Oct 11, 2010

A picture from the query might help. (providing these columns maintain themselves properly)

Here are the results with Balance criteria <>0

Code:

ProjectID	CustomerID	Balance	Outstanding
7	           5	         ($0.01)	0
10	           8	          $0.01	        0
38	           25	         $291.80	77
32	           19	         ($0.01)	0
34	           20	         ($0.01)	0
37	           24	          $0.02 	0

Here are the results with Balance criteria >1

Code:

ProjectID	CustomerID	Balance	Outstanding
38	            25	        $291.80	        77

Tom

SkipVought · Oct 11, 2010

...the user makes calculations in the field ...

Not a very controlled environment, especially when it comes to money and legal obligation.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

THWatson · Oct 11, 2010

I'm getting lost in the translation here.

Is what is being said that the SQL should read

Code:

SELECT qryBalance.ProjectID, qryBalance.ProjectNbr, qryBalance.FullName, qryBalance.CustomerID, qryBalance.InvoiceNbr, qryBalance.Balance, qryBalance.CompletionDateActual, qryBalance.[SumOfTotal Invoice Cost], qryBalance.TotalInterestNSF, qryBalance.Payments, [COLOR=blue]IIf([Balance]<>0[/color],DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date()),0) AS Outstanding
FROM qryBalance
WHERE (((qryBalance.Balance)>1));

I have added the "<>0" in the portion of code in blue.

Skip, I agree with you with respect to your controlled environment statement. His work is done in a very small rural community, where sometimes people would just as soon pay on the spot and have him send the official invoice later. Ideally, he might carry his Netbook and a printer with him, but things aren't at that stage yet.

Tom

THWatson · Oct 12, 2010

I have changed the SQL in qryBalanceActual to the following:

Code:

SELECT qryBalance.ProjectID, qryBalance.ProjectNbr, qryBalance.FullName, qryBalance.CustomerID, qryBalance.InvoiceNbr, qryBalance.Balance, qryBalance.CompletionDateActual, qryBalance.[SumOfTotal Invoice Cost], qryBalance.TotalInterestNSF, qryBalance.Payments, qryBalance.Outstanding
FROM qryBalance
WHERE (((qryBalance.Balance)>1));

The # of Outstanding days is already calculated in qryBalance which is the basis for qryBalanceActual ... so this only takes those instances where the Balance is greater than $1.00

Now, in the splash form, I can change the piece of code that checks for balances greater than 28 days to

Code:

Dim i As Integer
i = DLookup("Outstanding", "qryBalanceActual")

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

This uses DLookup rather than DMax.

Is this the fix?

Tom

MajP · Oct 12, 2010

You still can make this simpler and more efficient.

qryGreaterThan28:

SELECT qryBalance.ProjectID
FROM qryBalance
WHERE qryBalance.Balance>1 AND qryBalance.outstanding > 28

if dcount("ProjectID","qryGreaterThan28") > 0 then....

THWatson · Oct 12, 2010

Yep, that works.

Here, all along I thought I was being told that I couldn't put the criteria of >1 in the Balance column because that wouldn't work, and that's where I got blocked

Thanks, MajP.

Tom

THWatson · Oct 12, 2010

Thanks as well to TheAceMan1 and PHV for their assistance.

Tom

THWatson · Oct 14, 2010

This is starting to drive me bananas! In spite of the fact that everything runs perfectly on 2 computers of mine, the runtime error 3075 still occurs on the user's Netbook computer...to the extent that it prevents the "frmSplash" from completely opening.

Here's the entire sequence of events that is supposed to happen:
When the program is fired up, it runs "frmSplash" which has the following code:

Code:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
Call Set_Links
Call SetEnvelope("rptEnvelopes")
Me.TimerInterval = 600
End Sub

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"

If DCount("ProjectID", "qryBalanceActual") > 0 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

The new SQL for "qryBalanceActual" is

Code:

SELECT qryBalance.ProjectID, qryBalance.ProjectNbr, qryBalance.FullName, qryBalance.CustomerID, qryBalance.InvoiceNbr, qryBalance.Balance, qryBalance.CompletionDateActual, qryBalance.[SumOfTotal Invoice Cost], qryBalance.TotalInterestNSF, qryBalance.Payments, qryBalance.Outstanding
FROM qryBalance
WHERE (((qryBalance.Balance)>1) AND ((qryBalance.Outstanding)>28));

This query uses "qryBalance" and the SQL for that query is

Code:

SELECT qunInvoiceTotals.CompletionDateActual, qunInvoiceTotals.ProjectID, qunInvoiceTotals.FullName, Sum(qunInvoiceTotals.[Total Invoice Cost]) AS [SumOfTotal Invoice Cost], qunInvoiceTotals.ProjectNbr, qunInvoiceTotals.InvoiceNbr, qryInterestNSFTotal.TotalInterestNSF, Format(Nz([SumOfAmount],0),"Currency") AS Payments, Round([SumOfTotal Invoice Cost]+Nz([TotalInterestNSF],0)-Nz([Payments],0),2) AS Balance, qunInvoiceTotals.CustomerID, DateDiff("d",[qunInvoiceTotals].[CompletionDateActual],Date()) AS Outstanding
FROM qryInterestNSFTotal RIGHT JOIN (qryPaymentsTotals RIGHT JOIN qunInvoiceTotals ON qryPaymentsTotals.ProjectID = qunInvoiceTotals.ProjectID) ON qryInterestNSFTotal.ProjectID = qunInvoiceTotals.ProjectID
GROUP BY qunInvoiceTotals.CompletionDateActual, qunInvoiceTotals.ProjectID, qunInvoiceTotals.FullName, qunInvoiceTotals.ProjectNbr, qunInvoiceTotals.InvoiceNbr, qryInterestNSFTotal.TotalInterestNSF, Format(Nz([SumOfAmount],0),"Currency"), qunInvoiceTotals.CustomerID;

As I understand it, runtime error 3075 means there is a missing operator somewhere, so why this error occurs makes no sense to me.

Since changing the SQL for "qryBalanceActual" to that suggested by MajP, the error on the Netbook computer no longer highlights a line, and it doesn't give a Debug option. It just says there is an error "in procedure Form_Timer of VBA Document Form_frmSplash" and quits without allowing the form to open.

Even if I put in additional code to branch around the Err.Number 3075 it makes no difference.

Does it make any sense to anyone why this occurs, and just on that one computer, not on mine?

Tom

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Runtime error 3075 1

Technical User

Programmer

Programmer

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

MIS

Technical User

Technical User

Programmer

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor