Automated Email Question

[siggybaby_18]

On one of my forms, I have a macro-enabled button called "Submit Report" which generates and sends an email via Outlook on-click. The code for the body of the email is as follows:



=IIf([Form].[Description].[TextFormat]=1,PlainText([Description] & "Reported by: " & [Reported by]),[Description] & " Reported by: " & [Reported by])

Now I tested the email and "Reported by" populates the auto number instead of the actual employee name. How can I change the code where it populates the employee name?

"Reported by" row source is: SELECT [Contacts Extended].ID, [Contacts Extended].[Group Name] FROM [Contacts Extended] ORDER BY [Contacts Extended].[Group Name] (a query)

Form record source is: Task (a table)

Any help would be much appreciated!

[RuralGuy]

It looks like you may be using Lookup Fields: http://access.mvps.org/access/lookupfields.htm

[siggybaby_18]

Correct. I read that article, but not much help how to go about modifying the code I provided. Or do I change from lookup to something else? Employees need to selecte their name from dropdown box. So I am confused.

[RuralGuy]

Lookup's (combo boxes) on a form are just fine and very useful. When in a field in a table, they hide what is really in the table and make it difficult to figure out what to do. http://www.btabdevelopment.com/ts/removelookups

[siggybaby_18]

I just did that, but the body of my email is still kicking out the auto ID instead of the employee name. Again...this is what i have for the body of my email :
=IIf([Form].[Description].[TextFormat]=1,PlainText([Description] & "Reported by: " & [Reported by]),[Description] & " Reported by: " & [Reported by])
The reported by is where my problem lies....it pulls from a query...and pulls the auto id instead of the name

[RuralGuy]

You need to modify your existing query to join the other table on the [Reported by] field and then add in the actual field you need and use *that* instead of the [Reported by] field which only contains the PK of the other table.

[siggybaby_18]

I added the "Task" table to my "Contacts Extended" query as you suggested .... It automatically had joined "Assigned To" (task table) to "ID" (contacts table) .... I then joined "Reported By" (task table) to the same "ID" (contacts table) as you suggested, but it still didn't work ... as a matter of fact, doing that removed the "Reported By" values from my form.

[RuralGuy]

We're going around in circles. If you want to zip up your db and attach it to a post we could look at it to see what is going on. Remove any sensitive data of course.

[siggybaby_18]

See attached. Sections that I am talking about are, that I need your review on are these:

1-"Task Details" form - Email button ..... when email is generated, as you will see, the "Report By" in the body pulls the "ID" value instead of the employee name

2-It's also doing the same thing in the "Completed Tasks By Date" report - cateory tab ..... it pulls ID rather than catgory value

Appreciate your help with this RG ... I've modified/set up everything else, DB is working fine, and have already rolled out the DB, but it's just these two minor issues I am having.

[RuralGuy]

I'm not sure the purpose of a Locked Combo Box on the [Task Details] but the easiest way I found to resolve this issue was to add an invisible TextBox (orange) set to the value you want and reference that control in the Macro instead of the cbo. There was a lot of redirection in your design and I chose to not attempt to understand it but instead find a solution that would not break something else. I'll look at your next issue next.

[RuralGuy]

I might also add that Access defaults the name of a bound control to the name of the field to which it is bound. Then can easily get confused when you reference it because there are two elements with the same name. I usually rename my controls as to the type of control and the bound field name (txtBoundField). Then I know what type of control and to what it is bound. I do not think this is currently causing you problems but it sure could in the future.

[RuralGuy]

As for the report, I added the lookup table to the Completed Tasks query and added the Contacts Name to the query and referenced it in the report instead. I don't think that will break anything else in your system.

[siggybaby_18]

Hey, RG ... Thanks mate! ... The email works exactly as I would like. Appreciate that! But the report is doing the same thing, no changes. On the "Completed Tasks by Date" report it still shows the ID value under the "Category" heading. The heading is labeled "category" but the field is actually the "title" field.

[siggybaby_18]

I only used the "Completed Tasks by Date" for you to see what I am talking about, but essentially all the reports are pulling the ID value instead of the actual "category" value that I would like to see. The category values are listed in the "Categories" table.

[RuralGuy]

Okay, I see what you are talking about. I altered the RecordSource of the Completed Tasks by Date and changed where the report was picking up the "title". The same thing should be able to be done to the other reports.