Dlookup does not work in access 2003 - worked in older version 1

[keyword]

This code has worked for years in Access 97 and Access 2002. When I run it with Access 2003 and get "Run-time error '2001': You canceled the previous operation."

The dlookup line of code is highlighted in the debugger.

All references are in the same order the only difference being Microsoft Access 11.0 Object Library vs. Microsoft Access 10.0 Object Library.


Does anyone have any suggestions?

Private Sub ImportCIC_Click()
Dim MatchFoundSwitch As String
Dim DuplicateSSNandEINSwitch As String
Dim x As String
Dim y As String
Dim z As String
Dim fldConsultant_DataSSN As String
Dim tempSSN As String
Dim InitAgencyString As String
Dim myfile

InitAgencyString = "ESC"

'   Check to make sure that file is available
myfile = Dir("c:\sc_wotc_import.txt")
If myfile = "" Then
     MsgBox "Program can not find C:\sc_wotc_import.txt -- Import canceled!!"
     Exit Sub
End If
 
'   import new data
    DoCmd.TransferText acImportFixed, "Consultant_Data_Import_Specification", "tblConsultant_Data", "c:\sc_wotc_import.txt", False, ""
    

    Set dbTrackDB = DBEngine.Workspaces(0).Databases(0)
    Set rstblConsultant_Data = dbTrackDB.OpenRecordset("tblConsultant_Data", dbOpenTable)
    rstblConsultant_Data.MoveFirst
    
DoCmd.SetWarnings False

    
Do Until rstblConsultant_Data.EOF

         

 '   determine if there is already an Employee record with same  transaction ssn and transactionEIN
If Not IsNull(rstblConsultant_Data!SSN) Then
[COLOR=red]      y = Nz(DLookup("[ssn]", "Employees", " [SSN] = rstblConsultant_Data!ssn and [EIN] =  rstblConsultant_Data!EINFirst2 & rstblConsultant_Data!EINLast7 "))[/color]
        If y <> " " Then
 '          MsgBox "Employee table match found on SSN and EIN"
             With rstblConsultant_Data
                   .Edit
                   !notes = " Trans SSN and EIN already on Employee table"
                   .Update
             End With
.
.
.
.
.

Help !!

 

[mrsbean]

At last ... a question that I can answer :0)

In order to use Nz, I think you need to specify what should replace Null. If you want it to use 0 instead of Null, then specify that value.

y = Nz(
DLookup("[ssn]", "Employees", " [SSN] = rstblConsultant_Data!ssn and [EIN] = rstblConsultant_Data!EINFirst2 & rstblConsultant_Data!EINLast7 "), 0)

MrsBean

 

[PHV]

This code has worked for years in Access 97 and Access 2002
Really ?
What about something like this ?
[tt]y = Nz(DLookup("SSN", "Employees", "SSN='" & rstblConsultant_Data!ssn & "' AND EIN='" & rstblConsultant_Data!EINFirst2 & rstblConsultant_Data!EINLast7 & "'"), " ")[/tt]

For each field defined as numeric, get rid of the corresponding single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[keyword]

I get the same error. I put the Nz() in when I was researching the problem at tek-tips because it seemed to be everybody's favorite solution for dlookup problems. Unfortunatly, I got the syntax wrong.

It is weird that with or without the invalid syntax, I get the same error. I don't see how the preceeding line could fail so

I am still IN THE DARK.

corrected code

      y = Nz(DLookup("[ssn]", "Employees", " [SSN] = rstblConsultant_Data!ssn and [EIN] =  rstblConsultant_Data!EINFirst2 & rstblConsultant_Data!EINLast7 "), " ")

 

[PHV]

Seems you didn't read carefully my suggestion ...

 

[abaldwin]

I think you removed the single quotes but did not maintain the concantenation of the recordset fields.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[keyword]

Thanks all for the great help, it finally works.

The question of why remains. I have 2 computer on my desk, one with XP and access 2003 the other with Windows NT and access 2002. That code works on the old version of access and does not work on the newer version.

I also had problems with this code which worked in the old version but had to be modified to run on access 2003.

DoCmd.RunSQL "INSERT INTO [Employee Info] ( SSN, FirstName, MI, LastName, Addr1, Addr2, City, ST, Zip,  AppDOB)" _
& "SELECT rstblConsultant_Data!SSN , rstblConsultant_Data!FirstName, rstblConsultant_Data!MiddleInitial, rstblConsultant_Data!LastName, rstblConsultant_Data!EmpAddr1, " _
& "rstblConsultant_Data!EmpAddr2, rstblConsultant_Data!EmpCity, rstblConsultant_Data!EmpState, rstblConsultant_Data!EmpZip1,  rstblConsultant_Data!AppDOB; "

 

[abaldwin]

I think you know what the core problem is (multiple and not necesarily exactly compatible version of Access in your net). The real problem here is for you to decide what is going to be used in your environment and to develope on the same version.

Otherwise you are going to have to insert code to check for the version an construct different statments based on the version in use by the end user. BIGG TIME problems and time consuming. Simply standardize on a version an move on.


Andy Baldwin

"Testing is the most overlooked programming language on the books!