Access Report #Error

[miracleblake]

I continue to experience this problem and can't find a work around my solution. I have a text box "txtBlackberry". I run a report to print the total number of blackberries, everything works fine until there are no blackberries. I then encounter "#error" on my report. Any help would be greatly appreciated.

 

[THWatson]

miracleblake
You might try the HasData property, which is a way to check for an empty recordset in a report or subreport.

Tom

 

[miracleblake]

I still continue to have problems. Let me see if I can explain and maybe you can give me possibly a better way to go.

I have a report that has as a footer. All, PC's, Notebooks, blackberry, cell phone, copier, router, (these are just summing the amount of records of a particular type)(the code is for software is =Sum(Abs([Type]="Software"))etc. When I print the "All" all pc's return a correct count, blackberry returns a correct count, etc. everything is fine. There are NO routers, so the txtRouters box displays 0, which is correct.

However, when I select Router as a report, I make all other (PC's, Notebooks, blackberry, cell phone, etc. invisible, so that they aren't seen). This works fine. But, there is no "type" router in the actual table, and instead of seeing a 0, I get the infamous #error.

Any help again will be greatly appreciated. I have worked on this for days and still have not come up with a solution.

 

[dhookom]

As suggested by Tom, try an expression like:

=IIf(HasData, Sum(Abs([Type]="router")),0)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[miracleblake]

I tried it and it still did not work. The zero does show. However, when I created 1 blackberry type in the table, the number did not change (it remained at 0). Thank you again for your assistance.

 

[dhookom]

What is your exact expression? What is your exact field value?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[THWatson]

miracleblake
I gather that your report is based directly on the table, not on a query. Correct?

What are the fields that are used for the report? Is there a Type field, and also a field that gives the number of units in that Type?
e.g. Blackberry 12
Router 0
Cell Phone 3

Tom

 

[miracleblake]

I have a frm called frmReports. frmReports has the following types of reports: blackberry, router, cell phone, pc, notebook, router, hub. When the user clicks blackberry, I have the following code on the on click:

Private Sub cmdBlackberry_Click()
'Filter database for all Blackberries
'Provide title for report

Dim strReportName As String

strReportName = "Blackberry Compilation Report"
DoCmd.OpenReport "Compilation Report ", acViewPreview, , "type = 'Blackberry'", , strReportName

'Change preview to 75%
DoCmd.RunCommand acCmdZoom75
DoCmd.Maximize
End Sub





WHICH THEN DOES

Option Compare Database

Private Sub Report_Open(Cancel As Integer)
'Pass the title to the report

Dim strReportName As String
strReportName = Me.OpenArgs
Me.lblReportName.Caption = strReportName

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

Dim strReportName As String
strReportName = Me.OpenArgs

'Make certain boxes invisible by the type of report selected
If Me.OpenArgs = "PC Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = True
Me!TxtTotalPC.Visible = True
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

If Me.OpenArgs = "All Items Compilation Report" Then
Me!lblAllComputers.Visible = True
Me!txtTotalComputers.Visible = True
Me!LblTotalPC.Visible = True
Me!TxtTotalPC.Visible = True
Me!LblTotalNotebook.Visible = True
Me!TxtTotalNotebooks.Visible = True
Me!lblPrinter.Visible = True
Me!txtPrinter.Visible = True
Me!lblHub.Visible = True
Me!txtHub.Visible = True
Me!lblRouter.Visible = True
Me!txtRouter.Visible = True
Me!lblSwitch.Visible = True
Me!txtSwitch.Visible = True
Me!lblSoftware.Visible = True
Me!txtSoftware.Visible = True
Me!lblCopier.Visible = True
Me!txtCopier.Visible = True
Me!lblBlackberry.Visible = True
Me!txtBlackberry.Visible = True
Me!lblCellPhone.Visible = True
Me!txtCellPhone.Visible = True
End If

If Me.OpenArgs = "Notebook Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = True
Me!TxtTotalNotebooks.Visible = True
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

If Me.OpenArgs = "Printer Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = True
Me!txtPrinter.Visible = True
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

If Me.OpenArgs = "Copier Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = True
Me!txtCopier.Visible = True
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

If Me.OpenArgs = "Software Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = True
Me!txtSoftware.Visible = True
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

If Me.OpenArgs = "Switch Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = True
Me!txtSwitch.Visible = True
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

