SQL select statement issues

[James]

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

 

[Dirk Goldgar]

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 & "'")

 

[James]

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

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)