help! form is trying to update the wrong table

[dan]

I have a couple of tables, Contacts and AssocContacts, as
follows:

"Contacts"
ContactId
Name
Address
telno
etc. ...

"AssocContacts"
ContactId1
ContactId2

The AssocContacts table stores links between contacts, for
example, Dan has a link with Dave. This is obviously
stored using the ContactId's from the Contacts table.

I have set up the relationships - note that I needed to
add the Contacts table in twice in order to create two
links to it from the AssocContacts table. I don't think
that's an issue, though.

Now, I have a master form, frmContacts, which allows you
to edit the main contact details in the Contacts table,
that's fine. I also have an embedded form,
frmAssocContacts, which is meant to allow you to add an
association with the current contact in the frmContacts
form.

On the frmAssocContacts form I have a combo box which
allows the user to select a surname from a list of
contacts. Note that, when pulled down, the combo list
also shows the firstname, address and contact id values.

I also have a couple of visible locked fields which show
the first name and address of the selected contact.

In addition, I have got a couple of other "invisible" text
boxes in order to store the primary key for adding new
records in AssocContacts. the invisible text boxes are
for ContactId1 and ContactId2.

Two problems:

Problem 1:
In the combo-box, I have some code that is executed on the
Surname_Changed event. This code updates the visible
fields to show the address and first name of the contact.
It also sets the value of the invisible text boxes that
store the primary key values for the AssocContacts table.
However, an error occurs when the code tries to set the
primary key value. The code is:

Me.txtAssocConId.Value = Me.txtSurname.Column(7)

And the error is 3341, "The current field must match the
join key 'ContactId' in the table that serves as the 'one'
side of the 'one-to-many' relationship.

When I removed the line in order to debug it, I realised
that Access was trying to update the Contacts and the
AssocContacts tables, when it should only be updating the
AssocContacts table.

The query that the form uses is as follows:

SELECT DISTINCTROW Contacts.Surname, Contacts.FirstName,
Contacts.JobRole, Contacts.AddressLine1,
Contacts.AddressLine2, AssociatedContacts.ContactType,
Contacts.ContactId, AssociatedContacts.ContactId2
FROM Contacts INNER JOIN AssociatedContacts ON
Contacts.ContactId = AssociatedContacts.ContactId2;

I thought I'd be cunning and change the inner join clause
so that it joined AssociatedContacts to Contacts rather
than the other way around, but Access said that it
couldn't support that!

I assume that the problem is something to do with the way
the query joins the tables, but I'm not entirely sure how
to get around it. I just want the form to update the
correct table.

If anyone can help me then I will buy them a large beer,
or drink of their choosing!

Many thanks,

Dan.

 

[Emilia Maxim]

I have a couple of tables, Contacts and AssocContacts, as
follows:

"Contacts"
ContactId
Name
Address
telno
etc. ...

"AssocContacts"
ContactId1
ContactId2

The AssocContacts table stores links between contacts, for
example, Dan has a link with Dave. This is obviously
stored using the ContactId's from the Contacts table.

I have set up the relationships - note that I needed to
add the Contacts table in twice in order to create two
links to it from the AssocContacts table. I don't think
that's an issue, though.

<--- snip --------

dan,

I'll describe how this is set up usually.

First, the main form for the contacts. The underlying query should
contain only fields from the Contacts table. There is no need for a
JOIN. The LinkMasterField and LinkChildField properties of the subform
_control_ take care of this when properly set. In your case:
LinkMasterField: ContactId
LinkChildField: ContactId1

(Please look up these properties in the Help for detailed
explanations.)

Then the subform.
The text box bound to ContactId1 would be invisible.
The combo bound to ContactId2 would be visible. The query for the
combo would be something like:
SELECT ContactId2, Contacts.Firstname & " " & Contacts.Lastname As
FullName FROM AssocContacts INNER JOIN Contacts ON
AssocContacts.ContactID1 = COntacts.ContactID ORDER BY FullName
Bound column: 1
Column widths: 0;1.5
LimitToList: Yes

This way, the combo would display the name of the associated contact
and in the background store the ID. No need to set the ContactID2 via
code.

To display further data for the associated contact you would need
unbound, visible, and inactive text boxes having functions as
ControlSource. You can use the function DLookup to return address and
whatever else needed (see Help for this).

When entering new records in the subform, Access will take care to
fill ContactID1. Choosing a name from the combo list will take care of
filling in the correct ContactID2.

OK, so much for the moment. Try to implement all this and come back if
you need further help.

BTW, the OnChange event is not recommended for updating other fields
depending on entered data. The event fires for each key stroke in that
field (backspace for ex), plus the entered value is not yet saved. The
AfterUpdate event would be the appropriate place.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Guest]

Thank you very much for taking the time, that was really,
really helpful. I will buy you a drink when I am next in
Stuttgart!

Dan.

 

[Emilia Maxim]

Thank you very much for taking the time, that was really,
really helpful. I will buy you a drink when I am next in
Stuttgart!

.... and where are you now? )

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[dan]

for my sins, Sheffield, England.

-----Original Message-----


.... and where are you now? )

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
.