Recordset Problem

[Darrell Wootton]

Hi,

I have been trying for the last two days to get this piece of code working:

However I am having no luck.

I need to use the names in table "one" With the first name and go and see if exists in table "two".

If there is a match(s) It then ticks a box for every match found.

Once it has finished searching for the first name in table one, I then need it to move onto the second name in table one and search table two again etc etc.

Public Sub GetLName()
Dim dbMonthlyReports2000 As DAO.Database
Dim rst As DAO.Recordset
Dim strCritera, strCritera2 As String
Dim rst, rst1
Set dbMonthlyReports2000 = CurrentDb
Set rst = dbMonthlyReports2000.OpenRecordset("CB_March_2004", dbOpenDynaset)
Set rst1 = dbMonthlyReports2000.OpenRecordset("CBSearch", dbOpenDynaset)

Do Until rst.EOF
strCritera = rst!Surname
rst1.FindFirst rst1!Customer = strCritera
Do Until rst1.NoMatch
If rst1!customer = strCritera Then
rst1.Edit
rst1!Buy = True
rst1.Update
Else
End If
rst1.FindNext rst1.customer = strCritera
Loop
rst.MoveNext
Loop
rst.Close
rst1.Close
dbMonthlyReports2000.Close

End Sub

Thanks in advance

Darrell......

 

[Mile-O]

Public Sub GetLName()
Dim dbMonthlyReports2000 As DAO.Database
Dim rst As DAO.Recordset
Dim strCritera, strCritera2 As String
Dim rst, rst1
Set dbMonthlyReports2000 = CurrentDb
Set rst = dbMonthlyReports2000.OpenRecordset("CB_March_2004", dbOpenDynaset)
Set rst1 = dbMonthlyReports2000.OpenRecordset("CBSearch", dbOpenDynaset)

Do Until rst.EOF
    strCritera = rst!Surname
       rst1.FindFirst rst1!Customer = strCritera
       Do Until rst1.NoMatch
       If rst1!customer = strCritera Then
                rst1.Edit
                rst1!Buy = True
                rst1.Update
                Else
                End If
    rst1.FindNext rst1.customer = strCritera
    Loop
    rst.MoveNext
    Loop
rst.Close
rst1.Close
dbMonthlyReports2000.Close

End Sub

I'll just go through the whole thing...

Dim strCritera, strCritera2 As String

This is incorrect. You are defining a Variant and then a String
This line should be:

Dim strCritera As String, strCritera2 As String

Dim rst, rst1

You have already defined rst. You can't redefine it as a Variant - it's already a DAO.Recordset object.

Change to:

Dim rst1 As DAO.RecordSet

As for the rest, I see a little inconsistency which I've never understood why people do:

i.e. using early and late binding when working with recordset objects...

They start with rst.EOF, rst.Edit - emphasis on the dot (early binding)

but then, with relation to fields, they revert to late binding (rst!FieldName)

Is something wrong with rs.Fields("FieldName")? It saves Access having to find out what rs!FieldName actually is!


Darrell, the question is, however....


...why would a name in one table appear in a second?

 

[Darrell Wootton]

Hi,

The reason names appear in two tables is:

The records were sent to us by a third party company who want to know if we have had the same customers in our db as they do in theirs.

So i am comparing the records for matches. I have imported their table of customers into our db and I am trying to do a check.

The code is posted that is not quite working.

Thanks

Darrell....

 

[Mile-O]

Why not just use a Find Unmatched Query and then reverse the criteria.


P.S. The reply button on the right of a post is to quote the entire thing. The reply button on the bottom left is to reply without quoteing.