User select queries

[jcrump230]

first and foremost I am a novice.......
I have a form with several list boxes. I want my users to select a value in the list boxes to create query results. Sometimes I will need only certain list boxes on the form to create a query. I can use listbox1 ,3, 5 or 1,2,3 etc. If no value is selected in the other list boxes it will be bypass . Thanks in advance

list box1 contains All and Id_numbers for each participant
list box2 contains Primary address , secondary address and Both
list box3 contains Male , Female
List box4 contains dates


List box5 contains email address(yes or no), telephone(yes or no) and Leave message(yes or no)
etc.....................




I have no problems getting the values in the list boxes. I use either value list or row source it from a table.
Or provide me any other way to do the job

[Bob Fitz]

IMHO This sounds like an unusual request, so I'm wondering if there is a better way to achieve the desired results. Can you tell us more about the queries and the values in the list boxes.

[jcrump230]

Hi Bob,

I thought that was the way to run queries. The user signs in and select the queries they want to see. Just my idea how it should run. I am assigned a project that have 700 participants. We are going to ship them a book one time and the equipment and a book next time.. There are 4 qtrs. each participant will participate in 2 qtrs. their name, address of availability either primary address or secondary address or both for the project, id number, equipment id number, availability dates, when items are shipped, returned date, phone, email address
So we can send mail merge letter or email or make a phone calls for returned or not returned items or completed assignments. I have a availability grid that tells us when the participants are available and we will take that info and see if the 1st qtr, 2nd qtr, 3rd qtr or 4th qtr are suitable. All the records will be kept on each quarter they participant in. there will be batch shipments for each qtr(on certain dates). Hopefully this will help you out. Thanks for replying back. I am trying to visualize what is needed. right now I am testing things before the 700 or so come out. so I will be ready to go

[Bob Fitz]

I have some understanding now, but can you explain what you want to do/happen once you have finished making selections from the list boxes.

[jcrump230]

I would like to click a button to Run a query after my selection(s) were made. then run a report or mailmerge

[Bob Fitz]

Have you tried creating a query that references the list boxes on your form for its criteria.

[jcrump230]

Bob,
can I have a small example to get me going in the right direction

[jcrump230]

I believe I have the first list box works nicely. for "All" it grabs all records, select a single record it grabs just the single record, if I select two records it grabs just the two records. Having two list boxes, the code wants me to select a item in the second list box no matter what which if I do not select "All" in the first list box I really do not want to select anything for the second list box. If a single record is selected in the first list box and I have to select from the second list box it will show no records.

[Bob Fitz]

Take a look at the attached file.Also see post #2 at this link: http://www.access-programmers.co.uk/...arameter+query

[jcrump230]

thanks Bob. Its working with the combo boxes. I will try with the list boxes later. One problem I am having is "All". I have it in my combo box with Union select but I need the code for it to be recognized as ALL. what do I need to do to give you credit for helping me. thanks again
Hopefully you will be around when I need more help

[Bob Fitz]

thanks Bob. Its working with the combo boxes. I will try with the list boxes later. One problem I am having is "All". I have it in my combo box with Union select but I need the code for it to be recognized as ALL. what do I need to do to give you credit for helping me. thanks again
Hopefully you will be around when I need more help

And if I'm not then I am sure there will someone else who will step up to the plate. There are many here with far greater experience and knowledge than I have. Read the bit of my signature in red to give credit.

[jcrump230]

One more thing, What do I need to do to get ALL selection to show all records. Right now, No records are showing up when selected. thanks

[orange]

see this link for one way to add ALL to your options.
Another way.

[jcrump230]

Orange,

I have ALL in my combo box but when I select the ALL item I get no records. I look at the link no help. what code do I need and where do I put the code...... thanks

[orange]

You have to create code to do select all relevant records if the user chooses the "All" option.
Here's a generic example:

If user chooses "All" then Select all records..
Else
Select all records matching chosen user criteria
......
end if

eg.

.....  If me!combobox ="All" then
         mySQL = "SELECT * from myTable "
       Else
         mySQL = "Select * from myTable where mycriteria = Me.combobox
        end if...