Sending email from Access database

[vic1]

I am trying to send an email direct from an Access query, with a form to be
updated by the recipient. (Something which I have done several times before,
using 'Create Email' in Access 2007). However, this time, several of the
fields have 'look-up' values and the resulting email arrives with the fields
blank and a 'click here' button which offers all choices for all fields, all
at once, rather than containing the existing information (at least, I think
that's what the problem is!).

I have tried copying the data into a new table, but this only returns the
autonumber in the fields, rather than the text it refers to - i.e. '1' rather
than 'Geography'.

Other than retyping all fields into this new table, is there a macro or
other method by which I can get it to display all text, rather than numbers?
What I want to avoid is the recipients having to enter every field from
scratch, rather than just updating when there has been a change, so they need
to be able to see what I have already.

Many thanks

 

[Arvin Meyer [MVP]]

Lookup fields are the problem. Just because Microsoft allows you to create
them, doesn't mean that you should. They are bad database design.:

http://www.mvps.org/access/lookupfields.htm

You should be storing the key of the field rather than it's description.
That's what relational design is about. You can always display the
description with a query.

 

[John W. Vinson]

I am trying to send an email direct from an Access query, with a form to be
updated by the recipient. (Something which I have done several times before,
using 'Create Email' in Access 2007). However, this time, several of the
fields have 'look-up' values and the resulting email arrives with the fields
blank and a 'click here' button which offers all choices for all fields, all
at once, rather than containing the existing information (at least, I think
that's what the problem is!).

I have tried copying the data into a new table, but this only returns the
autonumber in the fields, rather than the text it refers to - i.e. '1' rather
than 'Geography'.

Other than retyping all fields into this new table, is there a macro or
other method by which I can get it to display all text, rather than numbers?
What I want to avoid is the recipients having to enter every field from
scratch, rather than just updating when there has been a change, so they need
to be able to see what I have already.

Many thanks

Send the Email based on a Query joining your table to the various lookup
tables, rather than the table itself.

 

[vic1]

I thought I might have solved it - but I still have the same problem. I
exported the table to Excel, and all the text fields showed correctly, so I
re-imported into Access and created a new table, from which I created a query
which included the email addresses, which weren't in the original table. It
all looks OK.

The only problem is that the email still arrives with all fields blank!

I am very grateful for your suggestions.

 

[vic1]

I think I've overcome the problem - which was that the email field in the
query was only linked in from another table - it wasn't 'in' the table/query
from which I wanted to send the email. I resolved it by copying the email
column from the query to the table and sent the email from there - and now
the email has it's fields filled in!

Many thanks

 

[John W. Vinson]

Thanks for this, I think I understand the problem now, but could you possibly
explain how I should make a query joinin the table to the various lookup
tables? When I try, as soon as I add any links, I get a completely blank
query returned.

It might help if I tell you that my table contains a list of schools with
names of teachers in each school and the subjects they teach (both as Head of
Department and as Additional Teacher), together with the Exam Boards and
Syllabuses they study for each subject - so there are quite a few lookups!

I've tried changing the data type to text - but the lookups still seem to be
there and I can't get rid of them. Is there a way to get rid of the lookups
while still leaving the data intact?

The problem is not with lookups on Forms - they're very useful. What's causing
your confusion is lookups IN THE TABLE. This misfeature conceals the actual
contents of your table (the numeric ID's) by concealing the actual contents
behind a (slow and inefficient!!!) combo box.

Since I do not know the names of any of your fields or any of your tables I
can't be specific; but if you have (say) a lookup field named [Head of
Department] which is a lookup to a table named [Teachers], you would create a
Query joining the (numeric!) [Head of Department] field in your table to the
corresponding autonumber field in the [Teachers] table. Include the non-lookup
fields from your main table and the teacher name from [Teachers].

If you would like more specific help, please post a description of your
tables, with fieldnames and dataypes. If you have multiple fields for multiple
subjects or for multiple teachers... your entire data structure may need
revision. "Fields are expensive, records are cheap"!

 

[John W. Vinson]

I thought I might have solved it - but I still have the same problem. I
exported the table to Excel, and all the text fields showed correctly, so I
re-imported into Access and created a new table, from which I created a query
which included the email addresses, which weren't in the original table. It
all looks OK.

Exporting to Excel and reimporting is not necessary and (as you see) not
helpful. The information that you need all exists in your tables; you just
need a correct query to display or export it.

Since we cannot see your database and do not know how it's structured, it's
more than a bit difficult to give you specific advice. See my other response
in this thread.

 

[vic1]

Thank you for that differentiation between tables and forms!

I really would appreciate some help on the best way to organise data. You
are probably right that my data structure may need revision! Necessity is the
mother of invention and I am entirely self-taught. Can you recommend a
worthwhile book on organising Access Databases? Most give examples which are
so simplistic that they have little relvance to real-life, complicated
relationships!

However, at the moment I have the following tables - all fields are text
fields, apart from the School ID, which is a number field:

SUBJECTS - containing a list of all subjects taken
EXAM BOARDS - containg a list of all possible exam boards
TEACHERS - containg the following fields:
School ID - this links to another table of basic data for each school and
ties the teacher to that school
Teacher - (a list of all teachers in all schools)
Head of Dept. Subject1
Exam Board1
Syllabus1 - (I do not yet have a table for Syllabuses, I am hoping that that
is what the schools will fill in when I send them a form!)
Head of Dept Subject2
Exam Board2
Syllabus2
(These are repeated for 4 possible Head of Dept. subjects)
Additional Teacher
(This is repeated for 4 possible Additional Teacher subjects)
School Email Address - (only required so that I can email a form to the
school)

I know it's complicated - I tried endless permutations of tables and queries
before settling on this one!

You probably need to know that, any given teacher MAY be Head of Dept. in up
to 4 subjects and MAY also teach other subjects ('Additional Subjects') for
which they are not HofD (or they may ONLY teach Additional Subjects).
Obviously, the exam board data is only applied once to each subject i.e.
against the teacher who is HofD for that subject, no matter how many other
teachers also teach it.

I am quite prepared to reorganise my data, if you can help me to organise
the resulting queries which I presume will be necessary.

Very many thanks!!

Thanks for this, I think I understand the problem now, but could you possibly
explain how I should make a query joinin the table to the various lookup
tables? When I try, as soon as I add any links, I get a completely blank
query returned.

It might help if I tell you that my table contains a list of schools with
names of teachers in each school and the subjects they teach (both as Head of
Department and as Additional Teacher), together with the Exam Boards and
Syllabuses they study for each subject - so there are quite a few lookups!

I've tried changing the data type to text - but the lookups still seem to be
there and I can't get rid of them. Is there a way to get rid of the lookups
while still leaving the data intact?

The problem is not with lookups on Forms - they're very useful. What's causing
your confusion is lookups IN THE TABLE. This misfeature conceals the actual
contents of your table (the numeric ID's) by concealing the actual contents
behind a (slow and inefficient!!!) combo box.

Since I do not know the names of any of your fields or any of your tables I
can't be specific; but if you have (say) a lookup field named [Head of
Department] which is a lookup to a table named [Teachers], you would create a
Query joining the (numeric!) [Head of Department] field in your table to the
corresponding autonumber field in the [Teachers] table. Include the non-lookup
fields from your main table and the teacher name from [Teachers].

If you would like more specific help, please post a description of your
tables, with fieldnames and dataypes. If you have multiple fields for multiple
subjects or for multiple teachers... your entire data structure may need
revision. "Fields are expensive, records are cheap"!