By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 938 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Select records based on three different combo boxes

P: n/a
How can you select records based on more than one combo box -
I have a combobox that selects records based on name
(I'm sure this has been asked a thousand times - web site answer/link
could be helpful too; but I'm so bad with syntax that specifics will
be MOST helpful)

SELECT DISTINCT [qryAE_ProtocolsSponsors].[PI Last Name],
[qryAE_ProtocolsSponsors].[PI First Name] FROM
[qryAE_ProtocolsSponsors] Union Select "<ALL>" , NULL From
[qryAE_ProtocolsSponsors];

and sorts the results:
If Me![Combo24] = "<All>" Then
DoCmd.ShowAllRecords
Me.OrderBy = "[PI Last Name] ASC"
Me.OrderByOn = True
Else
DoCmd.ApplyFilter , "[PI Last Name] =
Forms![Form-ProtocolsSponsors]![combo24]"
Me.OrderBy = "qryAE_ProtocolsSponsors.[Current Status] ASC"
'Me.OrderBy = "[Sponsor] ASC"
Me.OrderByOn = True
End If

1 How can I sort by Current Status THEN by Sponsor? so all those of
one status are sorted by sponsor and the next status are sorted by
sponsor, also?)
2. How can I set up two combo boxes so that combo 1 limits the record
set then combo 2 further limits the record set.
Then use a button to Show all records.

Thanks so much in advance!!

-warning e-mail address altered- arthureNOSPACE@
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I'm sorry I dont have time for a long response.

I often use forms with a listbox for navigation and I use several
combos to filter the recordset of this listbox. For the recordsource
of the combos I use two queries - the first to sort the list, then the
second to add an 'ALL' to the top of the list.

In the recordsource of the listbox I have the criteria set as the
value of those combos boxes with IIF statements incase the user
selects all:

IIf([Forms]![frmNewDataRequest]![cmboSelectByPriority]=0,([tblRequests].[Priority]),[Forms]![frmNewDataRequest]![cmboSelectByPriority])

This works great for me. Hope it helps a bit.

Lincoln King
Sydney Australia

arthur-e <ar*****@ix.netcom.com> wrote in message news:<ih********************************@4ax.com>. ..
How can you select records based on more than one combo box -
I have a combobox that selects records based on name
(I'm sure this has been asked a thousand times - web site answer/link
could be helpful too; but I'm so bad with syntax that specifics will
be MOST helpful)

SELECT DISTINCT [qryAE_ProtocolsSponsors].[PI Last Name],
[qryAE_ProtocolsSponsors].[PI First Name] FROM
[qryAE_ProtocolsSponsors] Union Select "<ALL>" , NULL From
[qryAE_ProtocolsSponsors];

and sorts the results:
If Me![Combo24] = "<All>" Then
DoCmd.ShowAllRecords
Me.OrderBy = "[PI Last Name] ASC"
Me.OrderByOn = True
Else
DoCmd.ApplyFilter , "[PI Last Name] =
Forms![Form-ProtocolsSponsors]![combo24]"
Me.OrderBy = "qryAE_ProtocolsSponsors.[Current Status] ASC"
'Me.OrderBy = "[Sponsor] ASC"
Me.OrderByOn = True
End If

1 How can I sort by Current Status THEN by Sponsor? so all those of
one status are sorted by sponsor and the next status are sorted by
sponsor, also?)
2. How can I set up two combo boxes so that combo 1 limits the record
set then combo 2 further limits the record set.
Then use a button to Show all records.

Thanks so much in advance!!

-warning e-mail address altered- arthureNOSPACE@

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.