Prev: Filter in the form
Next: Sort by date field
From: CJ on 10 Apr 2010 08:29
Hi,

Could someone please explain in very basic terms of how to auto populate a
field in a table.

I would like to enter a user ID and the address, name ect comes up
automatically.

Many thanks
From: Al Campagna on 10 Apr 2010 09:07
CJ,
Well, very basically, try using a combo (bound to the ID field)
to select the ID.
But, as a genereally accepted rule, don't re-capture all
the other ancillary information, like Address, City, etc. Just "display" it
on the form.
Since you capture the ID, you can always rederive the ancillary
information
"on the fly" in any subsequent form, query,or report.
That combo might display just ID and Name, but also have columns for
Address, City, State, etc...
After a selection is made you can display all the ancillary info with
calculated controls.
Given these combo (ex. cboID) columns...
ID Name Address City ... etc....
A text control iwth a ControlSource of...
= cboID.Column(2)
will always display the Address value from the combo.
(Combo cols are numbered left to right... 0,1,2,3,4,etc)
=cboID.Column(3)
would display the City value, and so on and so on.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


"CJ" <CJ(a)discussions.microsoft.com> wrote in message
news:2D908DFC-2599-4BB3-B44E-FD1C2752F23F(a)microsoft.com...
> Hi,
>
> Could someone please explain in very basic terms of how to auto populate a
> field in a table.
>
> I would like to enter a user ID and the address, name ect comes up
> automatically.
>
> Many thanks


From: John W. Vinson on 10 Apr 2010 13:02
On Sat, 10 Apr 2010 05:29:01 -0700, CJ <CJ(a)discussions.microsoft.com> wrote:

>Hi,
>
>Could someone please explain in very basic terms of how to auto populate a
>field in a table.
>
>I would like to enter a user ID and the address, name ect comes up
>automatically.
>
>Many thanks


You don't!

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place".

The user's name, address, and so on should be stored in the User table and
*noplace else*. Storing it redundantly in a second table wastes space, and
more importantly makes it possible to have inconsistant data: you could have a
user with one address in the user table, and a different address in this
second table.

The second table should have ONLY the UserID; if you need to see that table's
data in conjunction with user biographical data, you can use a Query joining
the tables, or a Form to *display* (but not store) the user data. To do the
latter include the address, name, etc. in the combo's rowsource query, and put
textboxes on the form with control sources like

=comboboxname.Column(n)

where n is the zero-based position of the column you want to see.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Filter in the form
Next: Sort by date field