Access SendEmail Macro

Hi

If I understood you right  you need to change the below values

Tbl_Asset_Details].[CheckedInLocation]
Tbl_Asset_Details].[CheckedInBy]

to the ones you want

So do a select and see which fields you need and replace those with the above two
Select * from Tbl_Asset_Details

Thank You Kanti,

Those are actually the fields i want...the problem is that the table is set with a "look up" data type and so its a drop down field to select from. Therefore instead of returning the selected value of those fields, access is returning the ID value of the linked table.

Hope this makes sense..

Thanks!

You need to do the lookups in the data macro yourself, store the values you need in local variables, then use those variables in the SendeMail command.

Jim.

Example:

/* Get the name of the technician  */
Look Up A Record In tblTechnicians
    Where Condition =[tblTechnicians].[ID]=[Tbl_Asset_Details].[CheckedInBy]
SetLocalVar
    Name TechName
    Expression [tblTechnicians].[FirstName] & " " & [tblTechnicians].[LastName]
/* End LookUpRecord  */

Now you can use [TechName] as part of the send e-mail.

Jim.

hello all,

ok so based on Dale's suggestion (Thanks You).......i was able to manipulate the form and added a text field that is = to the combobox and that did the trick but now i am stuck here:

i have this code:

Sub AuditChanges(IDField As String)
    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tbl_Audit_history", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    For Each ctl In Screen.ActiveForm.Controls
        If ctl.Tag = "Audit" Then
            If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                With rst
                    .AddNew
                    ![DateTime] = datTimeCheck
                    ![Technician] = strUserID
                    ![TableName] = "TBL_Asset_Details"
                    ![location] = Screen.ActiveForm.Text183.Value
                    ![SerialID] = Screen.ActiveForm.Combo136.Value
                    ![FieldName] = ctl.ControlSource
                    ![OldRecord] = ctl.OldValue
                    ![NewRecord] = ctl.Value
                   ' MsgBox ("hi")
                    .Update
               End With
              End If
           end if
                 Next ctl
                     
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit

End Sub

i would like to add another "if" to check the ctl.tag properties for a different name and add the fields as below:

If ctl.Tag = "Auditshp" Then
                   If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                    With rst
                    .AddNew
                    ![DateTime] = datTimeCheck
                    ![Technician] = strUserID
                    ![TableName] = "TBL_Asset_Details"
                    ![AssignedTo] = Screen.ActiveForm.Text189.Value
                    ![location] = Screen.ActiveForm.Text185.Value
                    ![SerialID] = Screen.ActiveForm.Combo144.Value
                    ![FieldName] = ctl.ControlSource
                    ![OldRecord] = ctl.OldValue
                    ![NewRecord] = ctl.Value
                    .Update

How do i combine the two?
HELP!!...& Thank you!

Hi

If ctl.Tag = "Auditshp" or ctl.Tag = "anothername" Then

Thanks all,

solved! by using Select case ..

I've requested that this question be closed as follows:

Accepted answer: 0 points for adauser's comment #a40881373

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.