Joel, On 12/1/05, Canfield, Joel <JCanfield at pacadvantage.org> wrote: > Our HelpDesk work order update tool is based on a form that calls > itself, checking for changed fields to update in the db (MSSQL) > > There are 17 changeable fields, and currently, I'm looping through each > field, comparing the current value I've gathered from the db to the new > value submitted by the form. Any changed values are concatenated onto > strings which ultimately form the db update query. > > There has to be a more efficient way, right? > > Here's a bit of code for two of the 17 fields: > > If Not(strCVTASK = strNewTASK) Then > strFields = strFields & ", TASK" > strValues = strValues & ", '" & strNewTASK & "'" > End If > If Not(strCVTYPE = strNewTYPE) Then > strFields = strFields & ", TYPE" > strValues = strValues & ", '" & strNewTYPE & "'" > End If > > and they all get strung together here: > > "insert into TASKS (" & strFields & ") values (" & strValues & ")" > > Ideas? Can you help me understand why you are using an insert here instead of an update? It seems like you have a purpose in mind, but I can't figure it out. Why aren't you just updating the original record? If you did that, you could issue an UPDATE statement with all fields and not have to worry about which have been modified. If you are trying to keep a history of the work order, I would suggest just copying the old record to a history table. But, if there is some reason you have to do it this way, I think you've pretty much nailed how it has to be done. -- Matt Warden Miami University Oxford, OH, USA http://mattwarden.com This email proudly and graciously contributes to entropy.