Access SendEmail Macro

Hello,

I have a table with an after update "Send email" macro as follows:

> ="The following Asset has been Checked in at: " & ([Tbl_Asset_Details].[CheckedInLocation]) & Chr$(13) & Chr$(10) & "Technician: " & ([Tbl_Asset_Details].[CheckedInBy]) & Chr$(13) & Chr$(10) & "Serial Number: " & ([Tbl_Asset_Details].[SerialID])

The result looks something like this:

The following Asset has been Checked in at: 371
Technician: 116
Serial Number: 123458

what is happening is that instead of the actual text of the field, i get the ID of location and technician.

what i need is for the code to be able to get the selected value of the drop down box and send and the actual value and not the ID. Is there a way to get this done? if this will not work; is there a vba i can use to accomplish the same result? THANK YOU!

Ex:
The following Asset has been Checked in at: CALIFORNIA
Technician: JOE SMITH
Serial Number: 125478

THANK YOU!
adauserAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:
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
adauserAuthor Commented:
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!
Dale FyeOwner, Developing Solutions LLCCommented:
Is this being edited from a form? or are you simply working in the table with a data macro?

Using the "lookup" feature in tables is BAD, BAD, BAD practice.  Microsoft included this "feature" to make it easier for newbies, but it just confuses them.  The value that is stored in these fields is the ID value, but what shows up when you look at the table is the text, very confusing!

Instead, you should use a form for your data entry and use a combo box that uses the "lookup" table as the RowSource for the combo.

If you are using a form for your data entry, you might be able to replace:

[Tbl_Asset_Details].[CheckedInLocation]

with:

Forms!yourFormName.yourComboControlName.column(1)

Combo boxes have a column property which allows you to get the data from any of the columns in the combo boxes RowSource.  This property is zero based, so to get the 2nd column, you would need to refer to controlname.column(1)

I don't have a lot of experience with data macros, so I don't know whether this form reference will work, but it is worth a try.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
adauserAuthor Commented:
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!
Kanti PrasadCommented:
Hi

If ctl.Tag = "Auditshp" or ctl.Tag = "anothername" Then
adauserAuthor Commented:
Thanks all,

solved! by using Select case ..
Martin LissOlder than dirtCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.