Run-time Error 424 :object required

[RinaGreen]

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]

 

[RoyVidar]

Yes it is something with the assigning - what is "orig_"?

If it is a form, use for instance:

[tt]forms("orig_")("MemberID")[/tt]

if it is something else, then please tell us what it is.

BTW - you're getting some challenges with the dates too, they'll proably become so close to 0, that they'll show "30/12/1899", try:

[tt] DMin = #9/1/2003#[/tt]

- else it becomes a mathematical expression;-)

Roy-Vidar

 

[RinaGreen]

Actually my code looks like that. Orig_ is a name of the table and I am confused to distinguish between real table name orig_ and rst!

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 EOF(orig_)

rst.MoveNext


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

 

[RoyVidar]

A table isn't an object type (unless you've instantiated an ADOX.table object). You refer to command objects, recordset object variables etc.

[tt]Do While Not rst.eof
' do important stuff with the recordset variable
rst.MoveNext
Loop[/tt]

BTW - you are getting a forwardonly readonly recordset here, you may want to dropt the line Set rst = cmd.Execute in favour of

[tt]set rst=new adodb.recordset
rs.open cmd,,adopenkeyset,adlockoptimistic[/tt]

or since the sql isn't parameterized, only

[tt]set rst=new adodb.recordset
rs.open strSQl,currentproject.connection,adopenkeyset,adlockoptimistic,adcmdtext[/tt]

Roy-Vidar

 

[RinaGreen]

Now it generates a compile error: method or data member is not found...for the line:

mID = rst.[MEMBER ID]

I am not sure how program will know what is the original table (ORIG_) since I refer just to the recordset? How should I specify it? Sorry for the naive question...


This is how my code looks like now...

***************************************************

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 dDateMin As Date
Dim dDateMax As Date

Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
rs.Open cmd, , adOpenKeyset, adLockOptimistic

cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT DISTINCT [MEMBER ID] FROM makepart1"
cmd.CommandText = strSQL

'delete cycle
'''''''


'''''''''''''''''''
'If Not rst.EOF Then rst.MoveFirst

Do While Not rst.EOF

mID = rst.[MEMBER ID]
mAccept = rst.[ACCEPTS CALLS]
mAsked = rst.[Asked Question]
dShot = rst.[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)

rst.MoveNext


Do While orig_.[Member_id] = mID

'If dMyDate >= dDateMin And dMyDate <= dDateMax Then
rst.MoveFirst

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

 

[RinaGreen]

Actually I feel that the following statement does refers from table to recordset....

cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT DISTINCT [MEMBER ID] FROM orig_"
cmd.CommandText = strSQL


Therefore I referenced all other var with rst rather than with ORIG_. hOWEVER it generates a compile error: method or data member is not found...for the line:

mID = rst.[MEMBER ID]

Does it mean that I cannot use square brackets with rst. prefix or anything else?

 

[cmmrfrds]

The dot . operator is used for objects/properties that are known at compile time. The names of recordset variables are only known until the command executes. These are referenced by the bang ! operator.

rst!memberID

 

[RinaGreen]

I did change dot to bang !

Now I got Run-time error 424 stuck on:

rs.Open cmd, , adOpenKeyset, adLockOptimistic

....object not found

*************************************************
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 dDateMin As Date
Dim dDateMax As Date

Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
rs.Open cmd, , adOpenKeyset, adLockOptimistic

cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT DISTINCT [MEMBER ID] FROM orig_"
cmd.CommandText = strSQL

'delete cycle
'''''''


'''''''''''''''''''
'If Not rst.EOF Then rst.MoveFirst

Do While Not rst.EOF

 

[RoyVidar]

1 - you don't use rs, you use rst (as in the assignement the line above)
2 - to use a command object, it need to be instantiated/populated first - you are using the command object before you've populated it

Roy-Vidar

 

[RinaGreen]

Thank you!

Would the following be a correct sequence?


Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

'populate
cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT DISTINCT [MEMBER ID] FROM orig_"
cmd.CommandText = strSQL

'use object

rst.Open cmd, , adOpenKeyset, adLockOptimistic

 

[RinaGreen]

Now when I populated the object first and use it next like the following:

Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
'populate
cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT DISTINCT [MEMBER ID] FROM orig_"
cmd.CommandText = strSQL
'use object
rst.Open cmd, , adOpenKeyset, adLockOptimistic


Do While Not rst!EOF

It looks like I initiated the object, populated it and started to use in the right sequence...

However .....I got run-time Error 3265 : item cannot be found in the collection corresponding to the request name or ordinal

it stuck on the following line:

Do While Not rst!EOF

I am confused why I have this error...

 

[RoyVidar]

Bangs (!) are used for members of a collection, for a recordset object, that would for instance be the field names. Dot's (.) are used to refer to properties and methods of the object. EOF is a property of the recorset object, so:

[tt] rst.EOF[/tt]

BTW an easy check for contents in a recordset:

[tt] if not rst.EOF then
debug.print rst.getstring
end if[/tt]

Then hit ctrl+g to review the result...

Roy-Vidar

 

[RinaGreen]

According to what was just mentioned I used dot (.) for Properties as EOF and bang (!) for for members of a collection ( for a recordset object, that would be the field names)

Therefore there should be no difference of assigning

mID = rst![Member Id]
OR
mAsked = rst![Asked Question]

However I do not get Run-time Error message 3265 for mID = rst![Member Id] which goes first and I do get it for all other fields (for instance mAsked = rst![Asked Question])

Although [Member Id] is the same table’s field as [Asked Question]. Both were declared as strings…...

 

[RinaGreen]

Actually Ihave just understood why. It happened because I populated recordset just with [MWMBER ID] !!!!


cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT DISTINCT [MEMBER ID] FROM orig_"
cmd.CommandText = strSQL

I updated it with :

cmd.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT * FROM orig_"
cmd.CommandText = strSQL

However it doesn't recognise (run-time error 3265 collection again.

I assume that VBA does not like Access syntax (SELECT *)

Is there an olny way to do LIKE THE FOLLOWING?:
strSQL = "SELECT [MEMBER ID], [QUESTION ASKED], ACCEPTSCALLS] FROM orig_"

I mean...is there any way to populate all fields without specifying each of them?