Fields not linked from Form to Table

[kimmieboot]

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 ???

 

[John W. Vinson]

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.

 

[kimmieboot]

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

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]

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".

 

[kimmieboot]

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 ?

 

[John W. Vinson]

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 ?

Well... it could be done with some rather tedious VBA code. But it is *not
necessary*. The query (if done correctly) *will* work.

What are the relevant fieldnames of the two tables? Do you have a Relationship
defined between them? Do you have a FacilitatorID field in the contact table?
What do you see if you create a Query joining the facilitator table to the
contact table, by the facilitator ID, and select all fields from both tables?