Thank you all who helped me to overcome my syntax errors.
Now I modified my first VBA module code and it doesn't generate compile error anymore BUT it stucks on the following line:
mID = orig_.[MEMBER ID]
generatinG a run-time Error 424 saying bject required
****************************************************************
Option Compare Database
Sub deleteDupl()
Dim cmd As ADODB.Command
Dim rst As Recordset
Dim strSQL As String
Dim mID As String
Dim dShot As Date
Dim mAccept As String
Dim mAsked As String
Dim DMin As Date
Dim DMax As Date
'Dim dMyDate As Date
Dim dDateMin As Date
Dim dDateMax As Date
'dMyDate = CDate(date)
'If dMyDate >= dDateMin And dMyDate <= dDateMax Then
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
'fill a recordset with distinct member ID's
cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT DISTINCT [MEMBER ID] FROM makepart1"
cmd.CommandText = strSQL
Set rst = cmd.Execute
'delete cycle
mID = orig_.[MEMBER ID]
mAccept = orig_.[ACCEPTS CALLS]
mAsked = orig_.[Asked Question]
dShot = orig_.[date Of Shot If Known]
DMin = 9 / 1 / 2003
DMax = 3 / 31 / 2004
dDateMin = CDate(DMin) 'dMin 9/1/2003
dDateMax = CDate(DMax) 'dMax = 3/31/2004
dShot = CDate(Date)
If Not rst.EOF Then rst.MoveFirst
Do While Not (orig_)
Do While orig_.[Member_id] = mID
'If dMyDate >= dDateMin And dMyDate <= dDateMax Then
If dShot >= dDateMin And dShot <= dDateMax And bAccept = "y" And mAsked = "YES" Then
rst.MoveNext
ElseIf dShot >= dDateMin And dShot <= dDateMax And bAccept = "y" Then
rst.MoveNext
ElseIf dShot >= dDateMin And dShot <= dDateMax And mAsked = "YES" Then
rst.MoveNext
ElseIf dShot >= dDateMin And dShot <= dDateMin And dDateMax Then
rst.MoveNext
ElseIf bAccept = "y" And mAsked = "YES" Then
rst.MoveNext
ElseIf bAccept = "y" Then
rst.MoveNext
ElseIf mAsked = "YES" Then
rst.MoveNext
Else
rst.Delete
End If
Loop
Loop
Set rst = Nothing
Set cmd = Nothing
End Sub
****************************
It looks like something is wrong with my assignement statement....
mID = orig_.[MEMBER ID]
Now I modified my first VBA module code and it doesn't generate compile error anymore BUT it stucks on the following line:
mID = orig_.[MEMBER ID]
generatinG a run-time Error 424 saying bject required
****************************************************************
Option Compare Database
Sub deleteDupl()
Dim cmd As ADODB.Command
Dim rst As Recordset
Dim strSQL As String
Dim mID As String
Dim dShot As Date
Dim mAccept As String
Dim mAsked As String
Dim DMin As Date
Dim DMax As Date
'Dim dMyDate As Date
Dim dDateMin As Date
Dim dDateMax As Date
'dMyDate = CDate(date)
'If dMyDate >= dDateMin And dMyDate <= dDateMax Then
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
'fill a recordset with distinct member ID's
cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT DISTINCT [MEMBER ID] FROM makepart1"
cmd.CommandText = strSQL
Set rst = cmd.Execute
'delete cycle
mID = orig_.[MEMBER ID]
mAccept = orig_.[ACCEPTS CALLS]
mAsked = orig_.[Asked Question]
dShot = orig_.[date Of Shot If Known]
DMin = 9 / 1 / 2003
DMax = 3 / 31 / 2004
dDateMin = CDate(DMin) 'dMin 9/1/2003
dDateMax = CDate(DMax) 'dMax = 3/31/2004
dShot = CDate(Date)
If Not rst.EOF Then rst.MoveFirst
Do While Not (orig_)
Do While orig_.[Member_id] = mID
'If dMyDate >= dDateMin And dMyDate <= dDateMax Then
If dShot >= dDateMin And dShot <= dDateMax And bAccept = "y" And mAsked = "YES" Then
rst.MoveNext
ElseIf dShot >= dDateMin And dShot <= dDateMax And bAccept = "y" Then
rst.MoveNext
ElseIf dShot >= dDateMin And dShot <= dDateMax And mAsked = "YES" Then
rst.MoveNext
ElseIf dShot >= dDateMin And dShot <= dDateMin And dDateMax Then
rst.MoveNext
ElseIf bAccept = "y" And mAsked = "YES" Then
rst.MoveNext
ElseIf bAccept = "y" Then
rst.MoveNext
ElseIf mAsked = "YES" Then
rst.MoveNext
Else
rst.Delete
End If
Loop
Loop
Set rst = Nothing
Set cmd = Nothing
End Sub
****************************
It looks like something is wrong with my assignement statement....
mID = orig_.[MEMBER ID]