Combo box that automatically update a sub-form record

[Robert Neville]

My database has a Company Form for inputting company information. The
Company form has an address sub-form, which allows a company to have
multiple addresses.

The company form is tied to tblComp while the sub-form record source
includes trelCompAddr and tblAddr.

The Company Address form has two Combo boxes that allow me to select a
previously entered address either by Address Name or the first address
line. The aim with these combo boxes involves selecting a previously
entered address and updating the underlying relationship table.

My objective involves having the AfterUpdate event automatically fill
in the other address fields. My code draft returns an error upon
activating the event. This approach seems dubious now after over a
month of trial and an error. The combo boxes should allow me to input
new data and to easily change the company addresses as well. The
error message follows below.

'---------------------------------------------------
' Run-time error '3331':
'---------------------------------------------------
Error Description: To make changes to this field, first save the
record

The code follows.
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

This error message is straight-forward, yet I find myself at odds.
The code changes the data in the relationship table, trelCompAddr,
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 sub-form has an
inappropriate record source; or the code follows the incorrect syntax.
Let me know if you could lead me in the right direction with this
objective.

TABLES
tblComp trelCompAddr tblAddr

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

I have even tried DLookup, yet the combo boxes do not perform as
expected. Apparently, the code can not change the AddrID link in the
sub-form's record source and returns the same error as above. But I
could change AddrID manually on the sub-form.

Dim varAddrName As Variant
Dim varCity As Variant
Dim varState As Variant
Dim varPostalCode As Variant
Dim varCountry As Variant
Dim intAddrID As Integer

intAddrID = Me.cboAddr1.Column(0)
Me![txtAddrID] = intAddrID

varAddrName = DLookup("AddrName", "tblAddr", "AddrID =" &
intAddrID)
varState = DLookup("StateID", "tblAddr", "AddrID =" & intAddrID)
varCity = DLookup("City", "tblAddr", "AddrID =" & intAddrID)
varPostalCode = DLookup("PostalCode", "tblAddr", "AddrID =" &
intAddrID)
varCountry = DLookup("Country", "tblAddr", "AddrID =" & intAddrID)
If (Not IsNull(varAddrName)) Then Me![cboAddrName] = varAddrName
If (Not IsNull(varCity)) Then Me![cboCity] = varCity
If (Not IsNull(varState)) Then Me![cboStateID] = varState
If (Not IsNull(varPostalCode)) Then Me![txtPostalCode] =
varPostalCode
If (Not IsNull(varCountry)) Then Me![txtCountry] = varCountry

In my data scenario, companies move often to previously entered
addresses; thus the address link may need to be updated for this
particular company record. How? I prefer doing this from the Addr1 and
AddrName combo boxes if possible. Should I create this with a SQL
INSERT statement? Please let me know detail since my SQL experience is
limited.