My goal is to have a custom field (BG_USER_08) that is a count of the # of times a defect has been Re-Opened (from Closed to Open). I want to create an INSERT statement for the BUG table that will execute each time the defect dialog box is opened, and update the BG_USER_08 field with the new count. So far I haven't had any luck and the BG_USER_08 is always blank. I've tested out the Select statement in the Excel Generator (with a manual defect ID) and it works as it's suppose to. Any advice?


--------------------------------------
Function ReOpenCount
Fields = Bug_Fields

Dim td
Dim com
Dim rec

set td = TDConnection
set com = td.Command

com.CommandText ="INSERT INTO BUG('BG_USER_08')" & _
" VALUES(SELECT COUNT(1) FROM Audit_log JOIN" & _
" Audit_Properties ON AU_ACTION_ID = AP_ACTION_ID" & _
" WHERE AP_Field_Name= 'BG_STATUS'" & _
" AND AP_OLD_VALUE = 'Closed' & _
" AND AP_NEW_VALUE= 'Open'" & _
" AND AU_ENTITY_ID = " & Fields("BG_BUG_ID").Value & ")" & _

"WHERE BG_BUG_ID =" & Fields("BG_BUG_ID").Value

Set rec = com.Execute

set com = nothing
set rec = nothing
set td = nothing
End Function

-------