How to get field from lookup?

  • Thread starter Thread starter Kimmy
  • Start date Start date
K

Kimmy

Hi,

I am new to Access, here is my problem:
table1: one of the fields is Buildnumber which is a lookup
into table2 which contains buildNumber(key) DateFrom and
DateTo.

In my form that use table1, I am able to get only
BuildNumber. How do I get DateFrom and DateTo from table2
without using subform. I need to know what is the Date(s)
once user select a build number.

If I have to use subform, then is there away to hide
subform?

Thanks in advance.

Kimmy
 
Hi Kimmy,
Sounds like you want to add a few more controls with
Dlookup expressions in the control source property.
The tricky part is pulling in the fields you want.

Which table contains the primary key and which one has the
foreign key? If the primary key is in table1, which I
suspect, then you would want to set it up as follows.

Add the primary key field to your form from Table1, you
can make it invisible if necessary. Create a DLookup
using an unbound text box, and in the control source
property ,try typing something like this:

=DLookup("[DateFrom]","Table2","[foreignkeyfield]=Forms!
formname![key field]")

Do the same for the DateTo field as well.

This will look up the DateFrom field in Table2 where
Table2's foreignkey is the same as the key field on form1.

If these are numbered fields, then you may not need the ""
around the last part, ie, [foreignkeyfield]=Forms!formname!
[keyfield]).

If your relationship is set as 1 to Many, the lookup will
only take the first occurrence where the condition is true.

Hope this helps.

Sincerely,

Don Barton
 
Thanks for reply. It does work. Kimmy
-----Original Message-----
Hi Kimmy,
Sounds like you want to add a few more controls with
Dlookup expressions in the control source property.
The tricky part is pulling in the fields you want.

Which table contains the primary key and which one has the
foreign key? If the primary key is in table1, which I
suspect, then you would want to set it up as follows.

Add the primary key field to your form from Table1, you
can make it invisible if necessary. Create a DLookup
using an unbound text box, and in the control source
property ,try typing something like this:

=DLookup("[DateFrom]","Table2","[foreignkeyfield]=Forms!
formname![key field]")

Do the same for the DateTo field as well.

This will look up the DateFrom field in Table2 where
Table2's foreignkey is the same as the key field on form1.

If these are numbered fields, then you may not need the ""
around the last part, ie, [foreignkeyfield]=Forms! formname!
[keyfield]).

If your relationship is set as 1 to Many, the lookup will
only take the first occurrence where the condition is true.

Hope this helps.

Sincerely,

Don Barton
-----Original Message-----
Hi,

I am new to Access, here is my problem:
table1: one of the fields is Buildnumber which is a lookup
into table2 which contains buildNumber(key) DateFrom and
DateTo.

In my form that use table1, I am able to get only
BuildNumber. How do I get DateFrom and DateTo from table2
without using subform. I need to know what is the Date(s)
once user select a build number.

If I have to use subform, then is there away to hide
subform?

Thanks in advance.

Kimmy
.
Click to expand...
.
Click to expand...
 
Back
Top