Prev: (Easier) Database
Next: events for record navigator
From: kimmieboot on 9 Jun 2008 20:38
Can anyone please help me, I have set up two tables:
1. Contact Details 2. Facilitators

I created a form using the 1. Contact Details table. In this form is a
bound combo box called 'select facilitator' with the following in it:

SELECT [Facilitator].[FACID], [First Name] & " " & [Surname] AS FullName,
[Facilitator].[Mr/Mrs], [Facilitator].[First Name], [Facilitator].[Surname],
[Facilitator].[Address], [Facilitator].[E-mail], [Facilitator].[Phone (H)],
[Facilitator].[Phone (W)], [Facilitator].[Phone (M)] FROM Facilitator;

Then I have matching fields underneath this box to fill in the form:

Namely:
Title (Mr/Mrs)=[Select Facilitator].Column(2)
First Name =[Select Facilitator].Column(3), etc

These populate/enter the data from the Facilitator Table into the Form.

But it does not put the data into the table - it is only storing it on the
form ???

When I go to do the mail merge letters with the Facilitator FirstName,
Address, etc my letters are coming up empty even though the data is in the
form.

Can anyone tell me what I am doing wrong ???
From: John W. Vinson on 9 Jun 2008 21:09
On Mon, 9 Jun 2008 17:38:14 -0700, kimmieboot
<kimmieboot(a)discussions.microsoft.com> wrote:

>Can anyone please help me, I have set up two tables:
>1. Contact Details 2. Facilitators
>
>I created a form using the 1. Contact Details table. In this form is a
>bound combo box called 'select facilitator' with the following in it:
>
>SELECT [Facilitator].[FACID], [First Name] & " " & [Surname] AS FullName,
>[Facilitator].[Mr/Mrs], [Facilitator].[First Name], [Facilitator].[Surname],
>[Facilitator].[Address], [Facilitator].[E-mail], [Facilitator].[Phone (H)],
>[Facilitator].[Phone (W)], [Facilitator].[Phone (M)] FROM Facilitator;
>
>Then I have matching fields underneath this box to fill in the form:
>
>Namely:
>Title (Mr/Mrs)=[Select Facilitator].Column(2)
>First Name =[Select Facilitator].Column(3), etc
>
>These populate/enter the data from the Facilitator Table into the Form.
>
>But it does not put the data into the table - it is only storing it on the
>form ???
>
>When I go to do the mail merge letters with the Facilitator FirstName,
>Address, etc my letters are coming up empty even though the data is in the
>form.
>
>Can anyone tell me what I am doing wrong ???

Attempting to store the data redundantly.

Access, as a relational database, uses the "Grandmother's Pantry Principle":
"A place - ONE place! - for everything, everything in its place". The surname,
phone, etc. should exist ONLY in the table which has one record per person.
It's not clear how your tables are related - which is the "one" side of the
relationship? The "Many" side table should contain *only* the FACID field as a
link to the "one".

If you want to see [Contact details] data in conjunction with [Facilitator]
data, use a Query joining the two tables.
--

John W. Vinson [MVP]
From: kimmieboot on 9 Jun 2008 21:34
Hi, My database is set up by:
1. The contact details table contains all the information relating to one
client.
2. The Facilitator table contains 10 records consisting of facilitator
names, phones numbers, address, etc.

I have created a form to enter the new clients details and then a drop down
box that lists all the facilitators that the matter can be referred to. The
drop down box then populates the fields. The only reason I want it to
populate the fields is that these fields are linked to the mail merge fields
in Word. If you have another way to get the job done - please let me know
!!! I have tried a variety of codes, macros, queries, but nothing I do seems
to make the data link in Word.

thanks, Kim


"John W. Vinson" wrote:

