Access 2002 / 2003 Function Compatability

[Bod]

Hi Folks,
I've produced an Access application that reads the users login name from their environmental settings and then acquires their full name and department from the "Startup" table, to display on their startup screen (Code shown below).

While this works satisfactorily in Access 2002 on any windows platform, it falls down on Access 2003.

When debugging I get [Run-Time Error "2001" You cancelled previous operation], and have no idea why.

The line Marked "***" is where the debugger pulls up the error (Admin Edit - Line #13).

Any pointers would be greatfully recieved.

Thanks
Phil
--------------------------------------------------

Public Sub Form_Load()
 
Dim RSstr As String
Dim Uname As String
Dim Udept As String
Dim Utitle As String
Dim N_Name As String
Dim Slevel As Integer
 
N_Name = Environ("Username")
 
 
** If IsNull(DLookup("[Users_Name]", "Startup", "[Network_Name] = N_name")) Then
    Uname = "Operator"
    Udept = "Unknown"
    Utitle = "Unknown"
    Slevel = 5
Else
    RSstr = "SELECT * FROM Startup Where [Network_Name] = " & "'" & N_Name & "'"
    Me.RecordSource = RSstr
    Uname = [Users_Name]
    Udept = [Dept]
    Utitle = [Job_Title]
    Slevel = [Sec_Level]
    NTstr = Uname & " - " & Utitle
 
End If
 
 
End Sub

[PianoMan64]

Hi Folks,
I've produced an Access application that reads the users login name from their environmental settings and then acquires their full name and department from the "Startup" table, to display on their startup screen (Code shown below).

While this works satisfactorily in Access 2002 on any windows platform, it falls down on Access 2003.

When debugging I get [Run-Time Error "2001" You cancelled previous operation], and have no idea why.

The line Marked "***" is where the debugger pulls up the error.

Any pointers would be greatfully recieved.

Thanks
Phil
--------------------------------------------------

Public Sub Form_Load()
 
Dim RSstr As String
Dim Uname As String
Dim Udept As String
Dim Utitle As String
Dim N_Name As String
Dim Slevel As Integer
 
N_Name = Environ("Username")
 
 
** If IsNull(DLookup("[Users_Name]", "Startup", "[Network_Name] = N_name")) Then
Uname = "Operator"
Udept = "Unknown"
Utitle = "Unknown"
Slevel = 5
Else
RSstr = "SELECT * FROM Startup Where [Network_Name] = '" &  N_Name & "'"
Me.RecordSource = RSstr
Uname = [Users_Name]
Udept = [Dept]
Utitle = [Job_Title]
Slevel = [Sec_Level]
NTstr = Uname & " - " & Utitle
 
End If
 
 
End Sub
 

The reason is because the N_Name variable is not outside the Critieria string of your DLOOKUP function. If it returns a null, then the procedure is cancelled. that is why you're getting the Run-Time Error 2001. You can either select the second half of your if statement, and get rid of the Dlookup, or just simple use the .NoMatch option available is DAO to see if you've found the record or not and test on that.

If you change the dlookup line of code to say the following:

 
        DLookup("[Users_Name]", "Startup", "[Network_Name] ='" & N_Name & "'))
 
 

Public Form_Load()
       Dim RSstr As String
       Dim Uname As String
       Dim Udept As String
       Dim Utitle As String
       Dim N_Name As String
       Dim Slevel As Integer
       Dim MyDB as DAO.Database
       Dim MyRS as DAO.Recordset
 
 
       N_Name = Environ("UserName")
 
       Set Mydb = CurrentDB()
       Set MyRs = MyDB.OpenRecordSet("SELECT * FROM Startup WHERE Nework_Name = '" & n_Name & "'",dbOpenSnapshot)
 
        With MyRS
                If Not .NoMatch Then
                         Set me.RecordSource = MyRS
                         Uname = [Users_Name]
                         Udept = [Dept]
                         Utitle = [Job_Title]
                         Slevel = [Sec_Level]
                         NTstr = Uname & " - " & Utitle
                Else
                         Uname = "Operator"
                         Udept = "Unknown"
                         Utitle = "Unknown"
                         Slevel = 5
                 End if
       End With
   MyRS.Close
   MyDB.Close
End Sub
 

[NeoPa]

If you look at your line #13...

If IsNull(DLookup("[Users_Name]", "Startup", "[Network_Name] = N_name")) Then

... you will see the string "[Network_Name] = N_name" is used as the third parameter. If you use ...

Debug.Print "[Network_Name] = N_name"

... you will see the results are exactly as you would expect ([Network_Name] = N_name). What you intend however, is something of the form of ...
[Network_Name] = 'JonesR'
To get this you need to formulate the string from the data in your variable BEFORE passing it across to the DLookup() function.

If IsNull(DLookup("[Users_Name]", "Startup", "[Network_Name] = '" & N_name & "')) Then

Essentially you want to search the table for whatever's in your variable N_name - rather than always to search for the string N_name (in fact, as it's not delimited, it wouldn't even recognise it as a string, but some spurious reference instead).

[Bod]

Thank you for your help folks

Phil

[NeoPa]

No problems Phil.
I hope it all helped :)