Please Help ... Setting Security

[tinatt]

OK ... this is the scoop. I am a beginner Access programmer and I have no idea whatsoever how to do what I need to do. I have never learned in school either, so I need you to help me and be as simple as possible.

Here's my problem ... I have created Table1 with 'User ID', 'Password' and 'Security' columns and a 'Login' form that comes onto the screen as soon as the user opens my Access database. The way I want things to work is that when the user types in their 'User ID' and 'Password', if their login information corresponds with the information I have in my Table1 table then it will open a certain 'Main Form'; however, if their login information is not in my Table1, then it will open a different 'Main Form'.

How do I do this? My supervisor wants me to set this up and have it done by today, so I really need anyones help who has any idea how to do this. I'm really scared that I'm not going to be able to do this. Please help.

Thanks,
Tina.

 

[Muttley]

Ok here goes:

Create your form with the following text boxes:

txtUserName
txtPassword

Create a button and then put the following code on the 'On Click' event;

Dim myQry as String
Dim myRs as Recordset

myQry = "SELECT username, password FROM tblSecurity WHERE username = me.txtUserName AND password = txtPassword"

Set myRs = Nothing
Set myRs = CurrentDb.OpenRecordset(myQry)

If not myRs.BOF and not myRs.EOF then
docmd.openform "frmFormA",acNormal
Else
docmd.openform "frmFormB",acNormal

Set myRs = Nothing

There's a bunch of ways in which you could do this but the above should work, let me know if you have problems with the syntax.

HTH

 

[Muttley]

Oops, forgot the 'End If' statement - you need to put this after the latter openform statement. Soz!

 

[tinatt]

Thanks so very much for your help Muttley !! I just put the 'End If' statement after the 'DoCmd.OpenForm "frmFormB',acNormal' statement and now when I try to run the code, I get an 'Compile error' that says 'Else without If' which highlights the 'Else' above the 'DoCmd.OpenForm "frmFormB',acNormal' line. I then changed the line to 'Else If' and it's now giving me another 'Compile Error' that syas 'Expected: expression'. What should I do here now or do you think I did something wrong.

Thanks again so very much for your help. I really appreciate it.

Tina.

 

[tinatt]

Actually Muttley ... here's the code I'm using. maybe this will be easier for you to actually see it.


Private Sub Command5_Click()

Dim myQry As String
Dim myRs As Recordset

myQry = "SELECT User, Password FROM tblUser WHERE User = me.txtUserName AND Password = txtPassword"

Set myRs = Nothing
Set myRs = CurrentDb.OpenRecordset(myQry)

If Not myRs.BOF And Not myRs.EOF Then DoCmd.OpenForm "frmMain_Menu1", acNormal

Else If

DoCmd.OpenForm "frmMain_Menu2", acNormal

End If

Set myRs = Nothing

End Sub


Thanks again,
Tina

 

[Muttley]

Try putting a carriage return between

If Not myRs.BOF And Not myRs.EOF Then

and

DoCmd.OpenForm "frmMain_Menu1", acNormal

Access treats single line 'if' statements differently than block 'if' statements. You should then be able to use 'Else' rather than 'Elseif'

Let me know if any probs.

Cheers

Muttley

 

[tinatt]

Muttley,

Thanks for all your help and I'm sorry this is coming to me a bit more clearly. Anyways ... I've modified the code as is below and now I'm getting a 'Run-time error '3061':' that says 'Too few parameters. Expected 2.' and it highlights the 'Set myRs = CurrentDb.OpenRecordset(myQry)' line.


Tina.




Private Sub Command5_Click()

Dim myQry As String
Dim myRs As Recordset

myQry = "SELECT User, Password FROM tblUser WHERE User = me.txtUserName AND Password = txtPassword"

Set myRs = Nothing

Set myRs = CurrentDb.OpenRecordset(myQry)

If Not myRs.BOF And Not myRs.EOF Then

DoCmd.OpenForm "frmMain_Menu1", acNormal

Else

DoCmd.OpenForm "frmMain_Menu2", acNormal

End If

Set myRs = Nothing

End Sub

 

[tinatt]

Muttley,

I have a thought why this might not be working. I hav set my 'Record Source' for the 'Login' form to tblUser, but the 'Control Source' for each text box (txtUserName' and 'txtPassword' is currently unbound. Should I set the 'Control Sources' to the table respectively.

Tina.

 

[tinatt]

I still really really need help with this. Is there anyone else out there that could give me a hand with this problem?

Tina.

 

[Muttley]

Sorry Tina, got diverted - try modifying your SQL to say

myQry = "SELECT User, Password FROM tblUser WHERE User = me.txtUserName AND Password = me.txtPassword"

You were missing the 'me' in your password string

 

[Muttley]

I've had the same error sometimes...an alternative approach that may fix it is to create the query using the normal query editor (paste the SQL from earlier into the SQL view), try running the query to see if it runs - that way you can check your SQL. If it works, save your query e.g. qryCheck

You can then use the DCount function from code a la:

If DCount ("User", "qryCheck&quot > 0 then
Blah
Else
Blah Blah
End if

If this doesn't work then someone else will have to help!