needing IF help

[John]

I am trying to limit who can sign on from an Employees table where
their username and status is kept. Below is what I have so far. The
SELECT statement returns the information in sql server 2005 as
expected. The gUsername also provides the correct info.

Dim secure_id As Variant
secure_id = "SELECT Status, Username FROM Employees WHERE Username =
'" & gUsername & "' AND Status <> 'Former'"

If the info doesn't match who signed in then the application will
close. What I am struggling with is putting the information into an
IF...Then...Else....End If statement and ask for someone to get me
started in the right direction. I am draw a blank. It must be too
many hours and not enough sleep or desperate need of a vacation.

Thanks...John

 

[John W. Vinson]

I am trying to limit who can sign on from an Employees table where
their username and status is kept. Below is what I have so far. The
SELECT statement returns the information in sql server 2005 as
expected. The gUsername also provides the correct info.

Dim secure_id As Variant
secure_id = "SELECT Status, Username FROM Employees WHERE Username =
'" & gUsername & "' AND Status <> 'Former'"

If the info doesn't match who signed in then the application will
close. What I am struggling with is putting the information into an
IF...Then...Else....End If statement and ask for someone to get me
started in the right direction. I am draw a blank. It must be too
many hours and not enough sleep or desperate need of a vacation.

Thanks...John

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim secure_id As Variant
Set db = CurrentDb
secure_id = "SELECT Status, Username FROM Employees WHERE Username =
'" & gUsername & "' AND Status <> 'Former'"
Set rs = db.OpenRecordset(secure_id)
If rs.RecordCount > 0 Then
' all is well, proceed with a valid ID
Else
' smite the wretched offender
End If

Or, more simply,

If IsNull(DLookUp("Username", "Employees", _
"Username ='" & gUsername & "' AND Status <> 'Former'") Then
<ID is not valid>
Else
<ID is valid>
End If


--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim secure_id As Variant
Set db = CurrentDb
 secure_id = "SELECT Status, Username FROM Employees WHERE Username =
'" & gUsername & "' AND Status <> 'Former'"
Set rs = db.OpenRecordset(secure_id)
If rs.RecordCount > 0 Then
  ' all is well, proceed with a valid ID
Else
  '  smite the wretched offender
End If

Or, more simply,

If IsNull(DLookUp("Username", "Employees", _
  "Username ='" & gUsername & "' AND Status <> 'Former'") Then
  <ID is not valid>
Else
  <ID is valid>
End If

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thanks for the response. The 'smite the wretched offender' seemed a
bit strong so I went with the dlookup. Which I wondered about it but
couldn't get that to work either before making the post.
Thanks again
John