?
Solved

Report generation button worked in Access 2003, doesn't work correctly in 2010

Posted on 2014-01-28
8
Medium Priority
?
677 Views
Last Modified: 2014-02-03
I have a MS access database that was built in Access 2003. It keeps track of the cabling infrastructure in our data center.

All was good until I upgraded to 2010 some time ago. Since then, one report doesn't work unless I supply the information that should have been carried from the current viewing filter. the report is supposed to provide the list of all cables in the current filter.

When I built it several years ago I had barely any idea what I was doing, now I have far less idea what I did. So I need to know why it doesn't work and what to do about it.

Here is the scripting behind the form on which the button sits. Let me know what else you need to know:
Option Compare Database



Private Sub CableNumber_AfterUpdate()
Me.Refresh
End Sub

Private Sub Form_Open(Cancel As Integer)
      MsgBox ("Remember that all changes to records are written immediately. Be careful not to edit the wrong records!")
      setFilter
End Sub

Private Sub Print_Click()
'Check if the filter is enabled
If Len(Me.Filter) > 0 And Me.FilterOn = True Then
    DoCmd.OpenReport "FormReport", acViewPreview, OpenArgs:=Me.Filter
Else
    DoCmd.OpenReport "FormReport", acViewPreview
End If
End Sub

Private Sub SelectCable_AfterUpdate()
   setFilter
End Sub

Private Sub SelectHost_AfterUpdate()
   setFilter
End Sub

Private Sub SelectLoc_AfterUpdate()
   setFilter
End Sub

Private Sub SelectModel_AfterUpdate()
    setFilter
    Me!SelectSwitch = ""
    Me.Refresh

End Sub

Private Sub SelectModule_AfterUpdate()
    setFilter
End Sub

Private Sub SelectPort_AfterUpdate()
    setFilter
End Sub

Private Sub SelectSwitch_AfterUpdate()
    setFilter
        Me.Refresh
End Sub
Private Sub UsedPorts_AfterUpdate()
    setFilter
End Sub

Private Sub RefreshButton_Click()
On Error GoTo Err_RefreshButton_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    Me.Refresh
    Me.OrderByOn = True
    Me.OrderBy = "s.module, s.port, s.switch"

   
Exit_RefreshButton_Click:
    Exit Sub

Err_RefreshButton_Click:
    MsgBox Err.Description
    Resume Exit_RefreshButton_Click
   
End Sub
Sub setFilter()
  Dim sFilter As String
 
  'Dummy criteria that is always true, so you can append the other criteria easier
  sFilter = "1=1"
 
  'First check if both selectCable and SelectHost have values, if so clear all boxes and generate a message
  If Nz(Me!SelectCable) <> "" And Nz(Me!SelectHost) <> "" Then
      Me!SelectHost = ""
      Me!SelectCable = ""
      sFilter = "1=1"
      MsgBox ("You can't search for a Cable and a Host at the same time. Try your search again.")
  End If
 
  'Check if selectCable has a value, if so clear the other boxes
  If Nz(Me!SelectCable) <> "" Then
    Me!SelectSwitch = Null
    Me!SelectModule = ""
    Me!SelectPort = ""
    Me!SelectHost = ""
    Me!SelectLoc = Null
    Me!SelectModel = Null
    Me!UsedPorts = ""
    sFilter = sFilter & " and [cablenumber]= '" & Me!SelectCable & "'"
  Else
    'Check if SelectHost has a value, if so clear the other boxes
  If Nz(Me!SelectHost) <> "" Then
    Me!SelectSwitch = Null
    Me!SelectModule = ""
    Me!SelectPort = ""
    Me!SelectCable = ""
    Me!SelectLoc = Null
    Me!SelectModel = Null
    Me!UsedPorts = ""
    sFilter = sFilter & " and [endhost]= '" & Me!SelectHost & "'"
  Else
    'Build the filter based on multiple criteria since SelectCable and SelectHost are blank
    If Nz(Me!SelectLoc, "") <> "" Then sFilter = sFilter & " and [location]= '" & Me!SelectLoc & "'" Else sFilter = sFilter & " and [location]= Null "
    If Nz(Me!SelectModel, "") <> "" Then sFilter = sFilter & " and [model]= '" & Me!SelectModel & "'" Else sFilter = sFilter & " and [model]= Null "
    If Nz(Me!SelectSwitch, "") <> "" Then sFilter = sFilter & " and [switch]= '" & Me!SelectSwitch & "'"
    If Nz(Me!SelectModule, "") <> "" Then sFilter = sFilter & " and [module]= " & Me!SelectModule
    If Nz(Me!SelectPort, "") <> "" Then sFilter = sFilter & " and [port]= " & Me!SelectPort
    If Nz(Me!UsedPorts, False) = True Then sFilter = sFilter & " and [endhost] is not null"
  End If
 End If

  'Check if any of the boxes was set, if not clear the filter
  'If sFilter = "1=1" Then
  '  Me.FilterOn = False
 ' Else
    Me.Filter = sFilter
    Me.FilterOn = True
  'End If
  Me.Refresh
