subForm returns 0

[baouk2000]

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:

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 ) :

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

[CJ_London]

Subforms load before the main form so if F_Menu is your main form, the line

mydate = [Forms]![F_MENU]![s_date]

will fail

[baouk2000]

Thank you, F_MENU is not the principle Form, it is an other Form that is already open before principle Form ( and subForms ) Load

[CJ_London]

other problem you have - in sql, when dates are expressed as a string they need to be in the format of mm/dd/yyyy or the sql standard of yyyy-mm-dd. I prefer the latter

so change

Format(mydate, "dd/mm/yyyy")

to

Format(mydate, "yyyy-mm-dd")

[baouk2000]

Thank you CJ_London, I tested the date format yyyy-mm-dd and it works perfect

[mike60smart]

Hi
You need to look at the design of your tables.

Your table MVMT has what are known as repeating groups.

The fields qte1m through to qte6m need to be records in a related table, and not fields in this table

This also applies to nb1m through nb4m and nb1s through to nb4s

[baouk2000]

Hi,
I made this design because there is relation (formula) taking in consideration qte1m to qte6m ... or nb1m to nbm4m in the same record ..

[mike60smart]

All of those fields should not be in 1 record

[baouk2000]

There is an other way to design. But, it would Take more complicated way to code it in VBA and SQL. I'm not professional ..

[Welshgasman]

Hi,
I made this design because there is relation (formula) taking in consideration qte1m to qte6m ... or nb1m to nbm4m in the same record ..

Perhaps explain a little more of why they are like that. Generally, repeating groups become separate records, but that is not 100% all the time.