Hi Ed,
Are you storing the address associated with the selected UPRN in this table
- hopefully not. Hopefully you just want display the associated address with
this data related to the UPRN. There are several ways of doing this - one is
to create a calculated control which has an expression in the ControlSource
that simply pulls the value from a column of the combo (assuming Address is
in the Rowsource of the combo). Here is what goes in the ControlSource of the
calculated control. Note that the column property is indexed
starting with 0. So for example, if you want the 2nd column to be displayed
in this textbox your reference would be:
=MyCombo.column(1)
Another method is to use an AutoLookup query as the recordsource of the
form. An Autolookup query is fairly straightforward to create. Lets say you
have Customers and Orders and on your Orders form you want to select the
CustId and then display the related CustName and CustAddress fields. The
Customers table is on the 'one' side of the Customers->Orders relationship
and Orders is on the Many side. For an AutoLookup query to work, you must
include the linking field from the Many side of the relationship and then you
would include the related fields (CustName, Address) from the One side. Using
the query designer you would select both tables, join them on the Custid
field and then drag the Custid field from the Orders table onto the Query
grid along with the relevant fields on the Order table, then drag in any
other fields from the Customer table except the Custid field (you could
include it too but it is easier to leave it out).
You can test the Autolookup query using the datasheet view of the query -
you should be able to go to a new record, put in a valid Custid and
immediately see the related CustName and Address fields.
When you have this working you can use the query as the basis for your form.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
Ed said:
Hi Sandra,
Thanks, but I'm not sure that 's it. Essentially what I'm doing is updating
a table through a form. At the moment the table contains a list of properties
- each has a UPRN (Unique Property Reference Number) and the 1st line of the
address (i.e. 1 Acacia Avenue). On the first form I built - I put a combo box
in to look up the UPRN - when I found the one I wanted, the 1st line of the
address would update accordingly. My problem is, on subsequent forms -
although the combo box autocompletes the UPRN, when I find the UPRN i want,
the 1st line of the address doesn't update - I just can't understand why...
Cheers,
Ed.
Sandra Daigle said:
Hi Ed,
When you say that you are looking up table records do you mean that you are
navigating to the record itself? For example, on a Customer form you might
have a combo that is used for record navigation. When you select a custid,
the form navigates to that customer record. If this is what you want you need
code in the AfterUpdate event of the combo to find the correct record.
Note that this is normally done with an unbound combo (or listbox) on the
primary key field of the table. Here is the code for the AfterUpdate event
with me.recordsetclone
.findfirst "PrimaryKey=" & me.cboPrimaryKey
if not .nomatch then
me.bookmark=.bookmark
endif
end with
Replace "PrimaryKey" with the name of the primary key field in the forms
Recordsource and replace "cboPrimaryKey" with the name of the combo control.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
Ed said:
I'm creating a database and want to use Combo boxes on my data input forms to
look up table records for me. On the first form I created, I used a combo box
to look up the unique reference on a record and it returned all of the data
associated with that record in the other available fields. When I tried a
similar approach on a different form it didn't work (the unique ID is
available, but the other data fields do not chnage to mirror the record
returned). Can anyone help with this?
Thanks,
Ed.
