Multi Value Many-Many Relationship Query

[michelleumich]

Hi all,
I am writing a Query to use on a Print command button in a multi value
continuous form. However, the data that I am using is from multiple
tables and has a many to many relationship.
Perhaps this question will be better answered with a quick explanation
of my data to those outside the database:
My data is based on Student Scholarship information. The Database
contains Student demographic information of all students with
scholarships and all the information you could possibly imagine about
all of the scholarships at this university. , There are three
tables: Two containing separate information and then a linking table
to connect them...(because the data has a many to many relationship.
It has a many to many relationship because each student can have
multiple scholarships and each scholarship can be given to multiple
people at once time)

This is the SQL I have come up with so far:

PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Text
( 255 ), [Forms]![NewLinkedSubform]![txtFilterUMID] Text ( 255 ),
[Forms]![NewLinkedSubform]![txtProjectGrant] Text ( 255 );
SELECT [2007-08ScholarshipMaster2].*
FROM [2007-08ScholarshipMaster2]
WHERE ([2007-08ScholarshipMaster2].Name Like "*" & Forms!
NewLinkedSubform!txtFilterName & "*" Or Forms!NewLinkedSubform!
txtFilterName Is Null) And (Forms!NewLinkedSubform!txtFilterUMID Is
Null Or [2007-08ScholarshipMasterLink].UMID=Forms!NewLinkedSubform!
txtFilterUMID) And (Forms!NewLinkedSubform!txtProjectGrant Is Null Or
[2007-08ScholarshipMasterLink].ProjectGrant=Forms!NewLinkedSubform!
txtProjectGrant) And (Forms!NewLinkedSubform!txtStartDate Is Null Or
[2007-08ScholarshipMaster2].ScholarshipStart=Forms!
QueryMultiValueSearchForm!txtStartDate) And (Forms!NewLinkedSubform!
txtEndDate Is Null Or [2007-08ScholarshipMaster2].ScholarshipEnd=Forms!
NewLinkedSubform!txtEndDate);

However, when I run this it prompts me to enter values for all things
I am searching for through a pop-up box. It ignores the fact that I
am using a form to do this. There are also two rounds of popups, one
for each thing I am searching for. When I enter search criterion in
the popups that does not return any results either. I am relatively
new to access (dove into it not even knowing what the program was for
a month and a half ago) so keep that in mind when helping. Thank you
in advance,
M

 

[Jeff Boyce]

If Access can't see the form (e.g., if it is closed), you'll get this kind
of message.

If there's a typo/spelling difference between the SQL and the actual
location/form/control name, you'll get this kind of message.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[michelleumich]

It displays those messages when the form is not open, but it still
shows the same messages when I open the form. I double checked the
SQL for name consistency and typos.

 

[Jeff Boyce]

When that happens to me, I've spelled something wrong. You might want to
try using the wizard in query design to select the exact form/control name
expression.

Regards

Jeff Boyce
Microsoft Office/Access MVP