Access 2000 is this possible?

[SniperUK]

What I'm building is a order system as a project for college, using Access 2000, since at college the user rights etc features are disabled, I have build another way to have user rights, so I created, Teachers Table

Teachers Table
-Teacher ID e.g. T001
-User Name e.g. Luqman
-Password e.g. 121212
-First Name e.g. Luqman
-Last Name e.g. Khan
-User Level e.g. Admin Or Staff

so 2 different user levels,

I created a form, where they would have to enter in the user name and password, I'm stuck on how to do this,

They enter the user name and password, if correct it checks user level, depedning on user, level a diferent form is loaded, how would I go about doing this?

anyone can help thanks? I can send the database to you if needed!

thanks

[DaveMaxwell]

Quick and dirty would be something like this:

strSQL = "SELECT UserLevel FROM UserTable " & _
         " WHERE UserName = " & Request("username") & _
         "   AND UserPass = " & Request("password")
set rs = myConn.execute

if rs.eof or rs.bof then
   ' not found, go to default page
   NextPage = "default.asp"
else
   Dim UserLevel
   UserLevel = rs("UserLevel")
   select case userlevel
          case 1             ' Normal user
               NextPage = "normal.asp"
          case 2             ' Admin
               NextPage = "admin.asp"
          case else          ' If for some reason, not set..
               NextPage = "default.asp"
   end select
end if
rs.close    : set rs = nothing
' Actually go to the next page....
response.redirect(NextPage)

Now this is minimum functionality but should get you close to what you want. What you SHOULD do is make the UserLevel SQL Statement code into a function, and put that function in an include so you can add it to every page, and also do a security check on the top of the page and ensure the user is allowed to be there. You'd also want to have the username/password cookied and access them on each page to keep it secure. So in other words....

You would have an include file (inc_security.asp for lack of a better name) and have the following code in it:

Function CheckSecurity(strUserName, strPassword)
   strSQL = "SELECT UserLevel FROM UserTable " & _
            " WHERE UserName = " & strUserName & _
            "   AND UserPass = " & strPassword
   set rs = myConn.execute

   if rs.eof or rs.bof then
      ' not found...
      CheckSecurity = 0
   else
      Dim strUserLevel   : strUserLevel = rs("UserLevel")
      select case strUserLevel
             case 1, 2             ' Or whatever the appropriate values become
                  CheckSecurity = strUserLevel
             case else          ' If for some reason, not set..
                  CheckSecurity = 0
      end select
   end if
   rs.close    : set rs = nothing
Exit Function

Then in your first page, you would replace the code I had above with this:

<!--#INCLUDE FILE="inc_security.asp"-->
<%
Dim strUserName, strPassword, strUserLevel, strNextPage

strUserName = Replace(Request("UserName"), "'","''") ' Replace helps to prevents SQL injection attacks...
strPassword = Replace(Request("Password"), "'","''") 

strUserLevel = CheckSecurity(strUserName, strPassword)
if strUserLevel = 1 then
   strNextPage = "normal.asp"
elseif userUserLevel = 2 then
   strNextPage = "admin.asp"
else
   strNextPage = "default.asp"
end if
Response.Redirect(strNextPage)

Then, to be extra sure, you'd add this kinda code to the top of admin.asp:

<!--#INCLUDE FILE="inc_security.asp"-->
<%
Dim strUserName, strPassword, strUserLevel

strUserName = Replace(Request("UserName"), "'","''") ' Replace helps to prevents SQL injection attacks...
strPassword = Replace(Request("Password"), "'","''") 

if CheckSecurity(strUserName, strPassword) <> 2 then
   Response.Redirect(strNextPage)
end if

[SniperUK]

thanks DaveMaxwell, its explains how the things should be done, can this be done in Acess 2000 only, same code?

I'm only using access 2000 and the forms you can make in it or using visual basics 6.

not using .Asp, or any thing else thanks anyway

[DaveMaxwell]

Oh, sorry. Didn't see the access only part of it

You can do it through the scripting language on the forms, but it's been quite a while since I've done it there. The code should be SIMILAR, but there will be code changes from what I provided you in the quick and dirty example (which should be all you need.) I know the response.redirect is more like an openform or something such as that. You'll need to read the access 2000 help for exact syntax.

[platinum]

hrm, access isn't the most secure platform to build entirly on, but anyway, good enough I guess

As for the form, just get it to do a query of the database, and if the username and password match, then do a second query which will check which level they should access.

Do you need exact code? It's fairly straight forward in access anyway

Basically if I remember correctly tables are refered to as [blah blah].element and that kinda thing, you'll actually be better off creating a query (SELECT * FROM users WHERE username='[form].username' AND password='[form].password')

and then check of the result is NULL (or ""?) and from there go on with opening the form (there's an option in there somewhere)