Query and checkbox

[nilses]

Hello,

I have a problem with my query and my form. I have a form with 2 listbox, 8 check box (the number height is 1+2+3+4+5+6+7), 2 text area and 1 button (it's for execute a query).

I would like to do a multiselect with my 8 checkbox

My first idea was to create a function with If elseif then and in my query, i call my function but i'm not sure when i write my function and when i call my function in my query. What do you think? Can you help me?.

In my query: myfunction:region([?])

Function region(?)

If Forms!MainSwitchBoardTest!region1.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "1"
ElseIf Forms!MainSwitchBoardTest!region1.Value = True And Forms!MainSwitchBoardTest!region2.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "1" And [4_ExtractionDataEndTest].Ident_Region = "2"
ElseIf Forms!MainSwitchBoardTest!region1.Value = True And Forms!MainSwitchBoardTest!region3.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "1" And [4_ExtractionDataEndTest].Ident_Region = "3"
ElseIf Forms!MainSwitchBoardTest!region1.Value = True And Forms!MainSwitchBoardTest!region4.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "1" And [4_ExtractionDataEndTest].Ident_Region = "4"
ElseIf Forms!MainSwitchBoardTest!region1.Value = True And Forms!MainSwitchBoardTest!region5.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "1" And [4_ExtractionDataEndTest].Ident_Region = "5"
ElseIf Forms!MainSwitchBoardTest!region1.Value = True And Forms!MainSwitchBoardTest!region6.Value = true1 Then
[4_ExtractionDataEndTest].Ident_Region = "1" And [4_ExtractionDataEndTest].Ident_Region = "6"
ElseIf Forms!MainSwitchBoardTest!region1.Value = True And Forms!MainSwitchBoardTest!region7.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "1" And [4_ExtractionDataEndTest].Ident_Region = "7"
ElseIf Forms!MainSwitchBoardTest!region2.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "2"
ElseIf Forms!MainSwitchBoardTest!region2.Value = True And Forms!MainSwitchBoardTest!region1.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "2" And [4_ExtractionDataEndTest].Ident_Region = "1"
ElseIf Forms!MainSwitchBoardTest!region2.Value = True And Forms!MainSwitchBoardTest!region3.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "2" And [4_ExtractionDataEndTest].Ident_Region = "3"
ElseIf Forms!MainSwitchBoardTest!region2.Value = True And Forms!MainSwitchBoardTest!region4.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "2" And [4_ExtractionDataEndTest].Ident_Region = "4"
ElseIf Forms!MainSwitchBoardTest!region2.Value = True And Forms!MainSwitchBoardTest!region5.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "2" And [4_ExtractionDataEndTest].Ident_Region = "5"
ElseIf Forms!MainSwitchBoardTest!region2.Value = True And Forms!MainSwitchBoardTest!region6.Value = true1 Then
[4_ExtractionDataEndTest].Ident_Region = "2" And [4_ExtractionDataEndTest].Ident_Region = "6"
ElseIf Forms!MainSwitchBoardTest!region2.Value = True And Forms!MainSwitchBoardTest!region7.Value = True Then
[4_ExtractionDataEndTest].Ident_Region = "2" And [4_ExtractionDataEndTest].Ident_Region = "7"
End If

End Function

My second idea was to create a iif like this but when i run my query, my form said

firts pop up windows:Formulaires!MainSwitchBoard![france].value
second pop up windows: Operation is Cancel

and i don't no why?.

......(([2_TblExtractionData].[NomClient])=Formulaires!MainSwitchBoardTest![client])) And (([2_TblExtractionData].[Ident_Region]=IIf(Formulaires!MainSwitchBoard![france].value=True,[2_TblExtractionData].[Ident_Region],IIf(Formulaires!MainSwitchBoard![region1].value=True,"1",[2_TblExtractionData].[Ident_Region]) Or IIf(Formulaires!MainSwitchBoard![region2].value=True,"2",[2_TblExtractionData].[Ident_Region]) Or IIf(Formulaires!MainSwitchBoard![region3].value=True,"3",[2_TblExtractionData].[Ident_Region]) Or IIf(Formulaires!MainSwitchBoard![region4].value=True,"4",[2_TblExtractionData].[Ident_Region]) Or IIf(Formulaires!MainSwitchBoard![region5].value=True,"5",[2_TblExtractionData].[Ident_Region]) Or IIf(Formulaires!MainSwitchBoard![region6].value=True,"6",[2_TblExtractionData].[Ident_Region]) Or IIf(Formulaires!MainSwitchBoard![region7].value=True,"7",[2_TblExtractionData].[Ident_Region]))));

thanks for your help

nilses

 

[boblarson]

First of all, with that many checkboxes, move to Select Case statements, they're a whole lot easier to read than If Then ElseIf's.

 

[nilses]

Thanks Pat Hartman for your help, i found a solution for a listbox. I think, the checkbox is more difficult, but i would help about this point. This is the code for one list box but how do you do if i have 3 listbox, 2 text area like this?.

I have one listbox for customers
I have one listbox for the country (multiselect)
I have one listbox for the type of machine(multiselect)
I have one 2 text area for the start date and End date

In my query (she's not dynamic), i use Forms!MyForms!Thenameofmycontrol for customers, country and type of machine and i use (Between Forms!MyForms!Thenameofmycontrol(StartDate) And Forms!MyForms!Thenameofmycontrol(Enddate)) for the date (DD/MM/YY)

My code is this but i don't no how can i do if i have more controls.

Option Compare Database
Option Explicit

Private Sub Commande3_Click()
Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![List0]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = ctl.ItemData(Itm)
Else
Criteria = Criteria & "," & ctl.ItemData(Itm)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("MultiSelect Criteria Example")
Q.SQL = "SELECT * FROM Commandes Where [N° commande] In (" & Criteria & ");"
Q.Close

' Run the query.
DoCmd.OpenQuery "MultiSelect Criteria Example"

End Sub


Thanks for your help

Nilses