> On Mon, 9 Jun 2008 17:38:14 -0700, kimmieboot
> <kimmieboot(a)discussions.microsoft.com> wrote:
>
> >Can anyone please help me, I have set up two tables:
> >1. Contact Details 2. Facilitators
> >
> >I created a form using the 1. Contact Details table. In this form is a
> >bound combo box called 'select facilitator' with the following in it:
> >
> >SELECT [Facilitator].[FACID], [First Name] & " " & [Surname] AS FullName,
> >[Facilitator].[Mr/Mrs], [Facilitator].[First Name], [Facilitator].[Surname],
> >[Facilitator].[Address], [Facilitator].[E-mail], [Facilitator].[Phone (H)],
> >[Facilitator].[Phone (W)], [Facilitator].[Phone (M)] FROM Facilitator;
> >
> >Then I have matching fields underneath this box to fill in the form:
> >
> >Namely:
> >Title (Mr/Mrs)=[Select Facilitator].Column(2)
> >First Name =[Select Facilitator].Column(3), etc
> >
> >These populate/enter the data from the Facilitator Table into the Form.
> >
> >But it does not put the data into the table - it is only storing it on the
> >form ???
> >
> >When I go to do the mail merge letters with the Facilitator FirstName,
> >Address, etc my letters are coming up empty even though the data is in the
> >form.
> >
> >Can anyone tell me what I am doing wrong ???
>
> Attempting to store the data redundantly.
>
> Access, as a relational database, uses the "Grandmother's Pantry Principle":
> "A place - ONE place! - for everything, everything in its place". The surname,
> phone, etc. should exist ONLY in the table which has one record per person.
> It's not clear how your tables are related - which is the "one" side of the
> relationship? The "Many" side table should contain *only* the FACID field as a
> link to the "one".
>
> If you want to see [Contact details] data in conjunction with [Facilitator]
> data, use a Query joining the two tables.
> --
>
> John W. Vinson [MVP]
>
From: John W. Vinson on 9 Jun 2008 22:21
On Mon, 9 Jun 2008 18:34:05 -0700, kimmieboot
<kimmieboot(a)discussions.microsoft.com> wrote:

>Hi, My database is set up by:
>1. The contact details table contains all the information relating to one
>client.
>2. The Facilitator table contains 10 records consisting of facilitator
>names, phones numbers, address, etc.
>
>I have created a form to enter the new clients details and then a drop down
>box that lists all the facilitators that the matter can be referred to. The
>drop down box then populates the fields. The only reason I want it to
>populate the fields is that these fields are linked to the mail merge fields
>in Word. If you have another way to get the job done - please let me know
>!!! I have tried a variety of codes, macros, queries, but nothing I do seems
>to make the data link in Word.

You are basing the mailmerge on a Table.

That is not required.

Create a Query joining the contact details table to the facilitator table;
include the needed fields from both tables... and base your mail merge on
THAT.

Be sure not to use any Lookup Fields (either in the table or the query);
they'll display lookups in Access but not in the mail merge.

If this doesn't work please post the SQL of the query, a couple of sample
records (fake but realistic data if need be), and describe in what way it
"doesn't work".
--

John W. Vinson [MVP]
From: kimmieboot on 10 Jun 2008 07:09
I have made several attempts to create a query that does what I want but to
no avail. I cannot seem to match the contact table with the facilitator
details, where it allows me to select one facilitator to one record (links
the information to use in a word document).

Is there a code that can be used in the after update of the combo box to
fill in the table fields ?
--
thanks, kimmieboot


"John W. Vinson" wrote:

> On Mon, 9 Jun 2008 18:34:05 -0700, kimmieboot
> <kimmieboot(a)discussions.microsoft.com> wrote:
>
> >Hi, My database is set up by:
> >1. The contact details table contains all the information relating to one
> >client.
> >2. The Facilitator table contains 10 records consisting of facilitator
> >names, phones numbers, address, etc.
> >
> >I have created a form to enter the new clients details and then a drop down
> >box that lists all the facilitators that the matter can be referred to. The
> >drop down box then populates the fields. The only reason I want it to
> >populate the fields is that these fields are linked to the mail merge fields
> >in Word. If you have another way to get the job done - please let me know
> >!!! I have tried a variety of codes, macros, queries, but nothing I do seems
> >to make the data link in Word.
>
> You are basing the mailmerge on a Table.
>
> That is not required.
>
> Create a Query joining the contact details table to the facilitator table;
> include the needed fields from both tables... and base your mail merge on
> THAT.
>
> Be sure not to use any Lookup Fields (either in the table or the query);
> they'll display lookups in Access but not in the mail merge.
>
> If this doesn't work please post the SQL of the query, a couple of sample
> records (fake but realistic data if need be), and describe in what way it
> "doesn't work".
> --
>
> John W. Vinson [MVP]
>
 |  Next  |  Last
Pages: 1 2
Prev: (Easier) Database
Next: events for record navigator