Hello,
I have a matter with subforms that not return good result.
subforms are based on conditional queries. queries return results when tested. However subforms return 0
my database is as follows
Subforms are inserted into a main form which takes the (selected) date from another form (F_MENU).
I can't find the error in the VBA code of the subform (form_load()).
here is the VBA code included in the subform:
Code:
Private Sub Form_Load()
Dim accessRS As Recordset
Dim strq3 As String
Dim db As Database
Dim mydate As Date
Set db = CurrentDb
mydate = [Forms]![F_MENU]![s_date]
strq3 = "select datemvt, CIMAF, MIRA, FAKOTRANS, OKPLAST, TOTAL_ENTREE from sR_clinkerE where datemvt=#" & Format(mydate, "dd/mm/yyyy") & "# ;"
Set accessRS = db.OpenRecordset(strq3)
If accessRS.RecordCount > 0 Then
Me.cimaf_clnk = IIf(Nz(accessRS![CIMAF], 0) = 0, 0, accessRS![CIMAF])
Me.mira_clnk = IIf(Nz(accessRS![MIRA], 0) = 0, 0, accessRS![MIRA])
Me.FAKOTRANS_clnk = IIf(Nz(accessRS![FAKOTRANS], 0) = 0, 0, accessRS![FAKOTRANS])
Me.okplast_clnk = IIf(Nz(accessRS![OKPLAST], 0) = 0, 0, accessRS![OKPLAST])
Me.TOTAL_ENTREE_clnk = IIf(Nz(accessRS![TOTAL_ENTREE], 0) = 0, 0, accessRS![TOTAL_ENTREE])
Else
Me.cimaf_clnk = 0
Me.mira_clnk = 0
Me.FAKOTRANS_clnk = 0
Me.okplast_clnk = 0
Me.TOTAL_ENTREE_clnk = 0
End If
accessRS.Close
Set accessRS = Nothing
End Sub
this subform is based on a conditionnal query (sR_clinkerE here ) :
Code:
SELECT type.Type AS TYPE, Mvmt.datemvt, Sum(IIf(Len([Mvmt]![qte1m])=0,"0",[Mvmt]![qte1m])) AS CIMAF, Sum(IIf(Len([Mvmt]![qte2m])=0,"0",[Mvmt]![qte2m])) AS MIRA, Sum(IIf(Len([Mvmt]![qte3m])=0,"0",[Mvmt]![qte3m])) AS FAKOTRANS, Sum(IIf(Len([Mvmt]![qte4m])=0,"0",[Mvmt]![qte4m])) AS OKPLAST, Sum([Mvmt].[qte1m]+[Mvmt].[qte2m]+[Mvmt].[qte3m]+[Mvmt].[qte4m]) AS TOTAL_ENTREE, Sum(Mvmt.qte5m) AS SEC, Sum(Mvmt.qte6m) AS MOUILLE, Sum(Mvmt.total_qte) AS TOTAL2
FROM type INNER JOIN (typemvt INNER JOIN (Produit INNER JOIN Mvmt ON Produit.idtype = Mvmt.idtypefk) ON typemvt.idtypemvt = Mvmt.idtypemvtfk) ON type.idtype = Produit.idtype
WHERE (((Produit.idtype)=1) AND ((typemvt.idtypemvt)=1) AND ((Mvmt.idtypefk)=[Produit]![idtype]) AND ((Mvmt.idtypemvtfk)=[typemvt]![idtypemvt]))
GROUP BY type.Type, Mvmt.datemvt;
If anyone could help