Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Dec 2004
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Help working through 'No Current Record' error in Access

    In Access 2000, I am trying to create a login form where the user logs in and depending on what rights they have, another form opens. There are two tables I am using (tblGroup and tblUser)...the SQL statement uses an inner join between the tables and searches for the UserName in the User table and the Description in the Group table. If the UserName exists in the user table and the description in the Group table is 'Admin' then frmAddRecord opens...if the UserName exists in the user table and the description is 'User' in the group table, then frmSearch is opened. I want an error to be thrown if txtUserName is left blank or if the name entered is not in tblUser. With the code I have, I am receiving a 'Run-time error '3021': No Current Record.' error on this line - strUserName = rs!UserName. Does anyone know what I need to do in order for this to work properly? Any help would be greatly appreciated. Here's my code for the command button that the user hits after they enter their username:

    Code:
    Private Sub cmdLogin_Click()
       Dim db As Database
       'Dim rs As Recordset
       'Dim rs As Recordset
       Dim rs As Recordset
       
        'Dim strCriteria As String
       Dim strSQL As String
       Dim strUserName As String
       Dim strDescription As String
       Dim stDocName As String
       Dim stDocName2 As String
    
        'Set rst = CurrentDb.OpenRecordset("tblUsers", dbOpenDynaset)
        'Me!txtUserName = UCase(Me!txtUserName)
        'criteria = "[UserName] = '" & Me!txtUserName & "'"
       Set db = CurrentDb
        
       strSQL = "Select tblGroup.Description, tblUser.UserName From tblGroup, tblUser Where tblGroup.GroupID = tblUser.GroupID and UserName = '" & txtUserName.Value & "'"
       
       'MsgBox strSQL
       'Debug.Print strSQL
         
       Set rs = db.OpenRecordset(strSQL)
        
       strUserName = rs!UserName
       strDescription = rs!Description
       stDocName = "frmAddRequest"
       stDocName2 = "frmSearch"
         
          
         If UCase(txtUserName.Value) = strUserName And strDescription = "Admin" Then 'Check if user is Admin
           DoCmd.OpenForm stDocName, acNormal
          ElseIf UCase(txtUserName.Value) = strUserName And strDescription = "User" Then 'Check if user is User
             DoCmd.OpenForm stDocName2, acNormal
            ElseIf IsNull(Me.txtUserName) Then 'Not a Valid User
              MsgBox "Please enter a user name!"
              Me!txtUserName.SetFocus
          Else
           MsgBox "The user name you entered is not valid!"
           Me!txtUserName.SetFocus
          End If
          
       db.Close
       Set db = Nothing
       Set rs = Nothing
    
    End Sub
    Thanks,
    Shannon

  • #2
    New to the CF scene
    Join Date
    Jun 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No Current Record Problem

    hi i have the same promblem when i run data through this software it comes up with No current Record Error 3021. can any one help me with this.

    Regards
    Matt

  • #3
    New to the CF scene
    Join Date
    Jun 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Verify rs

    Before doing anything with the recordset, you should verify if it is empty. If is not empty then check user status; else, do nothing or add new user.
    Code:
    Private Sub cmdLogin_Click()
       Dim db As Database
       'Dim rs As Recordset
       'Dim rs As Recordset
       Dim rs As Recordset
       
        'Dim strCriteria As String
       Dim strSQL As String
       Dim strUserName As String
       Dim strDescription As String
       Dim stDocName As String
       Dim stDocName2 As String
    
        'Set rst = CurrentDb.OpenRecordset("tblUsers", dbOpenDynaset)
        'Me!txtUserName = UCase(Me!txtUserName)
        'criteria = "[UserName] = '" & Me!txtUserName & "'"
       Set db = CurrentDb
        
       strSQL = "Select tblGroup.Description, tblUser.UserName From tblGroup, tblUser Where tblGroup.GroupID = tblUser.GroupID and UserName = '" & txtUserName.Value & "'"
       
       'MsgBox strSQL
       'Debug.Print strSQL
         
       Set rs = db.OpenRecordset(strSQL)
       
       If Not ((rs.BOF = True) and (rs.EOF = True)) then
     
       strUserName = rs!UserName
       strDescription = rs!Description
       stDocName = "frmAddRequest"
       stDocName2 = "frmSearch"
         
          
         If UCase(txtUserName.Value) = strUserName And strDescription = "Admin" Then 'Check if user is Admin
           DoCmd.OpenForm stDocName, acNormal
          ElseIf UCase(txtUserName.Value) = strUserName And strDescription = "User" Then 'Check if user is User
             DoCmd.OpenForm stDocName2, acNormal
            ElseIf IsNull(Me.txtUserName) Then 'Not a Valid User
              MsgBox "Please enter a user name!"
              Me!txtUserName.SetFocus
          Else
           MsgBox "The user name you entered is not valid!"
           Me!txtUserName.SetFocus
          End If
          
          Else
              'Exit Sub     'Do Nothing or
              rs.AddNew
              'Enter field info for each required field
              rs.Fields("UserName").Value = TheUserName
              rs.Update
          End If
       db.Close
       Set db = Nothing
       Set rs = Nothing
    
    End Sub
    You can also try trap the error.
    Code:
    Private Sub cmdLogin_Click()
    On Error GoTo HandleMyError
       Dim db As Database
       'Dim rs As Recordset
       'Dim rs As Recordset
       Dim rs As Recordset
       
        'Dim strCriteria As String
       Dim strSQL As String
       Dim strUserName As String
       Dim strDescription As String
       Dim stDocName As String
       Dim stDocName2 As String
    
        'Set rst = CurrentDb.OpenRecordset("tblUsers", dbOpenDynaset)
        'Me!txtUserName = UCase(Me!txtUserName)
        'criteria = "[UserName] = '" & Me!txtUserName & "'"
       Set db = CurrentDb
        
       strSQL = "Select tblGroup.Description, tblUser.UserName From tblGroup, tblUser Where tblGroup.GroupID = tblUser.GroupID and UserName = '" & txtUserName.Value & "'"
       
       'MsgBox strSQL
       'Debug.Print strSQL
         
       Set rs = db.OpenRecordset(strSQL)
     
       strUserName = rs!UserName
       strDescription = rs!Description
       stDocName = "frmAddRequest"
       stDocName2 = "frmSearch"
         
          
         If UCase(txtUserName.Value) = strUserName And strDescription = "Admin" Then 'Check if user is Admin
           DoCmd.OpenForm stDocName, acNormal
          ElseIf UCase(txtUserName.Value) = strUserName And strDescription = "User" Then 'Check if user is User
             DoCmd.OpenForm stDocName2, acNormal
            ElseIf IsNull(Me.txtUserName) Then 'Not a Valid User
              MsgBox "Please enter a user name!"
              Me!txtUserName.SetFocus
          Else
           MsgBox "The user name you entered is not valid!"
           Me!txtUserName.SetFocus
          End If
         
       db.Close
       Set db = Nothing
       Set rs = Nothing
        
        Exit Sub
    
    HandleMyError:
       If Err.Number = 3021 then
            rs.AddNew
            'Enter field info for each required field
            rs.Fields("UserName").Value = TheUserName
            rs.Update
            Resume
        ElseIf Err.Number = 3022 Then
            MsgBox "The requested change can not be done because this" & Chr(10) & _
           "would create duplecate recorsin the database. Verify" & Chr(13) & _
           "the information and try again.", vbExclamation + vbOKOnly, "Duplicate"
           rs.CancelUpdate
           Exit Sub       'Could be Resume or Resume Next
        Else
           MsgBox "The error is " & Err.Number & " that means " & Chr(13) & _
                Err.Description & ".", vbInformation + vbOKOnly, "Error"
           Exit Sub        'Could be Resume or Resume Next
        End if
    End Sub
    Hope if helps!
    Last edited by Apokalipsys; 06-02-2009 at 07:43 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •