Option Compare Database
Option Explicit

Private Sub Form_Load()
Dim dbs As Database
Dim rst As Recordset
Dim w, ProjectTotal() As Single
Dim ProjectNumber, Quantities As Variant
Dim x, y, z, rcdcnt As Integer
Dim strSQL As String

Set dbs = CurrentDb

'Find incomplete projects
Set rst = dbs.OpenRecordset("SELECT ProjectNumber, Completed FROM tblProjects WHERE (Completed=Off)")
rcdcnt = rst.RecordCount
If rcdcnt > 0 Then
    ProjectNumber = rst.GetRows(rcdcnt) 'Put incomplete project numbers into variable
Else
    GoTo EndSubmit_Click
End If

Redim ProjectTotal(rcdcnt,0,0)

For x = 0 to rcdcnt - 1
	'Find expense quantities for incomplete project
	Set strSQL = "SELECT Quantity FROM tblExpenses WHERE ProjectNumber = " & ProjectNumber(0,x)
	Set rst = dbs.OpenRecordset(strSQL)
	y = rst.RecordCount
	If y > 0 Then
		Quantities = rst.GetRows(y) 'Put quantities into variable
	End If
	
	'Find expense quantity prices for incomplete project
	Set strSQL = "SELECT UnitPrice FROM tblExpenses WHERE ProjectNumber = " & ProjectNumber(0,x)
	Set rst = dbs.OpenRecordset(strSQL)
	z = rst.RecordCount
	If z > 0 Then
		UnitPrices = rst.GetRows(z) 'Put quantities into variable
	End If
	
	If y <> z Then 'A check to make sure quantities and prices returned the same amount of records
		MsgBox "y doesn't equal z"
		GoTo EndForm_Load
	End If
	
	For y = 0 to z - 1
		w = 0
		w = w + (Quantities(0,y) * UnitPrices(0,z))
	Next y
	ProjectTotal(x,0) = w
	
Next x

EndForm_Load:
rst.Close

End Sub