Results 1 to 11 of 11
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    SendForm sends the contents of the entire table (Access 2000)

    Scenario:
    I've included a table of Change Requests in my database, in the form based on my table there is a button that will generate an email from the db to me to see what the user change requests are.
    I'm using the following syntax on the button:
    <pre>DoCmd.SendObject acSendForm, "frm_ChangeLog", acFormatRTF, "firstemailaddress", _
    "2ndemailaddress", , "Database Change Request in RTF format", , yes
    </pre>


    Problem - it sends the contents of the entire table, which isn't a problem right now - but I can see it driving me crazy shortly.

    Any suggestions about extracting and email the current record only?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendForm sends the contents of the entire table (Access 2000)

    Create a global variable in a mod and update it to the proper criteria in code before calling the DoCmd. Then add this code behind the report.

    Private Sub Report_Open(Cancel As Integer)
    Me.Filter = "AssignedTo = " & "'" & gsGlobalVariable &

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: SendForm sends the contents of the entire table (Access 2000)

    Care to expand on your thought process on update it to the proper criteria in code ?

    Thanks for your help
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: SendForm sends the contents of the entire tabl

    Loren Ann's suggestion of filtering will work.

    You could add filtering code to the code behind the button that sends the email. The following code worked for me with a form where [Teacher_Id] was the key field.

    Dim strfilter As String
    strfilter = "[Teacher_id]= " & Me![Teacher_ID]
    Me.Filter = strfilter
    Me.FilterOn = True
    DoCmd.SendObject acSendForm, "frmTeachingStaff", acFormatRTF, "john@somewhere.net.au", , , "Teacher Details", , True


    A question to consider is whether you want to remove the filter after sending the email.
    Regards
    John



  5. #5
    Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendForm sends the contents of the entire table (Access 2000)

    The global variable is dimmed as 'Public gsGlobalVariable As String' just below the line 'Option Explicit' and before any subs or functions in a module. Variables like this are frowned upon because they can cause problems so be careful.

    Set the gsGlobalVariable equal to the key in the current field before you call the DoCmd to send the e-mail.

    Use

    Me.Filter =

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

    Re: SendForm sends the contents of the entire table (Access 2000)

    Here is a variation on Loren Ann's idea:

    I assume that the records in the Change Requests table have some kind of unique identifier; say a numeric field named ID. Create a query based on the table, and set the criteria for the ID field to Forms!frm_ChangeLog!ID. You can send this query. It will pick up the ID from the current record in the form.

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: SendForm sends the contents of the entire table (Access 2000)

    Thanks John and Loren Ann, I think I get it now - but I'm getting an error msg when I try to apply the filter
    "you have cancelled the previous operation"

    this is the code so far:
    Dim curChangeRequestNumber As String
    curChangeRequestNumber = Me.ChangeRequestNumber.Value
    Me.Filter = "ChangeRequestNumber = 'curChangeRequestNumber'"
    Me.FilterOn = True


    I haven't played with filters (usually I go straight to a query), so please be patient with me.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: SendForm sends the contents of the entire table (Access 2000)

    You can send this query

    um, where?
    I'm feeling very thick about this today
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: SendForm sends the contents of the entire table (Access 2000)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Queries can be mailed too! I don't know why I only thought forms could be mailed. So, skip that previous question Hans.
    I'm off for more coffee, MUCH more coffee.....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: SendForm sends the contents of the entire table (Access 2000)

    Perfect, got the button to send a form based on a query - its' working fine now.

    I'm still curious about my difficulties with filtering though...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: SendForm sends the contents of the entire table (Access 2000)

    You can't include a variable directly in a string like this:<pre>Me.Filter = "ChangeRequestNumber = 'curChangeRequestNumber'"</pre>

    Instead, use concatenation:<pre>Me.Filter = "ChangeRequestNumber = '" & curChangeRequestNumber & "'"</pre>

    The message you get probably means that the filter wasn't recognized. By the way. is ChangeRequestNumber really a text field?

Posting Permissions

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