Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limiting an output spreadsheet (2000)

    My form has a button on it which puts together an email based on the information being displayed on the form as follows:

    Dim stDocName As String

    stDocName = "FrmActualUplift"

    DoCmd.SendObject acSendForm, stDocName, "MicrosoftExcel(*.xls)", (EngineerName), , , "Free Issue Materials ", "Please see the attached Report. " & vbCrLf & vbCrLf & "Regards" & vbCrLf & vbCrLf & "Graeme"

    This works ok but the resulting spreadsheet lists all the records in the query on which the form is based.

    How would I limit the spreadsheet to contain only the record being displayed on the form? The unique field is OrderNo

    Regards

    Graeme

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limiting an output spreadsheet (2000)

    You will have to create a query that returns the correct record, and send that query instead of the form. The query should contain the OrderNo field, with criteria referring to the current OrderNo on the form:

    [Forms]![NameOfTheForm]![OrderNo]

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting an output spreadsheet (2000)

    Thanks Hans

    Now I'm getting the error message:

    The SendObject action was canceled

    Do you have any clues why this might happen?

    Regards

    Graeme

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limiting an output spreadsheet (2000)

    If SendObject encounters an error, Access will display this error message. Have you changed acSendForm to acSendQuery?

  5. #5
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting an output spreadsheet (2000)

    Ok, got rid of that error message.

    I need to run the query as it is to list and scroll through all the records. So I've made a copy of the query and put the Forms!formname!orderID in the orderID criteria of the second query as you advised. Then I made a copy of the form and based this second form on the second query.

    It's a bit long winded but it works in that it sends a email to the correct engineer with a spreadsheet attached listing only the relevant data.

    However, it only works once!!! After the first email is created and sent, a subsequent click of the button does nothing. If the form is closed, if the first form is closed or the database window is closed and the database opened again, nothing happens with subsequent clicks of the email button. The only way to get another email to send is to shut down Access and start it up again.

    Do these symptoms sound familiar?

    My son and I have however nearly got to the end of Ratchet and Crank one on the Playstation!

    Regards

    Graeme

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limiting an output spreadsheet (2000)

    1. I don't understand why you need to create a second form - sending the second query (the one with the OrderID criteria) is much more efficient than sending a form.

    2. Use Alt+Tab to see if there is a hidden message box that is waiting for input after clicking the button once.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting an output spreadsheet (2000)

    Hans

    If you can remember this one a week later, I don't know why I wanted to send the second form either! I've changed the code to send the second query and it works a treat.

    However, it still only works once! The only way to send a second email is to shut down the database and fire it up again. Any clues as to why this might happen would be gratefully accepted.

    Regards

    Graeme

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limiting an output spreadsheet (2000)

    Have you checked my second point?
    <hr>2. Use Alt+Tab to see if there is a hidden message box that is waiting for input after clicking the button once.<hr>
    Apart from that, have you installed Office 2000 SP3? According to SendObject method fails in Access 2000, problems with SendObject have been fixed in SP3.

  9. #9
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting an output spreadsheet (2000)

    Yes Hans, sorry I should have said, I've looked for messages hiding under windows.

    I'm using Access 2000 and Outlook 2000 SR-1 running on Windows NT 4. So I guess the problem wasn't fixed back in the days NT4 was invented!

    Untill we get some post medieval software here, I suppose I could add all the Engineer's names to the one email To: box, with a spreadsheet attached listing all the results of the first query and send that out before it crashes!

    Regards

    Graeme

  10. #10
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting an output spreadsheet (2000)

    Hans

    After reading the information on the link you posted above I've shortened the email message and it works almost all of the time now.

    It still failed in an instance where the Engineer did not have an email address listed and I canceled the Outlook "Create new address for this person" message box.

    Other than that I'll go sort out SP3 with our IT people.

    Thanks again.

    Regards

    Graeme

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •