Hi,
What you are not doing: you are not trying to find a match among all the
records, only for the "actual" record of both recordsets.
Assume the records are:
rst1.ID, rst2.ID
1, 1
2, 3
3, 2
you won't find rst1.ID =2 in rst2.ID since, by position, you haven't move
rstinfo2 (still point at the first record) and even if you would have make a
move next on rstinfo2, it won't match, since 2 <> 3 (hypothetical example).
To be safe, make a query like:
SELECT DISTINCT Employees.ID
FROM Employees LEFT JOIN newtbl
ON Employees.ID = newtbl.ID
WHERE newtbl.ID IS Null
that recordset returns all the Employees.ID not in newtbl.ID
You can then APPEND those records to newtbl, in one SQL statement:
INSERT INTO newtbl ( EmployeeID )
SELECT Employees.EmployeeID
FROM Employees LEFT JOIN newtbl ON Employees.EmployeeID = newtbl.EmployeeID
WHERE newtbl.EmployeeID Is Null;
just: CurrentDb().Execute ".......that SQL statement.... "
No loop, and almost no code!
Hoping it may help,
Vanderghast, Access MVP.
Quote:> im trying to check if a value in my first recordset exits in my second
> recordset. if not add a new record with that value.
> what im i doing wrong? please help
> Sub valueexist()
> Dim db As Database
> Dim rsinfo As Recordset
> Dim rsinfo2 As Recordset
> Set db = CurrentDb()
> Set rsinfo = db.OpenRecordset("employees")
> Set rsinfo2 = db.OpenRecordset("newtbl")
> Do Until rsinfo.EOF
> If rsinfo!employeeid = rsinfo2!employeeid Then
> MsgBox "this matchs"
> Else
> rsinfo2.AddNew
> rsinfo!employeeid = rsinfo2!employeeid
> rsinfo2.Update
> rsinfo.MoveNext
> End If
> Loop
> End Sub
by