AfterUpdate - Fill in multiple fields

[Robert Neville]

My database serves as a complex contact manager, which no other
commercial product emulates. My database has a Company Form for
inputting company information. The Company form has an address
subform.

My objective involves selecting a previously entered address through a
combo box field (addr1) and having AfterUpdate code automatically fill
in the other address fields. My code draft returns an error upon
activating the event. The combo box should allow new records for the
input and to easily change the company addresses as well. The error
message and code follow below.

Run-time error '3331':

Error Description: To make changes to this field, first save the
record

Private Sub cboAddr1_AfterUpdate()
' DOES NOT WORK
Me![txtAddrID] = Me![cboAddr1].Column(0)
Me![cboAddrName] = Me![cboAddr1].Column(1)
Me![cboAddr1] = Me![cboAddr1].Column(2)
Me![cboCity] = Me![cboAddr1].Column(3)
Me![cboStateID] = Me![cboAddr1].Column(4)
Me![txtPostalCode] = Me![cboAddr1].Column(5)
Me![txtCountry] = Me![cboAddr1].Column(6)
End Sub

The following error occurs when selecting a control outside the
sub-form.

"The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again."

This error message is straight-forward, yet I find myself at odds.
The code changes the data in the relationship table where two fields,
CompID and AddrID represent the index. The event is not creating a
duplicate with this index. This point implies that either the index is
not properly setup; or the subform has an inappropriate record source;
or the code follows an incorrect syntax. Let me know if you could
lead me in the right direction with this objective.

TABLE
tblComp trelCompAddr tblAddr

FIELDS
CompID CompID AddrName
CompName AddrID Addr1
CompTypeID Addr2 City
Sector AddrTypeID State
Phone PostalCode
Phone2 Country
Fax2
Email

[Allen Browne]

You have these two tables:
- tblComp: one record for each company;
- tblAddr: one record for each address.

There is a many-to-many relationship between the, so have a 3rd table
linking the two:
- trelCompAddr: one record for each combination of company+address.

If that's the case, trelCompAddr won't have all the address fields in it. It
will have just these two fields:
o CompID foreign key to tblComp.CompID
o AddrID foreign key to tblAddr.AddrID.

The interface would consist of a main form for Company, with a continuous
subform. The subform has just a combo for selecting the addresses that are
relevant to this company--one per row.

I didn't understand why there was a copying of addresses going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - [url]http://allenbrowne.com/tips.html[/url]
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

"Robert Neville" <robert_neville@y@h0o.com> wrote in message
news:lrf6hvgjqs5487vc2k43ch4pljq53ap6ej@4ax.com...

> My database serves as a complex contact manager, which no other
> commercial product emulates. My database has a Company Form for
> inputting company information. The Company form has an address
> subform.
>
> My objective involves selecting a previously entered address through a
> combo box field (addr1) and having AfterUpdate code automatically fill
> in the other address fields. My code draft returns an error upon
> activating the event. The combo box should allow new records for the
> input and to easily change the company addresses as well. The error
> message and code follow below.
>
> Run-time error '3331':
>
> Error Description: To make changes to this field, first save the
> record
>
> Private Sub cboAddr1_AfterUpdate()
> ' DOES NOT WORK
> Me![txtAddrID] = Me![cboAddr1].Column(0)
> Me![cboAddrName] = Me![cboAddr1].Column(1)
> Me![cboAddr1] = Me![cboAddr1].Column(2)
> Me![cboCity] = Me![cboAddr1].Column(3)
> Me![cboStateID] = Me![cboAddr1].Column(4)
> Me![txtPostalCode] = Me![cboAddr1].Column(5)
> Me![txtCountry] = Me![cboAddr1].Column(6)
> End Sub
>
> The following error occurs when selecting a control outside the
> sub-form.
>
> "The changes you requested to the table were not successful because
> they would create duplicate values in the index, primary key, or
> relationship. Change the data in the field or fields that contain
> duplicate data, remove the index, or redefine the index to permit
> duplicate entries and try again."
>
> This error message is straight-forward, yet I find myself at odds.
> The code changes the data in the relationship table where two fields,
> CompID and AddrID represent the index. The event is not creating a
> duplicate with this index. This point implies that either the index is
> not properly setup; or the subform has an inappropriate record source;
> or the code follows an incorrect syntax. Let me know if you could
> lead me in the right direction with this objective.
>
> TABLE
> tblComp trelCompAddr tblAddr
>
> FIELDS
> CompID CompID AddrName
> CompName AddrID Addr1
> CompTypeID Addr2 City
> Sector AddrTypeID State
> Phone PostalCode
> Phone2 Country
> Fax2
> Email