DSum criteria using a variable that has been defined

[beanhead0321]

Hey all,

I wanted to loop through a series of numbers and use them as criteria in a DSum function in an If Statement. This probably is very clear, but once you see the code you'll understand.

Private Sub Command0_Click()

Dim prdtID As Long

For prdtID = 1 To 450
    If Application.DSum("Quantity", "TakeIn", "ProductID = " & prdtID & "") - Application.DSum("Quantity", "TakeOut", "ProductID = " & prdID & "") < 6 Then
    Dim rstLessThan5 As DAO.Recordset
    Set rstLessThan5 = CurrentDb.OpenRecordset("Lessthan5")
        With rstLessThan5
            .AddNew
            .Fields("ProductID") = prdtID
            .Fields("ProductName") = "?"
            .Fields("ProductModel") = "?"
            .Fields("Quantity Left") = Application.DSum("Quantity", "TakeIn", "ProductID = " & prdtID & "") - Application.DSum("Quantity", "TakeOut", "ProductID = " & prdID & "")
            .Update
        End With
    End If
Next

End Sub

The point is to find the difference between items taken out of the stop and items put into the shop to see how much inventory is left over. If the amount left over is less than 6, the product ID, name, model, and left over quantity will be put onto a table to be made into a report. As of right now, I'm running through a series of numbers 1 to 450. I'm getting stuck on the DSum criteria where i try to use this variable. I think somethings wrong with the " and & and # around the variable. I'm also don't know how to refer to the product name and model that are associated with each product ID so it can be inserted into the table. Also, is it possible to have access look up my product ID from a field on a table instead of cycling through 1 to 450 like this?

Thanks guys!

[June7]

If ProductID is a number datatype then: "ProductID = " & prdtID)

If ProductID is a text datatype then: "ProductID = '" & prdtID & "'")

Text datatype requires apostrophe delimiter, # for dates, nothing for numbers.

[beanhead0321]

Works great! Thank you! Are you able to help me with any of the other stuff? I still don't know how to reference the fields in the safe row as the product ID. Also, is there a better way to loop through the product ID field instead of just the numbers?

[June7]

Instead of the 1 to 450 loop and DLookup could open a recordset of the TakeIn table and in loop move to each record to read the ProductID value. The recordset could also be read to retrieve the ProductName and ProductModel. Or since you are saving ProductID, don't save ProductName and ProductModel to this recordset/table and build an Access query that joins this table to the table with ProductName and ProductModel.

I do wonder why not using Access queries to generate this summary info and then calculate the balance. Would have to know more about your data structure.

[beanhead0321]

My first intention was to build a query, but I don't know how to use the difference between the two sums of two tables as a criteria for my query... I'm pretty new to access and I think I can use relationships to automatically put the product Name and Model into the table, but I'm not sure... Thanks for all the help. I'll try the Dlookup and tell you how it goes. I don't actually know the coding and format for it so I'll have to look around.

[beanhead0321]

Alright. It's all working. Thank you very much for all your help!