Solved

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

Posted on 2014-01-28
8
662 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
  • 4
  • 3
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 84
Comment Utility
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
 
LVL 28

Author Comment

by:mikebernhardt
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now