As Dave Hargis says, you could bind the control to columns in the underlying
table. You then push the values into the controls in the AfterUpdate event
of the suburbs combo box, rather than pulling them in by referencing the
combo box in the ControlSource properties of the (unbound) controls. The
code might be something like this:
Me.State = Me.cboSuburb.Column(1)
Me.PostCode = Me.cboSuburb.Column(2)
However, as the State and PostCode are not functionally dependent solely on
the whole of the key of the table but on the non-key (or possibly partial
key) Suburb column, storing them in the table introduces redundancy and means
the table is not properly normalized. This leaves it open to the risk of
update anomalies. Your present set-up, where the values are shown on the
form but not stored in the table is the correct approach. If you have State
and PostCode columns in the table these should be deleted. The values
appropriate to the Suburb value in any row of the table can always be
obtained, either by computed controls on a form or report as you are doing,
or by joining the relevant tables in a query and returning the State and
PostCode in columns in its result set.
Ken Sheridan
Stafford, England
