Query showing all records based on combo-box (1 Viewer)

[danikuper]

Hi,

I have a combo-box in my main form that allows the user to select from a list of options. There's a subform based on a query that will show the results based on the selection made in the combo-box.

Everything works fine, except that I also want the user to be able to select "all" from the combo-box and the query will then show all records.

Based on a suggestion from another thread, I added the following column in my query design:

Field: IIf([Forms]![frmProjectsSummary]![ComboStatus]<>"All",[ProjStatus]=[Forms]![frmProjectsSummary]![ComboStatus],True)

Criteria: <> False

This however is not working... does anyone know what could be happening or another way of handling this situation?

Thanks!

 

[dcx693]

I see what you're trying to accomplish with that formula. I need to find out exactly what you've done so for and what is and isn't working. First, have you successfully added the "All" choice to your combo box? The most typical way is through a union query.

Your formula creates the "end" of a SQL string that begins like this: SELECT (field list) FROM yourtable WHERE ". Your formula will complete that statement and make it a valid SQL statement. The SQL statement is then set to be equal to the recordsource of the subform. Usually, the assignment is done from the main form, usually from the AfterUpdate event of the combo box like this:
Me.MainformName.subformName.Form.Recordsource=theSQLStatement

 

[danikuper]

This form shows me a list of projects and the combo-box allows me to view projects only for the selected "status".

My combo-box is based on a value list where I have the following:

"All";"Not Started";"Open";"On-Hold";"Closed";"Canceled"

When I select any value but "All", the subform gives me the records with the status chosen. When I select "All", only one record shows up.

The strange thing is that if I open the query from the database window and not through the form, it works. The query in datasheet view shows all records if I type "All" in the dialog box that shows up after double-clicking the query. This makes me wonder if the problem is something with the way I have my form setup....

 

[dcx693]

Ah, I think I understand better what you're trying to do now. I tried creating a form with a query similar to yours, and I also could not get it to run correctly.

I typically do not base subform recordsources off queries the way that you are doing, so hopefully someone more knowledgeable can take a look at the problem and suggest something.

What I usually do is build a SQL statement and use VBA to assign the recordsource of the subform. Let me know if you're interested in how to do that and maybe I can help.

Good luck.

 

[danikuper]

Although I'm not experienced with VBA, if you could detail me the steps I should take to make it run it would be great.

thanks!

 

[dcx693]

OK, these are the steps to get it done. I'll assume you know how to enter VBA code into an event for a control on a form.

1 - open the query that is the source for the subform and look at it in SQL view. It will have this general syntax:
SELECT (fields) FROM (yourtable) WHERE .....;
The part you're interested in, is the part before the WHERE keyword. Copy it to the clipboard, or write it down.

2 - get the name your subform, I'll just assume it's called "subFormName" for now. Do this by clicking on it is form design view and looking at the properties.

3 - In the AfterUpdate event of the ComboStatus combo box enter this code (in between the Sub and End Sub lines, of course):

Dim strWhereSQL as String

If Me.ComboStatus<>"All" Then
  strWhereSQL="WHERE [ProjStatus]=" & Me.ComboStatus
End If

Me.subFormName.Form.Recordsource="SELECT (fields) FROM (yourtable) " & strWhereSQL

4 - Now, whenever you update the combo box, the subform recordsource should also update.

 

[danikuper]

I realized what the problem was... my main form and my subform were still linked as parent-child and the query for All records would only work if I use an unbound subform.

Thanks for the help!

 

[jackhot0615]

I have the same combobox question. In the attached db file, there is a parent –child link combobox in the mainform and UNION query within “(all)” from custom table.
When I select item from the combo subform will criteria update but “all” just show blank.
Should I write VBA in combo afterupdate event to show all records?
Can anyone help me?
Thanks a lot.

 

[jackhot0615]

I tried again but fail.
This is my S_C_ID(combobox) cascade in the mainform.

SELECT customer.ID, customer.C_NAME FROM [customer] UNION Select Null as AllChoice,"(All)" as allbogus FROM [customer];



And here is the afterupdate event of combobox.

Private Sub S_C_ID_AfterUpdate()
Dim strWhereSQL As String
If Me.S_C_ID <> "All" Then
strWhereSQL = "WHERE customer.ID=" & Me.S_C_ID
End If
Me.Child0.Form.RecordSource = "select customer.ID, customer.C_NAME FROM [customer]" & strWhereSQL
End Sub

Why everyitem will show the correct record but "all" show none?
What should I do to show all records in the subform?