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

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
LVL 28
mikebernhardtAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
mikebernhardtAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
mikebernhardtAuthor Commented:
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
 
mikebernhardtAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
mikebernhardtAuthor Commented:
Hadn't thought of this, and since removing the sort eliminates the problem without affecting the result, it's a great solution. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.