Query on a variable number of comboboxes

Hi

I have a form where I have 6 comboboxes and a subform that will show the result for the query.

The query is pretty simple : get all the records that have one or more of the comboboxes value.
the values are in 2 tables so I used a INNER JOIN.
I have the 6th comboboxes that works as a independent one because when I click on it it will erase all the other ones and I would like that the subform only searches for that value.

Here is the code that I've been using to erase all other values of comboboxes :
Private Sub cboPO_Click()
Me.cboNom = ""
Me.cboNom.Requery
Me.cboDiscipline = ""
Me.cboDiscipline.Requery
Me.cboClient = ""
Me.cboClient.Requery
Me.cboProject = ""
Me.cboProject.Requery
Me.cboFamille = ""
Me.cboFamille.Requery
Me.cboPO.SetFocus

Call UpdateFields
End Sub

And the code (that doesn't work) to populate the query of the subform :
SELECT tblCommandes.PO, tblCommandes.[OR], tblCommandes.NoUltragen, tblCommandes.Client, tblCommandes.NoClient, tblCommandes.Nom, tblCommandes.Description, tblCommandes.ValeurPO, tblCommandes.ValeurREAL, [ValeurReal]-[ValeurPO] AS ValeurDiff, tblCommandes.LivraisonPrévu, tblCommandes.DateButoir, tblCommandes.LivraisonREAL, CalcWorkdays([LivraisonPrévu],[LivraisonREAL]) & " Jours" AS LivraisonDiff, tblCommandes.NoReception, tblCommandes.[Conformité Tech], tblCommandes.[Service QLTY], tblCommandes.[Service Ap/V], tblCommandes.Suivit, tblCommandes.Soumission, tblCommandes.Docs, tblCommandes.DocsLink, tblFournisseurs.Discipline, tblFournisseurs.Famille
FROM tblCommandes INNER JOIN tblFournisseurs ON tblCommandes.Nom = tblFournisseurs.Nom
WHERE (((tblCommandes.NoUltragen) =[Forms]![frmSelection]![cboProject]) OR isnull([Forms]![frmSelection]![cboProject]))
AND (((tblCommandes.Client)=[Forms]![frmSelection]![cboClient])OR isnull([Forms]![frmSelection]![cboClient]))
AND (((tblCommandes.Nom)=[Forms]![frmSelection]![cboNom])OR isnull([Forms]![frmSelection]![cboNom]))
AND (((tblFournisseurs.Discipline) =[Forms]![frmSelection]![cboDiscipline])OR isnull([Forms]![frmSelection]![cboDiscipline]))
AND (((tblFournisseurs.Famille)=[Forms]![frmSelection]![cboFamille])OR isnull([Forms]![frmSelection]![cboFamille]))
OR (((tblCommandes.PO)=[Forms]![frmSelection]![cboPO]));

it doesn't give anything and I don't understant why because I have information that should apear in the subform.

thanks

Just a guess here ... you are linking two tables, I would recommend a LEFT join so that all null records on one side will not prohibit the records on the other side from being returned.

-dK

The problem with this is that I need it to give me the result if there is one, two or more comboboxes that have a value or if there is only one and if I use a left inner I have a result but don't have anything in the Discipline and the famille field

any other suggestions?

thanks

My apologies, no idea unless I can see the database ... I was just putting a guess out there based on what I have seen in the past.

-dK

Thanks for the try

I would like to post part of my database but the buttons in the forum don't work ...

anyone has an idea what I could do or where I could find information on that?

Thanks

I am not sure ... can check out the FAQ to make sure you are doing everything correctly.

Anyone else?

-dK

The SQL you have posted will only return results if every combo has a value selected and there is a record that matches of the record has a nul value. Do you want to leave some of the combos without a value?

Plus I think you need another set of brackets around your AND lines in the Where clause so that your OR line is handled separately.

I have tried to add the extra brackets but it didn't work

as for the purpose of the comboboxes, what I want it that it will search all the data listed in the select section that has a correspondence will all the comboxes that have a value. It can vary up to 5 comboboxes.

As said before the PO comboboxe will erase all the other comboboxes and search only for the Po combobox value

Hope it helps
thanks

I guess the confusing part to me is the Or IsNull bits ... I don't use that type of implementation because of the linking but it may be causing you problems because you don't have every condition covered.

For instance, let's say it was 3 combo boxes. Each column of your query you would need the combo box reference and 2 IsNulls to satisfy any given set of conditions depending on what combo box was selected and how for a total of 9 so the or'd logic will work correctly.

Col 1: [][]cbo/isnull/isnull
isnull/[][]cbo/isnull
isnull/isnull/[][]cbo
Col 2: isnull/[][]cbo/isnull
[][]cbo/isnull/isnull
isnull/isnull/[][]cbo
Col 3: isnull/isnull/[][]cbo
isnull/isnull/[][]cbo
[][]cbo/isnull/isnull

How about dropping all of the IsNulls and for your combo box reference and in the criterial put:

Like [][]cbo & "*"

This way when your last combo box clears the others, it is an empty text field so the criteria for the other columns would be 'all' and allow all records to be displayed.

-dK

Hi

It seems really clear to you but I dont understand where I could put that info.. I'm kind of new in all of this

I added the "is null" part because I read it on the net that it could help me remove the condition if that combobox is set to null (has no values).

How could I use that :

Like [][]cbo & "*"

Thanks

Sorry ... [][]cbo was my shortcut for the reference ...

Forms!FormName!cboComboBoxName

As a way to contrast what I think is going on verses what needs to be done.

Again, I am not that great at just reading queries per se, just applying to what I have done in the past with what it sounds like what you are wanting to do.

-dK

dK is on the right lines.

As I said before, the SQL you posted requires a match in every combo. If you want the option to match just some of the combos you can use LIKE [combovalue] & "*" to produce the wildcard operator if there is no value selected. The wildcard still won't return a null value, so you need to add OR IsNull to catch those.

Where you have a reasonably complex WHERE clause, I would set it up using one line at a time. That way your trouble shooting is limited to the extra qualifier you just added.