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

Using combo box in form to select sub-group of records or to select all records

P: 47
Hi all,

I have a form with a subform. On the form is a combo box with three choices "current", "former" and "all". I'd like to use that combo box to set the data source for the subform based on the status field referred to in the subform.

I've tried to set the data source to a query based on that value -- it works for "current" and "former", but I can't figure out how to write the query so that it selects all records.

My thinking is as follows (omitting the Former status): the query would look something like this (leaving out the form specifications, which I do know how to do):
Expand|Select|Wrap|Line Numbers
  1. Select Table.*
  2. from Table
  3. where [status] = iif([field]="current","current",is not null)
Any ideas? Is there a different approach then using a query for the data source?


Jun 11 '09 #1
Share this Question
Share on Google+
5 Replies

P: 19
I'm not sure I am clear on what you are attempting, but if you are trying to use "current," "former," and "all" as combo box values to select by the end-user, then you do not need a query to feed the control (combo box). You can go to properties and change the row source to "value list." In the example shown below, "change" and "new" are the two values for this box.

Hope that helps. If you meant something else, just disregard.
Jun 11 '09 #2

P: 19
Sorry, forgot the pic.
Attached Images
File Type: png Properties.png (11.6 KB, 212 views)
Jun 11 '09 #3

P: 19
Okay, after reading some more, I think I understand what you are asking. However, there is not enough info to answer fully.

To summarize what you are asking, is that you want to have a listing of "current", "former", or "all" and make the form function so that when you click one of these values, it changes the data which appear on the subform. Is that correct? Based on your answer, my reply would be totally different.
Jun 11 '09 #4

P: 675
Just omit the WHERE clause and you will get all the records.
Jun 11 '09 #5

Expert Mod 15k+
P: 31,712
I'm guessing you have two statuses - "Current" & "Former" - but that you also want the option to show all. If so try :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   Table
  3. WHERE  [Status] Like IIf([Control]='All','*',[Control])
Jun 11 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.