Display a record in a form

[Guest]

I have a form for entering data from table1 into table2. Not all the
information from table1 needs to be saved in table2, but I would like it to
be displayed in the form to make sure the user picks the correct record.
Ideally I would like to be able to pick a company from a drop down box and
for the form to display their address etc from the same record (all stored in
the table1) purely as text without the option of the user being able to alter
the information in the form. Can anyone tell me how to do this?
Thanks

 

[Guest]

Do you really need to store the data from the Companies table in the
referencing table? This is only necessary if the data in the Companies table
might change with time (e.g. a new address) but you want each row in the
referencing table to retain the data as it was at the time when the row was
inserted into the table. If you want the rows in the referencing table to
reflect any changes in the Companies table then all you need in the
referencing table is the foreign key column which references the primary key
of Companies, e.g. CompanyID.

With the second scenario, which is more usual, simply base the form on a
query which joins the referencing table to the Companies table. Return the
company address etc columns in the query. You can then bind controls in your
form to these columns.

To prevent users editing the data in the controls set their Locked property
to True and their Enabled property to false.

Should the first scenario apply include a combo box bound to the foreign key
columns and set its RowSource property to something like this:

SELECT CompanyID, CompanyName, Address1, Address2, City, State, Zip
FROM Companies
ORDER BY CompanyName;

Set the other properties of the control like so:

BoundColumn 1
ColumnCount 7
ColumnWidths 0cm;8cm;0cm;0cm;0cm;0cm;0cm (or rough equivalent in inches
but all but the second dimension should be zero to so only the name shows –
in a combo box the last 5 zeros are not really necessary provided the second
dimension is at least the control's width, but they would be in the case of a
list box which can be scrolled horizontally)

In the AfterUpdate event procedure of the combo box put code to assign
values to controls bound to columns in the referencing table by referencing
the combo box's Column property. This property is zero based so the code
would be:

Me.Address1=Me.cboCompany.Column(2)
Me.Address2=Me.cboCompany.Column(3)
Me.City=Me.cboCompany.Column(4)
Me.State=Me.cboCompany.Column(5)
Me.Zip=Me.cboCompany.Column(6)

where cboCompany is the name of the bound combo box, and Address1 etc are
the names of the other bound controls.

The above assumes that the primary key of the Companies table is an
arbitrarily unique identifier, most likely an autonumber, not the company
name, which would not be advisable as names can be duplicated. If the
company name has been used as the key, however, the above can be amended very
simply.

Ken Sheridan
Stafford, England