Error 3071 (1 Viewer)

[babymeosh]

Hello everybody,
At 1st i'm not an english speaker so you will excuse errors that i may commit.
And Now, my problem. I'm experiencing an error in Access 2013 when trying to run a form that i'm doing for my job. That form is designed to help users access to some statistics. So, i've made a form (Copie de BQ1C1) on which they can choose some parameters to run a query (named "ReqTemp"). That query is a crosstab query. Users can choose flow, partners, and commodities (yes, it,s a form for foreign trade statistics). 3 buttons are added, the 1st one "ok" collects the user-selected parameters to run my query "ReqTemp". That query is used as a source of a subform(also named like my query, "ReqTemp"), on my main Form. The subform is in worksheet mode. Another button, "extracteur" export my data in excel format.
Everything seems to work fine, but when i try to open the form at Access launching, an error (3071) says this expression is typed incorrectly, or it is too complex to be evaluated. But when i paste and run directly the query, everything works fine!!

Romeo

Ps:I've posted a pic of my form, and this is my query:

Option Compare Database
Public Function ConcatVBA()

Dim strINIT As String
Dim strWHERE As String
Dim strGROUP As String
Dim strCrit As String

strWHERE = ""
strGROUP = ""
strCrit = ""

'Début de syntaxe
strINIT = "TRANSFORM Sum(BonneBanque1.Valstat) AS SommeDeValstat " & _
"SELECT FLUX.Lib_FR, PAYS.Lib_FR, BonneBanque1.PRODUIT, NTSBENIN.Lib_FR, Sum(BonneBanque1.Valstat) AS [Total de Valstat] " & _
"FROM ((BonneBanque1 INNER JOIN FLUX ON BonneBanque1.FLUX = FLUX.Code) INNER JOIN PAYS ON BonneBanque1.PARTENAIRE = PAYS.Code) INNER JOIN NTSBENIN ON BonneBanque1.PRODUIT = NTSBENIN.Code "

'SUITE DE LA SYNTAXE: GROUP et PIVOT
strGROUP = " GROUP BY FLUX.Lib_FR, PAYS.Lib_FR, BonneBanque1.PRODUIT, NTSBENIN.Lib_FR " & _
"PIVOT BonneBanque1.Annee;"

strW1 = "WHERE (((BonneBanque1.Mois) =" & Chr$(34) & "00" & Chr$(34) & ") And ((Len([BonneBanque1].[PRODUIT])) = 4) And ((BonneBanque1.Flux) = " & Chr$(34) & Me.Flux & Chr$(34) & ")" & " And ((BonneBanque1.SYSCOM) =  " & Chr$(34) & "2" & Chr$(34) & " Or (BonneBanque1.SYSCOM) = " & Chr$(34) & "S" & Chr$(34) & ") " & "AND "

'Boucle strWHERE et strCrit
For Each Varsol In Me!Pays.ItemsSelected
  If strW2 <> "" Then strW2 = strW2 & " OR "
  'Remplissage de pays
  strW2 = strW2 & "[BonneBanque1].Partenaire = '" & Me.Pays.ItemData(Varsol) & "'"
'End If
Next Varsol

'Boucle PRODUIT strWHERE et strCrit
For Each varsol2 In Me!PRODUIT.ItemsSelected
  If strW3 <> "" Then strW3 = strW3 & " OR "
  'Remplissage de pays
  strW3 = strW3 & "[BonneBanque1].PRODUIT = '" & Me.PRODUIT.ItemData(varsol2) & "'"
'End If
Next varsol2

'Construction du strCrit
strCrit = strW1 & "(" & strW2 & ")" & " AND " & "(" & strW3 & ")" & ")"


If strCrit <> "" Then strWHERE = strWHERE & strCrit
ConcatVBA = strINIT & strWHERE & strGROUP
Debug.Print ConcatVBA

End Function

Private Sub BoutonPays_Click()

Dim ligne As Integer
Dim colonne
colonne = 1

For ligne = 0 To Me.Pays.ListCount - 1
If Me.Pays.Selected(ligne) Then
' Si la ligne est sélectionnée, on stocke sa valeur dans la variable "varsol"
Varsol = Me.Pays.Column(colonne, ligne)
Me.iPays.AddItem Me.Pays.Column(colonne, ligne)
End If
Next ligne
End Sub

Private Sub BoutonPaysVid_Click()
Me.iPays.RowSource = ""
Varsol = Empty

End Sub

Private Sub BoutonProduit_Click()
'Dim varsol2 As Variant
'jai efface les 2 de colonne2 et ligne2

Dim ligne2 As Integer
Dim colonne2
colonne2 = 1

For ligne2 = 0 To Me.PRODUIT.ListCount - 1
If Me.PRODUIT.Selected(ligne2) Then
' Si la ligne est sélectionnée, on stocke sa valeur dans la variable "varsol"
varsol2 = Me.PRODUIT.Column(colonne2, ligne2)
Me.iProduit.AddItem Me.PRODUIT.Column(colonne2, ligne2)
End If
Next ligne2
End Sub

Private Sub BoutonProduitVid_Click()
Me.iProduit.RowSource = ""
varsol2 = Empty

End Sub

Private Sub Form_Open(Cancel As Integer)
'DoCmd.Maximize
End Sub

Private Sub BoutonAnnuler_Click()

End Sub

Private Sub BoutonOK_Click()
Dim NomReq As String
Dim DefReq As Variant
Dim strSQL As String
NomReq = "ReqTemp"

strSQL = ConcatVBA


'ecrire un petit truc pour verifier que la requete existe et la supprimer

Set DefReq = CurrentDb.QueryDefs(NomReq)
'DefReq.SQL = strSQL
'Debug.Print strSQL
DefReq.Close
RefreshDatabaseWindow
Me.ReqTemp.Form.RecordSource = strSQL

'Form_ReqTemp.Form.RecordSource = strSQL
'DoCmd.OpenForm "ReqTemp", acFormPivotTable

End Sub

Private Sub Extracteur_Click()

Dim NomReq As String
Dim DefReq As Variant
Dim strSQL As String
NomReq = "ReqTemp"

strSQL = ConcatVBA


'ecrire un petit truc pour verifier que la requete existe et la supprimer

Set DefReq = CurrentDb.QueryDefs(NomReq)

DefReq.SQL = strSQL
DefReq.Close
RefreshDatabaseWindow

DoCmd.OutputTo acOutputForm, "ReqTemp", acFormatXLS, "C:\Users\Romeo\Documents\INSAE DOCUMENTS\Extracteur\" & Format(Date, "yyyymmdd") & "_Valeur.xls"

'Me.PivotTable.export "DODO", plExportActionOpenInExcel
MsgBox "fin"
'DoCmd.Close

End Sub

 

[pr2-eugin]

babymeosh, Welcome to AWF..

Would much appreciate if you could Please use Code Tags when posting VBA Code.