Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    MarDude is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2013
    Posts
    11
    Your solution would work, but it would require several steps. The reason we export the report to Excel is it already exists. In other words, users are already looking at the report. so what do we do? Add another button to the selection criteria instead of preview to export. Then they have to look at the report. Close the report. Run the query again. That will not go over well. Users generally do not like to have an extra click added to every step of thier workload. So adding two extra clicks AND a wait for a query is going to make everyone who uses this function unhappy.



    So while your solution technically works, it comes up short in a business sense. Not your fault since you didn't have all the details, but thanks for trying.

    Remember these are the same users who complained about seeing the quotes. While your solution fixes this, it introduces more work.

    I mentioned to them I could write my own export routine. Its not very hard to copy query results to Excel. I can just populate a dataset. ADO has functions to do just that. But then they have to learn a new way to do their job. And I don't blame them for not wanted to get used to something different. sometimes it can be difficult to see things from a user's point of view. They want it to work the way they are used to or not at all. And depending on what they do with the spreadsheet, it may be the best thing for the bottom line.

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The reality is you and users might not have any choice as you are stuck with 2003 and stuck with Excel output. I think it could be done so that new behavior is not so different nor overly burdensome to users.

    Report has Click and DblClick events (I've never used either). So instead of right click, left click on report. However, it is possible those events will fire only in ReportView, not PrintPreview.

    DoCmd.Close acReport, "reportname", acSaveNo
    If MsgBox("Export data now?") = vbYes Then
    'code to export query
    End If

    What event opens the report - a button Click? Can open report with acDialog parameter. This will suspend code execution by the calling procedure until report closes. Then the procedure could continue with the above If Then.

    If people could never get used to something new there would be no progress.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    MarDude is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2013
    Posts
    11
    Your way would work, but it would inconvenience the users. Users don't like extra clicks and I don't blame them. Who wants their job to be harder? While your technique would work and fixes the problem of quotes in the report, it would require them to back out of the report, run the query again (which may or may not be fast). The reason the report is exported to excel is the users are already looking at the report. They need to see they have the right BOM (Bill Of Materials). Some of the part numbers look very similar. I told them I could write my own export routine. It wouldn't be much work since ADO can create a dataset and it is easy to dump the dataset to Excel. But that would make them have to learn a new process, and I can't blame them for not wanting that. They have much more to do than just copy a report to Excel. Remember these are the same users who rejected my adding quotes to the report which to me made more sense than an incorrect itemid. They will definitely balk and anything that makes their job more difficult

    So basically if I can't fix it so it can work as before I don't have to do anything. Fine by me. My job is to make them as happy as then can be.

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Don't understand what you mean by 'run the query again'. SELECT queries don't have to be 'run' unless they have popup input parameters (which I never use because they are an aggravation source for users, unvalidatable, and annoy me).

    Let's get really nitpicky and count clicks:

    1. open report
    2. right click
    3. pick item from menu
    4. X close report

    versus

    1. open report
    2. left click or X close
    3. message box or export command button


    Good luck, hope you find a satisfactory solution and share it here.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    MarDude is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2013
    Posts
    11
    You must've missed the solution I already posted, maybe I did not explain it fully.
    Quote Originally Posted by MarDude View Post
    I used double quotes.
    Problem:
    Using the send function found by right-clicking on an Access report to attach an Excel spreadsheet to an e-mail causes data corruption.

    Result:
    A text field in the format of 20####-00# will be treated as a date. Excel will do some sort of conversion. For example 202103-001 becomes 1313049 and 205103-001 becomes 2408777.

    Solutions:
    1) Enclose the field in quotes.
    2) Prefix the text field with another character besides space.
    3) Write your own custom export function.

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I thought the users rejected that solution and you were back to square one. Oh well, as long as you now have it working, congrats.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #22
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    I realize this is marked as Solved already (and that all my other suggestions were shot down!), but I have one more suggestion that mirrors your Solution #3:

    Remove the option for viewing a Report on the data. Replace it with an option to "View this Report in Excel" that runs your custom export function, generates a .XLS file, and then opens it for the user.

    This would allow you to control the output to Excel (by either using Office Automation to force the column's Format or by using a single quote prefix) and give them a pretty report to view. And, at the same time, they would already have their Excel file for sorting and filtering.

  8. #23
    MarDude is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2013
    Posts
    11
    The users did reject my solution. But just because they reject a solution doesn't mean it isn't a solution. And if it is a solution it can help others.
    Quote Originally Posted by June7 View Post
    Good luck, hope you find a satisfactory solution and share it here.
    that is why I reposted it. It seemed you had trouble finding it.

    @Rawb
    Don't feel bad, my solution was shot down also. But I like your idea. It allows then to see their report and I can use the single quote thing you mention earlier without interfering with the output. At least I can't think of anything that can complain about. I'll see what they say.

    Thanks

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access is Corrupted
    By waqas in forum Forms
    Replies: 4
    Last Post: 10-16-2011, 01:23 PM
  2. Database Corrupted
    By waqas in forum Forms
    Replies: 3
    Last Post: 09-14-2011, 01:35 PM
  3. Database corrupted?
    By kctxucop08 in forum Access
    Replies: 3
    Last Post: 07-21-2011, 08:54 AM
  4. Help!! - Corrupted Module
    By rcrobman in forum Modules
    Replies: 3
    Last Post: 04-29-2011, 03:37 PM
  5. Corrupted Database
    By evander in forum Access
    Replies: 1
    Last Post: 04-29-2010, 12:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums