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:
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:
Code:
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
Attachments
Last edited: