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

[deeranwith]

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.

 

[Al Campagna]

deeranwith,
Try... (use your own control names)
= Forms!frmMainFormName!frmSubformName.Form!YourID
--
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."

 

[deeranwith]

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?

 

[Al Campagna]

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.
--
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
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)
= Forms!frmMainFormName!frmSubformName.Form!YourID
--
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."

 

[deeranwith]

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.

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.
--
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
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)
= Forms!frmMainFormName!frmSubformName.Form!YourID
--
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
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.