Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-28
8
Medium Priority
?
680 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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