I would really appreciate it if someone would help simplify my code. My command button works most of the time, but not all of the time because I have logic problems.
Currently, when I copy a record in a form, it grabs all of the fields except my primary key field and duplicates the record. Then I point at the new record and clear out a few fields based on a criteria in the new record. The problem comes when my data is filtered. If my form is filtered and I make and save changes to a record, which would exclude the record from my filter, and hit copy before I refresh the filter, then my record get's filtered out and I am pointing not to the new record, but to the max record in the current filter and it clear out fields in my existing record. Regardless of the filter or what record I am pointing at after it is copied, it is essential that I do not have two copies without clearing out cetain fields. I hope this makes sense and would appreciate any advice from a more experience programmer.

Thank You,
Ty

-------
Private Sub cmdDupe_Click()
Dim strSQLStmt As String
Dim intIter As Integer
Dim recsetMax As Recordset
Dim lngNewRecID As Long
Dim recsetClone As Recordset

'DBEngine.Workspaces(0).BeginTrans

'On Error GoTo aborttrans
strSQLStmt = "INSERT INTO Marketing SELECT

' Copy all of the fields from the current record,
' with exception to the items listed below which have been removed.
For intIter = 2 To CurrentDb.TableDefs("Marketing").Fields.Count
If intIter > 2 Then strSQLStmt = strSQLStmt & ", "
strSQLStmt = strSQLStmt & CurrentDb.TableDefs("Marketing").Fields(intIter - 1).Name
Next intIter

strSQLStmt = strSQLStmt & " FROM Marketing where MarketingID = " & Me.MarketingID
CurrentDb.Execute strSQLStmt
DoEvents

' Refresh the displayed data
Me.Filter = strFilter()
Me.FilterOn = True
voidSetTotals

' Find the new marketing record
Set recsetMax = CurrentDb.OpenRecordset("Select max(MarketingID) from Marketing", dbOpenSnapshot)
lngNewRecID = recsetMax(0)
recsetMax.Close
Set recsetMax = Nothing

' Point at the new marketing record
Set recsetClone = Me.RecordsetClone
recsetClone.FindFirst "MarketingID = " & lngNewRecID
Me.Bookmark = recsetClone.Bookmark
recsetClone.Close
Set recsetClone = Nothing

If lngNewRecID = Me.MarketingID Then
Me.Invoice = Null
Me.ShipDate = Null
Me.ActualCost = Null
Me.Proof = False
Me.AmountTaken = 0
Me.AsOfDate.Value = Now()

If Me.AccountID <> 3 Then
Me.ResellerCombo = Null
Me.EndUserCombo = Null
Me.RollOutFrom = Null
Me.RollOutTo = Null
Me.Quantity = Null
End If

End If

'DoCmd.GoToRecord , , acNewRec

'DBEngine.Workspaces(0).CommitTrans

Exit Sub
aborttrans:
DBEngine.Workspaces(0).Rollback
Exit Sub
End Sub