How to fill data from another table to a form - URGENT

  • Thread starter Thread starter ljubo lecic via AccessMonster.com
  • Start date Start date
L

ljubo lecic via AccessMonster.com

I have the simple continuous form with several fields.
When entering data I want to fill some of my fields with data from another
table based on the value entered in
my first field "FIELD1"
I want to do something like this:"
select Column_x into My_Field_in_my_form
from Table_x
where table_X.id = Forms!My_Form!FIELD1"
This is probably incorrect in ACCESS but it shows what i need.
I am New in ACCESS ! THANKS in advance!
 
ljubo lecic via AccessMonster.com said:
I have the simple continuous form with several fields.
When entering data I want to fill some of my fields with data from
another table based on the value entered in
my first field "FIELD1"
I want to do something like this:"
select Column_x into My_Field_in_my_form
from Table_x
where table_X.id = Forms!My_Form!FIELD1"
This is probably incorrect in ACCESS but it shows what i need.
I am New in ACCESS ! THANKS in advance!
Click to expand...

There are two ways to do this, depending on your situation. The
simplest way, if it works for you, is to base the form, not just on the
first table, but on a query that joins the two tables on the linking
field. It has to be an outer join, so that all records from the first
table are included, even if they don't have FIELD1 filled in yet, or if
that field doesn't have a matching record in Table_X.

So, for example, you form might be based on a query with SQL along these
lines:

SELECT
Table_A.FIELD1,
Table_A.FIELD2,
Table_A.FIELD3,
Table_X.Column_X
FROM
Table_A LEFT JOIN Table_X
ON Table_A.FIELD1 = Table_X.ID;

This sort of thing is sometimes called an "auto-lookup" query. As soon
as FIELD1 is filled in, the matching information is pulled in from
Table_X.

A different approach is to use a calculated control with a DLookup
function expression as its ControlSource. But that won't be as
efficient, and I'd recommend using it only if the auto-lookup query
doesn't work for some reason.
 
Back
Top