End Sub


Private Sub View_Click()
Application.FollowHyperlink Me.Image.Column(1), , True
End Sub
0
Comment
Question by:mikebernhardt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39815977
check if first if you build the filter correctly

Private Sub Print_Click()
'Check if the filter is enabled
If Len(Me.Filter) > 0 And Me.FilterOn = True Then

debug.print me.filter      'ADD this line and see the result from the immediate window


    DoCmd.OpenReport "FormReport", acViewPreview, OpenArgs:=Me.Filter
Else
    DoCmd.OpenReport "FormReport", acViewPreview
End If
End Sub
0
 
LVL 28

Author Comment

by:mikebernhardt
ID: 39816023
Well, I don't see any results from that debug line... the button opens a print preview window from which I would normally print the report.

But here's another clue: The filtering does work correctly. All the button is supposed to do is use the existing filter, and it does create the report correctly. But it asks for input for S.Module, S.Port, and S.Switch. before creating it, as if there's something missing from the query that used to be provided.

I just found that I don't even have to enter anything into those fields, I can leave them blank just click "OK" and the report will print correctly.
0
 
LVL 85
ID: 39816356
When Access prompts you for data like that, it means it cannot find those data items.

You can review the Recordsource for that report to see what/where "S.Module" an the others are sourced. Make sure that sourcing is correct - table names are correct, field names, etc.

If all seems well you might try compacting your database. Make a backup first, and then open the database in 2010 and click the File button, then click compact. See if that resolves things.
0
Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

 
LVL 28

Author Comment

by:mikebernhardt
ID: 39818479
This used to work- it's only been an issue since I upgraded my desktop to 2010 last year. Clicking on the button runs this, and I suspect that's where the empty fields are coming from, The information in those fields is displayed correctly, so it can't be that they are incorrect or something:
Private Sub Report_Open(Cancel As Integer)
  Me.Filter = "1 = 1"
  If Len(Me.OpenArgs) > 0 Then
    Me.Filter = OpenArgs
    Me.FilterOn = True
    Me.OrderBy = "S.Module, S.Port, S.Switch"
    'DoCmd.Maximize
  End If

End Sub
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39818574
try removing this line

Me.OrderBy = "S.Module, S.Port, S.Switch"

and see if the prompt goes away


btw, what is the record source of your report ?

are these fields ( "S.Module, S.Port, S.Switch" ) in the record Source ?
0
 
LVL 28

Author Comment

by:mikebernhardt
ID: 39818651
Huh. The prompts go away, and the report is still sorted the way I want anyway.

The source is from a query, which calls from a number of tables including the 3 above. Is that what you're asking?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39818681
you can get rid of this report open event

Private Sub Report_Open(Cancel As Integer)
  Me.Filter = "1 = 1"
  If Len(Me.OpenArgs) > 0 Then
    Me.Filter = OpenArgs
    Me.FilterOn = True
   ' Me.OrderBy = "S.Module, S.Port, S.Switch"
    'DoCmd.Maximize
  End If

End Sub 

Open in new window


if you will change the way you open the report using

 
DoCmd.OpenReport "FormReport", acViewPreview, OpenArgs:=Me.Filter

Open in new window


to

 
DoCmd.OpenReport "FormReport", acViewPreview,, Me.Filter

Open in new window

0
 
LVL 28

Author Closing Comment

by:mikebernhardt
ID: 39830420
Hadn't thought of this, and since removing the sort eliminates the problem without affecting the result, it's a great solution. Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question