Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append to a table based on selection from a form (2003)

    Hi all,

    I have a table (tblAuditTrail) which appends details to it from several parts of my database as changes are made, normally this happens after working on one record at a time. I also use a form to select certain criteria and produce a report that I then email out. I want to be able to append to my audit trail table deatils of each record in the report so I can track the last time a report for those records was produced.

    Also in the code below is how I attach the file to an email. Is is possible to
    1) default the email to a group email I have saved in outlook
    2) use preset text for subject and message area (the words TEST in the code was supposed to do that but don't
    3) Default the export to 'Excel 97-2003 .xls' as at the moment it gives me choices.

    Below is the code I am using.

    Private Sub cmdEmail_Click()
    On Error GoTo Err_cmdEmail_Click

    Dim stDocName As String
    Dim Dbs As Database
    Dim localDbs As Database
    Dim Rst As Recordset

    stDocName = "QryOPStatuswithDemographics"
    DoCmd.SendObject acSendQuery, stDocName, , "Weekly / Monthy 18WRTT Returns", , , TEST, TEST


    Set localDbs = CurrentDb
    Set Rst = localDbs.OpenRecordset("TblAuditTrail")
    With Rst
    .AddNew
    !CRN = DLookup("CRN", "OUT0506", "Validated= " & Chr(34) & Me.cmbType & Chr(34))
    !Reference = "F2 update request (" & Me.cmbType.Value & ")"
    .Update
    End With
    Set Rst = Nothing
    Set Dbs = Nothing
    Set localDbs = Nothing

    Exit_cmdEmail_Click:
    Exit Sub

    Err_cmdEmail_Click:
    MsgBox Err.Description
    Resume Exit_cmdEmail_Click

    End Sub

    The table OUT0506 is where there query gets it data from. The code above errors with 'you cancelled your last action'

    Many thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

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

    Re: Append to a table based on selection from a form (2003)

    The subject and message text should be quoted strings (or string variables).

    You can use something like this:

    DoCmd.SendObject acSendQuery, stDocName, acFormatXLS, "nameofgroup", , , "TEST Subject", "TEST Message"

    If you want to know which line causes the error, you should temporarily comment out the line

    On Error GoTo Err_cmdEmail_Click

    by inserting an apostrophe in front of it. When the error occurs, click Debug and see which line is highlighted.

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

    Re: Append to a table based on selection from a form (2003)

    If there is more than one matching record, DLookup will return the first one it finds.

    What kind of field is Validated?
    - A text field
    - A number field or currency field
    - A date/time field
    - A yes/no field

  4. #4
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append to a table based on selection from a form (2003)

    <P ID="edit" class=small>(Edited by kemorr on 18-Aug-08 09:13. )</P>Thanks Hans,

    Email part works fine now.

    The error line is
    !CRN = DLookup("CRN", "OUT0506", "Validated= " & Chr(34) & Me.cmbType & Chr(34))

    Could it be becuase there is more than one matching record in the source table.

    OK, well it should be DLookup("F_PATTID", not "CRN"
    But it only adds one record to the audit trail not all 79, and wierdly it does select the first or last but one in the middle.

    How would I make this add all in the list, would I use some kind of loop, of would I be better off using an append query. I don't really want to use a query as there are so many of them I would rather do it in code.

    Thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: Append to a table based on selection from a form (2003)

    What does it matter if you have many queries in your database? A stored query is usually more efficient than VBA code.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append to a table based on selection from a form (2003)

    Yes a stored query would perhaps be more efficent, however if you look at the code I want to add a specfic text to the reference field of the tblaudittrail table. This text is slightly different depending on the combo box selection.

    I can't see a way of doing this with a query.

    Thanks Hans
    Regards
    Gerbil (AKA Kevin)

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

    Re: Append to a table based on selection from a form (2003)

    You can refer to the value of the combo box in a query as follows:

    [Forms]![NameOfTheForm]![cmbType]

    where NameOfTheForm is the name of the form.

  8. #8
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append to a table based on selection from a form (2003)

    Thanks Hans,

    I did know that, but that won't quite work unless I'm missing something.

    The reference field will for example say. "Update request sent to " & cmbType

    Where "update request sent to " is not stored anywhere in any table only in the code

    Are you saying I can somehow use the text in a query.

    Thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: Append to a table based on selection from a form (2003)

    In an update query, you could set the Update To line for the Reference field to

    "Update request sent to " & [Forms]![NameOfTheForm]![cmbType]

Posting Permissions

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