Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL select statement issues

Discussion in 'Information Technology' started by James, Jul 28, 2006.

  1. James

    James
    Expand Collapse
    Guest

    I'm trying to run this code to display the user's password (in an admin
    console) but i can't remember how to clear the currentdb (if that's
    what i need to do) Whatever user i retrieve their password first, that
    password shows up for every person i try to retrieve thereafter. Here's
    my code. Thanks!

    Private Sub cmdFindPassword_Click()

    Dim strSQL As String
    Dim db As Dao.Database
    Dim qd As Dao.QueryDef
    Dim msgBoxText As String
    Dim display As Integer
    Dim test As Variant


    On Error GoTo Error_Handler

    'msgBoxText = "Are you sure you want to delete user: '" & Me!cboUserID
    & "'"
    'If MsgBox(msgBoxText, vbYesNo + vbQuestion, "Delete Warning") = vbYes
    Then
    Set db = CurrentDb


    'define the SQL of the query to delete the record
    strSQL = "SELECT passwd FROM UserIDandPassword WHERE [USERID] =
    '" & Me!cboUserID & "'"

    'create an unnamed new query definition (the "" is the name)
    Set qd = db.CreateQueryDef("", strSQL)

    msgBoxText = "The password for " & Me!cboUserID & " is: " &
    passwd
    display = MsgBox(msgBoxText, vbInformation + vbOKOnly)

    cboUserID = ""


    Me.Refresh

    Exit_Method:
    Exit Sub

    Error_Handler:
    MsgBox Err.Description
    Resume Exit_Method

    End Sub
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "James" <jamesburkett@gmail.com> wrote in message
    news:1153495291.831659.104890@75g2000cwc.googlegroups.com
    > I'm trying to run this code to display the user's password (in an
    > admin console) but i can't remember how to clear the currentdb (if
    > that's what i need to do) Whatever user i retrieve their password
    > first, that password shows up for every person i try to retrieve
    > thereafter. Here's my code. Thanks!
    >
    > Private Sub cmdFindPassword_Click()
    >
    > Dim strSQL As String
    > Dim db As Dao.Database
    > Dim qd As Dao.QueryDef
    > Dim msgBoxText As String
    > Dim display As Integer
    > Dim test As Variant
    >
    >
    > On Error GoTo Error_Handler
    >
    > 'msgBoxText = "Are you sure you want to delete user: '" & Me!cboUserID
    > & "'"
    > 'If MsgBox(msgBoxText, vbYesNo + vbQuestion, "Delete Warning") = vbYes
    > Then
    > Set db = CurrentDb
    >
    >
    > 'define the SQL of the query to delete the record
    > strSQL = "SELECT passwd FROM UserIDandPassword WHERE [USERID]
    > = '" & Me!cboUserID & "'"
    >
    > 'create an unnamed new query definition (the "" is the name)
    > Set qd = db.CreateQueryDef("", strSQL)
    >
    > msgBoxText = "The password for " & Me!cboUserID & " is: " &
    > passwd
    > display = MsgBox(msgBoxText, vbInformation + vbOKOnly)
    >
    > cboUserID = ""
    >
    >
    > Me.Refresh
    >
    > Exit_Method:
    > Exit Sub
    >
    > Error_Handler:
    > MsgBox Err.Description
    > Resume Exit_Method
    >
    > End Sub


    Is that really all your code? I don't see anything there that actually
    executes the query to retrieve the password. I'd expect something like
    this (leaving out non-essential bits):

    '----- start of code -----
    Dim db As DAO.Database
    Dim rs As DAO.QueryDef
    Dim strSQL As String
    Dim strPassword As String

    strSQL = _
    "SELECT passwd FROM UserIDandPassword " & _
    "WHERE [USERID] = '" & Me!cboUserID & "'"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    If rs.EOF Then
    MsgBox "User " & Me!cboUserID & " is not on file."
    Else
    strPassword = rs!passwd
    MsgBox _
    "The password for " & Me!cboUserID & " is: " & _
    strPassword
    End If
    rs.Close

    Me!cboUserID = Null

    '----- end of code -----

    Or you could just use the DLookup function, without bothering with the
    DAO objects at all:

    strPassword = "" & _
    DLookup("passwd", "UserIDandPassword", _
    "[USERID] = '" & Me!cboUserID & "'")

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. James

    James
    Expand Collapse
    Guest

    I knew I was taking that in the wrong direction. The DLookup worked
    perfectly, thanks.

    Dirk Goldgar wrote:
    > "James" <jamesburkett@gmail.com> wrote in message
    > news:1153495291.831659.104890@75g2000cwc.googlegroups.com
    > > I'm trying to run this code to display the user's password (in an
    > > admin console) but i can't remember how to clear the currentdb (if
    > > that's what i need to do) Whatever user i retrieve their password
    > > first, that password shows up for every person i try to retrieve
    > > thereafter. Here's my code. Thanks!
    > >
    > > Private Sub cmdFindPassword_Click()
    > >
    > > Dim strSQL As String
    > > Dim db As Dao.Database
    > > Dim qd As Dao.QueryDef
    > > Dim msgBoxText As String
    > > Dim display As Integer
    > > Dim test As Variant
    > >
    > >
    > > On Error GoTo Error_Handler
    > >
    > > 'msgBoxText = "Are you sure you want to delete user: '" & Me!cboUserID
    > > & "'"
    > > 'If MsgBox(msgBoxText, vbYesNo + vbQuestion, "Delete Warning") = vbYes
    > > Then
    > > Set db = CurrentDb
    > >
    > >
    > > 'define the SQL of the query to delete the record
    > > strSQL = "SELECT passwd FROM UserIDandPassword WHERE [USERID]
    > > = '" & Me!cboUserID & "'"
    > >
    > > 'create an unnamed new query definition (the "" is the name)
    > > Set qd = db.CreateQueryDef("", strSQL)
    > >
    > > msgBoxText = "The password for " & Me!cboUserID & " is: " &
    > > passwd
    > > display = MsgBox(msgBoxText, vbInformation + vbOKOnly)
    > >
    > > cboUserID = ""
    > >
    > >
    > > Me.Refresh
    > >
    > > Exit_Method:
    > > Exit Sub
    > >
    > > Error_Handler:
    > > MsgBox Err.Description
    > > Resume Exit_Method
    > >
    > > End Sub

    >
    > Is that really all your code? I don't see anything there that actually
    > executes the query to retrieve the password. I'd expect something like
    > this (leaving out non-essential bits):
    >
    > '----- start of code -----
    > Dim db As DAO.Database
    > Dim rs As DAO.QueryDef
    > Dim strSQL As String
    > Dim strPassword As String
    >
    > strSQL = _
    > "SELECT passwd FROM UserIDandPassword " & _
    > "WHERE [USERID] = '" & Me!cboUserID & "'"
    >
    > Set db = CurrentDb
    > Set rs = db.OpenRecordset(strSQL)
    > If rs.EOF Then
    > MsgBox "User " & Me!cboUserID & " is not on file."
    > Else
    > strPassword = rs!passwd
    > MsgBox _
    > "The password for " & Me!cboUserID & " is: " & _
    > strPassword
    > End If
    > rs.Close
    >
    > Me!cboUserID = Null
    >
    > '----- end of code -----
    >
    > Or you could just use the DLookup function, without bothering with the
    > DAO objects at all:
    >
    > strPassword = "" & _
    > DLookup("passwd", "UserIDandPassword", _
    > "[USERID] = '" & Me!cboUserID & "'")
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
     

Share This Page