If no record .. (DSUM)

[mtompkins]

I'm trying to have a form close and openen another form to indicate there are no records yet in the table.

I'm trying on the form's Form_Load :

If IsNull(DSum("[Units Received]", "Inventory Transactions")) Then

DoCmd.OpenForm ("frmNoInventory")
DoCmd.Close

End If

... however this returns a run-time error 2001 "You cancelled the previous action."

I'm trying to sum the "Units Received" field from table "Inventory Transactions". If there are no entries DSum should come back true.

Help would be apprecaited...

Thanks,
mark

 

[PHV]

In the Open event procedure of the form:
If IsNull(DSum("[Units Received]", "[Inventory Transactions]")) Then
Cancel = True
DoCmd.OpenForm "frmNoInventory"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mtompkins]

Hi PHV -

Same error as my attempt.

 

[PWise]

DoCmd.OpenForm "frmNoInventory",,,,,acDialog

DoCmd.Close acfrom , "the name of the form"

 

[mtompkins]

Hi Pwise -

thanks for your input. Same result also.
It would appear the problem resides with the DSum.

Kindest,
Mark

 

[PHV]

Why not not open the form if no inventory ?
If IsNull(DSum("[Units Received]", "[Inventory Transactions]")) Then
DoCmd.OpenForm "frmNoInventory"
Else
DoCmd.OpenForm "your inventory form"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mtompkins]

PHV - Because the control is on the switchboard and it becomes troublesome.

It should do the check on the "your inventory form". Many forms will point to this form to open.

 

[PWise]

it works on my end

by this "DoCmd.Close acfrom , "the name of the form""
i mean the inventory form not the no inventory form

 

[PHV]

The error message you get is generated by the DoCmd.OpenForm "your inventory form" being cancelled.

You may try to put your checking code in the Current event instead of the Load one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mtompkins]

I replaced the open form with a msgbox to confirm as I believe the problem is with the DSUM.

If IsNull(DSum("[Units Received]", "[Inventory Transactions]")) Then
    MsgBox ("STOP")
End If

same error generated.

This error is also generated when the code is in the Current event.

 

[missinglinq]

Since DSUM seems to be the trouble why not use DCount? I use a sub similar to this for the same purpose:

Dim Msg As String
intX = DCount([Units Received], "InventoryTransactions")
If intX < 1 Then
Msg = "There is no data in the table yet"
MsgBox Msg, vbOKOnly + vbInformation
DoCmd.Close acForm, "YourForm"
Else
'''Continue opening your form
End If

End Sub



There's ALWAYS more than one way to skin a cat!

 

[mtompkins]

Hi MissingLinq -

Thanks for yours. This worked. I was hoping to be able to sum the qty so that I can use a >0 filter . . . but I'll head another direction to sort that.

For those interested final code below

    intX = DCount("[UnitsReceived]", "Inventory Transactions")
    If intX < 1 Then
        Msg = "There is no Green Inventory in the table yet!!"
        MsgBox Msg, vbOKOnly + vbInformation
        DoCmd.Close acForm, "frmRoasting"
    End If