How do I use the value from a subfrom field as criteria in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deeranwith - 16 May 2008 10:38 GMT

I have a membership database and within the main input form is a sub form for
address input. - A person may have more than one address. I have added a
control to the form that calls a query which checks to see if the address has
been used by another person, say a works address. I cannot see how to take
the value of the address primary key (numerical) and put it into the query
search criteria. I have tried DLookup but this does not seem to work.
I am using Access 2003 for this exercise.

Help please and thanks in advance.

Reply to this Message

Al Campagna - 16 May 2008 13:40 GMT

deeranwith,
Try... (use your own control names)
= Forms!frmMainFormName!frmSubformName.Form!YourID

Signature

hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

>I have a membership database and within the main input form is a sub form
>for
[quoted text clipped - 8 lines]
>
> Help please and thanks in advance.

Reply to this Message

deeranwith - 16 May 2008 16:15 GMT

Al
Tried your solution and failed. The query works if you manually enter the
number. The control string (after access has corrected) it reads
[Forms]![ISTR members]![Child77].[Form]![Addresses_add ref no]
I typed in
=Forms![ISTR members]![Child77].Form![Addresses_add ref no]

Thoughts?

> deeranwith,
> Try... (use your own control names)
[quoted text clipped - 11 lines]
> >
> > Help please and thanks in advance.

Reply to this Message

Al Campagna - 16 May 2008 23:15 GMT

deeranwith,

> Tried your solution and failed.

What was the failure... no error message?

If you're running a query, using a value from an open form's subform as
a criteria, your addressing to the value should be correct.
You must be sure that all elements/controls in the address are named
correctly.
The form must be open when the query runs.

Try this...
Open the form, and set the focus on the subform record that has the
value you want.
Using Ctrl-F6 until you come to the database screen....
Open your query in design mode. (design grid)
In the criteria for your key field, right click and select Build.
You should be able to build the correct address to the specific sub
field by drilling down through the forms build.
Selecting Forms-OpenForms-yourmain - yoursub - yoursubfield.

Signature

hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

> Al
> Tried your solution and failed. The query works if you manually enter the
[quoted text clipped - 24 lines]
>> >
>> > Help please and thanks in advance.

Reply to this Message

deeranwith - 17 May 2008 16:13 GMT

Hi Al,

I have now sorted it with your help, thanks. The string that works is the
one below which has DATA FORM included.

[Forms]![ISTR members DATA FORM]![Child77].[Form]![Addresses_add ref no]

Again many thanks for the help.
Deeranwith

> deeranwith,
> > Tried your solution and failed.
[quoted text clipped - 43 lines]
> >> >
> >> > Help please and thanks in advance.

Reply to this Message