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!