If Me.OpenArgs = "Router Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = True
Me!txtRouter.Visible = True
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

If Me.OpenArgs = "Hub Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = True
Me!txtHub.Visible = True
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

If Me.OpenArgs = "Cell Phone Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = False
Me!txtBlackberry.Visible = False
Me!lblCellPhone.Visible = True
Me!txtCellPhone.Visible = True
End If

If Me.OpenArgs = "Blackberry Compilation Report" Then
Me!lblAllComputers.Visible = False
Me!txtTotalComputers.Visible = False
Me!LblTotalPC.Visible = False
Me!TxtTotalPC.Visible = False
Me!LblTotalNotebook.Visible = False
Me!TxtTotalNotebooks.Visible = False
Me!lblPrinter.Visible = False
Me!txtPrinter.Visible = False
Me!lblHub.Visible = False
Me!txtHub.Visible = False
Me!lblRouter.Visible = False
Me!txtRouter.Visible = False
Me!lblSwitch.Visible = False
Me!txtSwitch.Visible = False
Me!lblSoftware.Visible = False
Me!txtSoftware.Visible = False
Me!lblCopier.Visible = False
Me!txtCopier.Visible = False
Me!lblBlackberry.Visible = True
Me!txtBlackberry.Visible = True
Me!lblCellPhone.Visible = False
Me!txtCellPhone.Visible = False
End If

End Sub

 

[dhookom]

By "What is your exact expression?" I meant what is the control source that is showing 0 rather than your count.

I would certainly look at your table structure. If this isn't the result of a crosstab query then I would guess that your table might not be normalized. If you wanted to add "PDA", would this require adding a field to your table?

At the very least, you could place some value in the Tag property of your controls and then write a single loop to hide or display controls. I don't have the time at the moment to do this but perhaps someone else can or I will check back later.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[miracleblake]

Thank you so much for your help. My control is called txtBlackberry. The control source is =sum(abs([type] = "Blackberry"))

In order to add a field, you would have to go to computersInformation table go to design mode, in the type field text and add "PDA" as a rowsource.

 

[dhookom]

Do you realize that your table structure is unnormalized? You are storing values in field names.

However, I would open the report in design mode and select all the appropriate text boxes and label controls and add something to their Tag property like:
Tag: Hello
Then add code to the On Format event of your section containing the controls like:

Dim strType as String
Dim ctl as Control
   'store the common part of the name
   '   in strType depending on OpenArgs
Select Case Me.OpenArgs
   Case "PC Compilation Report"
      strType = "TotalPC"
   Case "All Items Compilation Report"
      strType = "ALL"
   Case "Notebook Compilation Report"
      strType = "Notebook"
   '-- more of these --
End Select
'show or hide the controls based on strType
For Each ctl In Me.Controls
   If ctl.Tag = "Hello"
      ctl.Visible = (Instr(ctl.Name, strType)>0 Or strType = "All")
   End If
Next

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[miracleblake]

Can you tell me what I would need to do to normalize my table?

 

[dhookom]

Can you provide a sample of your table's fields? Generically, consider a table to store a grocery list. Your solution might be a table like:
[tt][blue]
tblGroceryList
=================
ListID Autonumber primary key
ShoppingDate Date/Time
Apples numeric (for qty)
Cereal numeric (for qty)
Bread numeric (for qty)
Milk numeric (for qty)
[/blue][/tt]
A normalized solution would have tables like:
[tt][blue]
tblGroceryList
=================
ListID Autonumber primary key
ShoppingDate Date/Time
Item Text
Quantity numeric
Comments Text (like "Granny Smith" or "Fuji")
[/blue][/tt]

This is a very simplistic example. In real life, you might have a lookup table for items and the Shopping date might be moved to a "parent" table to the grocery list.

The nice feature of this structure is you can easily query/report the quanitity of each item you bought for a date range. You can also add items at any time without mucking around in design views.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[miracleblake]

Table name is ComputerInformation

serialNumber - Number
CLN - Number
StockNumber - Number
Item - Text
Type - Text (row source is pc;notebook;blackberry;copier)
Weight - Number
DVD - Text (row source is reader;writer)

 

[dhookom]

My bad, it looks like your table is fairly normalized. I have too many threads in my head.

Have you just considered using a subreport that has a record source similar to your main report however Groups and Counts Type?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]