Again - Thanks John
Using your directions, I have written the code in needed to complete what I wanted to do.
For Anyone interested, below is the code that I wrote:
Code:
Private Sub cmdGenerate_Click()
'step thru records in table JobItems, assign Markups and determine
'Selling Price based on the markup (Qty * QuoteSpan * Cost)
'if MasterID = "01" (HTS beam) then we must determine the aggregate length
'of all equal series and depths (ItemID)to detemine the MarkUp
'Else find the MarkUp of each product in table InMarkUp
'To determine the aggregate length, design a Group By query similar to [JobItems Query1]
Dim rstSQL As Recordset
Dim rstTable As Recordset
Dim SQL As String
'place SQL statement into string
SQL = "SELECT JobItems.[ItemId], Sum([QuoteSpan]*[QTY]) AS TotalLf FROM JobItems GROUP BY JobItems.[ItemId];"
'open the SQL recordset
Set rstSQL = CurrentDb.OpenRecordset(SQL)
rstSQL.MoveFirst
'set thru records
While Not rstSQL.EOF
'write ItemID to me.ItemID
Me.ItemID = rstSQL!ItemID
'determine if group of items = HTS Beams (MasterID = 01)
If Left(rstSQL!ItemID, 2) = "01" Then 'is HTS Beam
'determine aggregate length
If rstSQL!totallf > 200 Then
Me.MarkUp = ".25"
Else: Me.MarkUp = ".35"
End If
MsgBox ""
Else
Me.MarkUp = ".50"
MsgBox "not beam"
End If
'write markups to table JobItems
'must step thru all grouped records
Set rstTable = CurrentDb.OpenRecordset("JobItems")
rstTable.MoveFirst
'step thru records
While Not rstTable.EOF
If rstTable!ItemID = Me.ItemID Then 'update markup with current markup
rstTable.Edit
rstTable!MarkUp = Me.MarkUp
rstTable.Update
End If
rstTable.MoveNext
Wend
rstSQL.MoveNext
Wend
End Sub
Note sure how to insert the above code into a 'code box' - sorry
Again Thanks