Find Matching Records

[madhouse]

I have been given the task of synchronizing 2 tables in an Access 2000 database. The first table is called TIssues and the second table is called IssLog. Within TIssues there is a field called issueUID (AutoNumber) and in IssLog there is a field called ILIssueNumber (Text). What I'm trying to do is to see if any records in TIssues exist in IssLog by comparing the 2 fields above (issueUID -> ILIssueumber). I created a function (see code below) to do this but keep getting one of the following error messages:

"Run-time error 3077 Syntax error (missing operator) in expression"

or

"Run-time error 3070 The Microsoft Jet database engine does not recognize 'issueUID' as a valid field name or expression"

or

"Run-time error 3070 The Microsoft Jet database engine does not recognize 'SIR210' as a valid field name or expression"

As you'll see from the code below I have tried different different methods for the criteria but none of them work.

The things to be aware of are that issueUID in table TIssues is an AutoNumber whereas ILIssueNumber in IssLog is Text. And just to complicate things a bit further, the values in ILIssueNumber are appended with "SIR" e.g. SIR100, SIR101 etc, etc.

If anybody can help I would be most grateful.

Function Code:Function CompareIssues()

Dim db As DAO.Database
Dim rsTIssues As DAO.Recordset
Dim rsIssLog As DAO.Recordset
Dim strCriteria As String

Set db = CurrentDb
Set rsTIssues = db.OpenRecordset("TIssues", dbOpenDynaset)
Set rsIssLog = db.OpenRecordset("IssLog", dbOpenDynaset)

With rsTIssues
.MoveFirst
Do While Not .EOF
'strCriteria = "[ILIssueNumber] = SIR" & .Fields("issueUID") ' If field is numberic
'strCriteria = "[IssueNumber] = '" & .Fields("issueUID") & "'" ' If field is string
'strCriteria = "[ILIssueNumber] = SIR" & CStr(.Fields("issueUID"))
'strCriteria = "[ILIssue Number] = SIR'" & CStr(.Fields("issueUID")) & "'" ' If field is string
'strCriteria = "[ILIssueNumber] = SIR" & CStr(.Fields("issueUID")) & ""
strCriteria = "CStr([ILIssueNumber]) = SIR" & " & CStr(.Fields(issueUID)) & """
rsIssLog.FindFirst strCriteria
If rsIssLog.NoMatch Then
' Put all you record copying stuff here

rsIssLog.AddNew

rsIssLog![ILIssue Number] = "SIR" & rsTIssues!issueUID
rsIssLog![ILFull Description] = rsTIssues!Description
rsIssLog![ILDate Raised] = rsTIssues!whenRaised
rsIssLog![ILTitle] = rsTIssues!shortDescription
rsIssLog![ILResolution] = rsTIssues!answer
rsIssLog![ILRaised By] = rsTIssues!whoRaised
rsIssLog![ILExternal Ref] = rsTIssues!priority

rsIssLog.Update

End If
.MoveNext
Loop
End With

End Function

 

[simongallop]

Few things which I am not sure but think that they may cause problems. You are using with and then running normal lines of code within the with statement. Don't think you can do that, though am sure that someone else will confirm one way or another. In all of your trials you missed out the one for text search. For numeric you say field = number, for string you say field = 'text' making sure that the string is enclosed by single quotes. Also in your description your say that the field name is ILIssueNumber yet in your code you call it IL Issue Number. Which one is it? Next, you say the table is IssLog, yet in your code you have set the recordset to point to IssLogTemp, and then writting to it if it does not exist. Are you trying to copy one table to another or are you trying to place the data that does not exist in IssLog into a temp table called IssLogTemp? In the code below I am settling for placing the unmatched data into a temp table so there is an extra recordset being declared.

Try the following:

Function CompareIssues()

Dim db As DAO.Database
Dim rsTIssues As DAO.Recordset
Dim rsIssLog As DAO.Recordset
DIM rsIssLogTemp AS DAO.RECORDSET
Dim strCriteria As String
DIM MySearch AS STRING

Set db = CurrentDb
Set rsTIssues = db.OpenRecordset("TIssuesX", dbOpenDynaset)
Set rsIssLog = db.OpenRecordset("IssLog", dbOpenDynaset)
Set rsIssLogTemp = db.OpenRecordset("IssLogTemp", dbOpenDynaset)

rsTIssues.MoveFirst
Do
MySearch = "[ILIssueNumber] = 'SIR" & rsTIssues("issueUID") & "'"
rsIssLog.FindFirst MySearch
If rsIssLog.NoMatch = True then
rsIssLogTemp.AddNew
rsIssLogTemp("IL Issue Number") = "SIR" & rsTIssues("issueUID")
rsIssLogTemp("IL Full Description") = rsTIssues("Description")
rsIssLogTemp("IL Date Raised") = rsTIssues("whenRaised")
rsIssLogTemp("IL Title") = rsTIssues("shortDescription")
rsIssLogTemp("IL Resolution") = rsTIssues("answer")
rsIssLogTemp("IL Raised By") = rsTIssues("whoRaised")
rsIssLogTEmp("IL External Ref") = rsTIssues("priority")
rsIssLogTemp.Update
End If
rsTIssues.movenext
Loop Until rsTIssues.EOF True

End Function


HTH

 

[madhouse]

Apologies with regards to the differing table names etc - I was playing about with copies of the tables and didn't change the code that I posted. I have now changed the code in my post accordingly.

I'll give your suggestions a try and see what happens.

Thanks for your help.