compare table

[LIORA BEN-EZRA]

I have 2 table : table1 ,table2 I need to check for each
record in "table1"(field "artno" and "batch") exist
in "table2" compare to filed "hnum" and "lotnum"
I wrote this moudle:
------------------------------------
Function Findext(Table1, Table2)
Dim db As Database
Dim r As DAO.Recordset
Dim t As DAO.Recordset
Dim Msg As String
Dim x As Integer
Dim y As Integer
Dim Criteria As String

Set db = CurrentDb
Set t = db.OpenRecordset(Table1, DB_OPEN_TABLE)
Set r = db.OpenRecordset(Table2, DB_OPEN_TABLE)
If t.RecordCount = r.RecordCount Then
Msg = "All item exist"
Else
Msg = "Some item not exist"
While Not t.EOF
Criteria = "lotnum='" & t!batch & "'"
'' and [lotnum] = '" & t!batch & "'"
r.FindFirst Criteria
If Not r.NoMatch Then
t.Delete
End If
t.NextRecordset
Wend
End If

t.Close
r.Close
x = MsgBox(Msg, vbInformation, "Import file - Result")
Findext = ""
End Function
--------------------------------------------------------
"Table1" create from import excel file, "table 2" create
by maketable query - there is no index on the table - is
it a problem??

I received an error at line "r.FindFirst Criteria"
error no' 3252: "the action is not supported for this kind
of object(translate from hebrew-maybe not hte exact word)

Why there is an error

thanks,
liora

 

[Emilia Maxim]

I have 2 table : table1 ,table2 I need to check for each
record in "table1"(field "artno" and "batch") exist
in "table2" compare to filed "hnum" and "lotnum"
I wrote this moudle:

---- snip -----<

Set db = CurrentDb
Set t = db.OpenRecordset(Table1, DB_OPEN_TABLE)
Set r = db.OpenRecordset(Table2, DB_OPEN_TABLE)
If t.RecordCount = r.RecordCount Then
Msg = "All item exist"
Else
Msg = "Some item not exist"
While Not t.EOF
Criteria = "lotnum='" & t!batch & "'"
'' and [lotnum] = '" & t!batch & "'"
r.FindFirst Criteria
If Not r.NoMatch Then
t.Delete
End If
t.NextRecordset
Wend
End If

"Table1" create from import excel file, "table 2" create
by maketable query - there is no index on the table - is
it a problem??

I received an error at line "r.FindFirst Criteria"
error no' 3252: "the action is not supported for this kind
of object(translate from hebrew-maybe not hte exact word)

Liora,

the FindFirst method cannot be used with table type recordsets. Either
you have to use the Seek method (see help item), or open the
recordsets as Dynasets:

Set t = db.OpenRecordset("Table1", dbOpenDynaset)

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de