Ben 't verder aan 't uittesten.
Maar de code doet nog niet wat ik wil.
M.
Dim trsql As String
Dim strsql As String
Dim rs As ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim Teller As Integer
Dim A As Integer
Dim spe As Long
Dim gro As Date
Dim tek As Long
Dim rst As New ADODB.Recordset
strsql = "DELETE tblTelling.* FROM tblTelling"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strsql
DoCmd.SetWarnings (True)
MsgBox "tblTelling is leeg"
Set rs = New ADODB.Recordset
Set cnn = CurrentProject.Connection
rs.Open "QryVoorraad", cnn, adOpenStatic, adLockReadOnly, adCmdStoredProc
rs.MoveLast
Teller = rs.RecordCount
rs.MoveFirst
For A = 1 To Teller
spe = rs.Fields("ProductId")
gro = rs.Fields("Teldatum")
trsql = "SELECT [Voorraad]+(Sum([orderdetail.mutaantalin]-[orderdetail.aantal]))-[Minvoorraad] AS Tekort, Orderdetail.ProductId, Sum(Orderdetail.MutAantalIn) AS SomVanMutAantalIn, Sum(Orderdetail.Aantal) AS Verkocht, Product.Voorraad, Product.Minvoorraad" _
& " FROM Product INNER JOIN (Orders INNER JOIN Orderdetail ON Orders.Orderid = Orderdetail.OrderId) ON Product.ProductId = Orderdetail.ProductId" _
& " WHERE (((Orders.Orderdatum) >= " & [gro] & ")) And Product.ProductId = " & spe & " " _
& " GROUP BY Orderdetail.ProductId, Product.Voorraad, Product.Minvoorraad" _
& " HAVING ((Product.Minvoorraad) <> 0)"
rst.Open trsql, cnn, adOpenStatic, adLockReadOnly
tek = rst.Fields("tekort")
strsql = "INSERT INTO tblTelling ( ProductId, Tekort ) Values(" & spe & ", " & tek & " )"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strsql
DoCmd.SetWarnings (True)
rst.Close
Next A
MsgBox "Klaar tblTelling is gevuld"
cnn.Close
Set rs = Nothing
Set cnn = Nothing