[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

Access - VB programming of DAO code routine

Access 2003.  DAo code.  Can someone spot the error for me in the following code.  The username is typed on the Form's field named txtUsername.  Then I am trying to search the Login table to see if the name exists.  If Yes, then doi something, if NO, then Exit the Sub and return focus to the txtusername field for the User to re-enter.
thanks.

    If IsNull(Me.txtUsername) Then
        MsgBox "You cannot leave the username blank, please re-enter"
        Me.txtUsername.SetFocus
        Exit Sub
    Else
        strUsername = Me.txtUsername
        strSQL = "SELECT tblLogin.Username FROM tblLogin WHERE ((tblLogin.Username = strUsername));"

        strUsername = Me.txtUsername
        MsgBox strUsername
       
       
        Set db = CurrentDb
        Set RS = db.OpenRecordset("tblLogin")
        RS.Index = "Primary Key"
        RS.Seek "=", Me.txtUsername
        If RS.RecordCount = 1 Then
            MsgBox "Fond'"
        Else
            MsgBox "not found"
        End If
'I tried another method as the above did not work
        If RS.NoMatch Then
          MsgBox "not found"
        Else
            MsgBox "error"
        End If
        RS.Close
        db.Close
' the under is another method that i tried.
        'RS.MoveFirst
        'RS.MoveLast
        If RS.RecordCount = 1 Then
            MsgBox "Found"
        Else
            MsgBox "Not found"
        End If
    End If
0
jegajothy
Asked:
jegajothy
8 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
what exactly is or is not happening ?

mx
0
 
Rey Obrero (Capricorn1)Commented:
you can use dcount or dlookup

   If IsNull(Me.txtUsername) Then
        MsgBox "You cannot leave the username blank, please re-enter"
        Me.txtUsername.SetFocus
        Exit Sub
    Else

        if  not IsNull(dlookup("Username","tblLogin","Username='" & Me.txtUsername &"'")) then
            msgbox "Found"
            else
            msgbox "Not Found"
        end if
 end if




0
 
Javin007Commented:
Erm.  I only use ADODB, but this still doesn't look right to me:

strSQL = "SELECT tblLogin.Username FROM tblLogin WHERE ((tblLogin.Username = strUsername));"

        strUsername = Me.txtUsername
        MsgBox strUsername
       
       
        Set db = CurrentDb
        Set RS = db.OpenRecordset("tblLogin")
        RS.Index = "Primary Key"
        RS.Seek "=", Me.txtUsername
        If RS.RecordCount = 1 Then

At what point are you passing strSQL to open the recordset?  Is this some kinda funky VBA, or actual VB?  I see you setting up a SQL string (which I also find weird because you reference ((tblLogin.Username = strUsername)) which makes me think you're using VBA, but if that were the case too, wouldn't you still need at least quotes around the text that tblLogin.Username is supposed to equal?)

I probably can't be of much help being an ADODB VB6 programmer, but I just don't see where strSQL is being put into the recordset.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
jkaiosCommented:
Try this:

    If IsNull(Me.txtUsername) Then
        MsgBox "You cannot leave the username blank, please re-enter"
        Me.txtUsername.SetFocus
        Exit Sub
    Else
        strUsername = Me.txtUsername
        strSQL = "SELECT Username FROM tblLogin WHERE (Username = '" & strUsername & "')"
       
        Set db = CurrentDb
        Set RS = db.OpenRecordset(strSQL)

        If RS.BOF And RS.EOF Then
            MsgBox "Username " & strUsername & " not found!"
        Else
            MsgBox "Found"
        End If
    End If
0
 
Rey Obrero (Capricorn1)Commented:
why use a recordset where  a simple dlookup will do the job
0
 
jkaiosCommented:
The good thing about using recordset is that it works both in VBA and VB.  The "dlookup" function will not work in VB if the code were to be ported to VB.
0
 
Rey Obrero (Capricorn1)Commented:
Access is using VBA. Is this Q posted too in VB?
0
 
Arthur_WoodCommented:
Also, all the domain functions (dLookup, dCount, etc) are VERY, VERY slow.

If you want any kind of performance, using a recordset with a restricted SQL statement (using a Where clause, and a proper index), will always outperform the domain functions.

AW
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now