A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query on a subform



 
 
Thread Tools Display Modes
  #1  
Old May 29th, 2010, 05:02 PM posted to microsoft.public.access.queries
Jeffrey Marks
external usenet poster
 
Posts: 4
Default Query on a subform

I have a form that displays an outing for members to sign up. There is
a subform where I display the members of the organization (via a combo
box), using a query to pull the current members.

Now the users would like to only show members by program. So I was
hoping to use the ProgramID field from the form to match against the
ProgramID on the member records in the subform's query. Is this
possible to talk back and forth from the form to the subform's query?
How would I do this?

Thanks

Jeff
  #2  
Old May 29th, 2010, 07:31 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Query on a subform

You can reference the parent form's ProgramID control as a parameter in the
combo box's RowSource property, e.g.

SELECT MemberID, FirstName & " " & LastName
FROM Members
WHERE ProgamID = Forms!ParentFormName!ProgramID
ORDER BY LastName, FirstName;

In the subform's Current event procedure requery the combo box with:

Me.YourComboBoxName.Requery

Ken Sheridan
Stafford, England

Jeffrey Marks wrote:
I have a form that displays an outing for members to sign up. There is
a subform where I display the members of the organization (via a combo
box), using a query to pull the current members.

Now the users would like to only show members by program. So I was
hoping to use the ProgramID field from the form to match against the
ProgramID on the member records in the subform's query. Is this
possible to talk back and forth from the form to the subform's query?
How would I do this?

Thanks

Jeff


--
Message posted via http://www.accessmonster.com

  #3  
Old May 30th, 2010, 12:36 PM posted to microsoft.public.access.queries
Jeffrey Marks
external usenet poster
 
Posts: 4
Default Query on a subform

Ken

Thanks. That works just like I want it to.

One more question: there are cases where an outing will have two or
more ProgramIDs listed (e.g. Program1/Program2). The Members table is
limited to one. I'm a little fuzzy on the Like command. Would it be:

WHERE Forms!ParentFormName!ProgramID Like "'*" & ProgramID & "*'"

Thanks

jeff
  #4  
Old May 30th, 2010, 06:23 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Query on a subform

Jeff:

Does that mean a row in the outings table could have a value such as
'Program1/Program2' in the ProgarmID column? If so then it would be;

WHERE Forms!ParentFormName!ProgramID LIKE "*" & [ProgramID] & "*"

But, and it's a big BUT, storing two values at one column position in a row
in a table is not good design. It means the table is not in First Normal
Form (1NF), the definition of which is:

'A relvar is in 1NF if and only if, in every legal value of that relvar,
every tuple contains exactly one value for each attribute.'

Loosely speaking, in the language of the relational model, a relvar (relation
variable) equates to a table, a tuple to a row (record) and an attribute to a
column (field). What you have is a many-to-many relationship type between
outings and programs. A many-to-many relationship type is modelled by
another table which references the primary keys of the two tables which are
related in this way. So a correct design would have Outings and Programs
tables, and an OutingPrograms table with columns OutingID and ProgramID,
there would then be no foreign key ProgramID column in the Outings table.
Diagramatically the relationship looks like this:

Outings---OutingPrograms---Programs

You'll see that the many-to-many relationship type has been resolved into two
one-to-many relationship types. The combo box on your subform would now have
a RowSource of:

SELECT MemberID, FirstName & " " & LastName
FROM Members INNER JOIN OutingPrograms
ON Members.ProgramID = OutingPrograms.ProgramID
WHERE OutingPrograms.OutingID = Forms!ParentFormName!OutingID
ORDER BY LastName, FirstName;

This query will return each member whose ProgramID value equates to the
ProgamID in any row in the OutingPrograms table whose OutingID value equates
to the primary key of the parent form's current record.

Ken Sheridan
Stafford, England

Jeffrey Marks wrote:
Ken

Thanks. That works just like I want it to.

One more question: there are cases where an outing will have two or
more ProgramIDs listed (e.g. Program1/Program2). The Members table is
limited to one. I'm a little fuzzy on the Like command. Would it be:

WHERE Forms!ParentFormName!ProgramID Like "'*" & ProgramID & "*'"

Thanks

jeff


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:00 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.