Question about DLookUp and how to bring in info from a foreign tab

[Cameron P]

OK, my database form is based off of a table titled: Show Events, and I want
to pull separate data from a foreign table titled: Show Stores.

One of the data fields I am trying to pull over from the foreign table is
the year that the store joined the organization, which is the field titled:
MemberSince. I am using the expression equation in an unbound control:

=DLookUp("[MemberSince]","Show Stores","[Count]=1")

Count is the field on the foreign table that gives a unique ID number to
each store.

My problem is that when I scroll through the different pages on the form, it
shows the same year for the store that is given a Count ID number of 1. Is
there a way to change the expression so that when I scroll through from store
count 1 through 100 that it gives me 100 different years according to the
unique company, as opposed to giving me the Year that Store 1 (Count = 1)
joined?

Someone in another forum responded with this:

“put the equation in the RecordSource of the form instead of making it a
calculated field on the form

MemSince: nz(DLookUp("[MemberSince]","Show Stores","[Count]=1"))

then, use MemSince as the ControlSource for the control on the formâ€

But I am confused as to where the Record Source is located.

And keep in mind that I want to keep the current table, Show Events, as the
main source for data on the form. There is just one section that I am
wanting to pull a few fields from the foreign table, Show Stores. Hopefully
that makes sense. Thanks for any help you can offer!

 

[Damian S]

Hi Cameron,

You are getting the same answer for all 100 stores as your dlookup statement
is hard coded to return information for StoreID 1. Note: Using reserved
words (like Count) as a field name is generally a bad idea.

Rewrite your dlookup similar to this:

=DLookUp("[MemberSince]","Show Stores","[Count]=" &
forms!FORMNAME.STOREIDFIELDNAME)

where FORMNAME and STOREIDFIELDNAME are the names of your form and your
control housing the Store ID (Count as you have called it).

Of course, you could simply link the second table into your recordsource for
your form and display the Membersince field directly.

Hope this helps.

Damian.