Login/VBA script to login to Access 2000

[cyndithomas via AccessMonster.com]

I new to Visual Basic and am struck on a issue. Have created a Login in
Screen for Remote User to access and input data. Want the Remote to be able
to login & access the Input Form, and Manager to Login to another screen to
view data.

Form 1 is rmFieldReq (Remote Users Form)
Form 2 is frmREquests (Manager Form)

Below is my Code

Private Sub cmdCancel_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT '' AS LoggedInAccess INTO access"
DoCmd.SetWarnings True
DoCmd.Quit acQuitSaveAll
End Sub

Private Sub cmdOK_Click()
DoCmd.SetWarnings False

'Check to see if they are a valid user

If DCount("[user]", "tblUser", "[user] = '" & txtUser & "'") = 0 Then
MsgBox "Get Away"
DoCmd.RunSQL "SELECT '' AS LoggedInAccess INTO access"
Exit Sub
End If
'Check password
Dim tempPass
tempPass = DLookup("[password]", "tblUSer", "[user] = '" & txtUser & "'")
If IsNull(txtPassword) Or txtPassword <tempPass Then
MsgBox "Wrong Password"
DoCmd.RunSQL "SELECT '' AS LoggedInAccess INTO access"
Exit Sub
End If

'Write Field Office Name (access) in access Table
Dim FO
FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")

DoCmd.RunSQL "SELECT '" & FO & "' AS LoggedInAccess INTO access"
'Write Username (access) in access Table
Dim User
User = DLookup("[GROUPID]", "tblUSer", "[user] = '" & txtUser & "'")
If (GroupID) = requests Then
DoCmd.RunSQL "SELECT '" & GROUP & "' AS LoggedInAccess INTO access"
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "Requests", acNormal

Else

FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")
If IsNull(FOID) = FO Then
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmFieldReq"

'Test it
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmFieldReq"

'DoCmd.OpenQuery "qryPOEs"'

DoCmd.SetWarnings True
End Sub
thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200703/1

[storrboy]

So what are you stuck on? You didn't actually ask anything or point
out a problem.

[cyndithomas via AccessMonster.com]

storrboy wrote:

>So what are you stuck on? You didn't actually ask anything or point
out a problem.

Am stuck on the fact that when you type in Username and Password. Only one
form is displayed which is DoCmd.OpenForm "frmFieldReq"

EXAMPLE: logged in as a Manager the form "frmFieldReq" is loaded. AS a
Manager the form "Requests" should display.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200703/1

[cyndithomas via AccessMonster.com]

storrboy wrote:

>So what are you stuck on? You didn't actually ask anything or point
out a problem.

My Problem is in this coding: Not displaying the frmREQUESTS when Managers
loggin, it displayes the "frmFieldReq.

'Write Username (access) in access Table
Dim User
User = DLookup("[GROUPID]", "tblUSer", "[user] = '" & txtUser & "'")
If (GroupID) = requests Then
DoCmd.RunSQL "SELECT '" & GROUP & "' AS LoggedInAccess INTO access"
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "Requests", acNormal

Else

FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")
If IsNull(FOID) = FO Then
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmFieldReq"

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200703/1

[rwr]

cyndithomas via AccessMonster.com wrote:

storrboy wrote:

>So what are you stuck on? You didn't actually ask anything or point
out a problem.

My Problem is in this coding: Not displaying the frmREQUESTS when Managers
loggin, it displayes the "frmFieldReq.

'Write Username (access) in access Table
Dim User
User = DLookup("[GROUPID]", "tblUSer", "[user] = '" & txtUser & "'")
If (GroupID) = requests Then
DoCmd.RunSQL "SELECT '" & GROUP & "' AS LoggedInAccess INTO access"
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "Requests", acNormal

Else

FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")
If IsNull(FOID) = FO Then
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmFieldReq"

Not sure what you are trying to do with the logic after the ELSE.
Seems to me it should be something like:

if IsNull(DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & _
"'")) then
Do something because I didn't find a match in the tblUSer table.
Else
FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")
Do something different because I DID find a match in tblUSer table.
Endif

You didn't show all your EndIf statements nor the Dim of FO. If FO is
defined as a string or some number type, you will get an error on your
DlookUp statement because it will return a Null if it can't find a match.

Ron

[storrboy]

There's a fair bit there and I don't know that I can pinpoint
everything as I'm familiar with the project, but I'll comment on what
I see. I'll also offer these few tips.

1) Go to Tools Options Editor Tab...Make sure Require Variable
Declaration is checked. This will enforce you to use Dim statements
for your variables and cut down on the number of ambiguous
references.

2) Periodically and before executing any code choose Compile [project
name] from the Debug menu. This will make the compiler exam your code
and pick out any syntax errors. (I would suspect you'd get more than a
few here) Especially when variables must be declared.

My comments preceeded by *****
****Where does txtUser come from? If it is a control on the form this
code is in you should (probably have to) include the form
reference...as in Me!txtUser. Same applies to the password section
and anywhere else a control reference is made.

If DCount("[user]", "tblUser", "[user] = '" & txtUser & "'") = 0 Then
MsgBox "Get Away"
DoCmd.RunSQL "SELECT '' AS LoggedInAccess INTO access"
Exit Sub
End If

******User is an Access Object. If this is a variable of your own
meaning, then it should be renamed.

'Write Username (access) in access Table
Dim User
User = DLookup("[GROUPID]", "tblUSer", "[user] = '" & txtUser & "'")

*****Where does (GroupID) = requests come from? If GroupID is a
reference to a table field, then it can't work unless this form is
bound to a table. If it is intended to be a variable, then it will
evaluate to null or "" because it has not been Dim'd anywhere. The =
requests in that same line should likely be = "requests". Quotes are
need to indicate this is a value or literal string and not another
variable. If it is a variable then it has not been Dim'd either and
will Null or "". Therefore this statement will always be false and
the "Requests" form will not be used. The If structure will
automatically try to execute whatever is in the Else portion.

If (GroupID) = requests Then
DoCmd.RunSQL "SELECT '" & GROUP & "' AS LoggedInAccess INTO access"
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "Requests", acNormal

Else

******What datatype is FO? If FO is not boolean (true/false) or it can
never be -1 or 0 then the statement If IsNull(FOID) = FO will always
be false. IsNull returns a boolean, so if FO is meant to be text
asking if IsNull = FO is false.

FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")
If IsNull(FOID) = FO Then
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmFieldReq"

'Test it
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmFieldReq"

'DoCmd.OpenQuery "qryPOEs"'

DoCmd.SetWarnings True

End Sub

As mentioned by rwr in another post - you have two open If blocks. One
starting @ If (GroupID) = requests Then the other @ If IsNull(FOID) =
FO Then. These need to be closed.

[cyndithomas via AccessMonster.com]

What I am trying to do is created a Secured Dbase. Created a Login Screen
with the following tblUser, tblAccess.

frmLogin are txt User, txtPassword
tblUser = user = txtUser

tblAccess = loginaccess = txt

tblUser
User Password FOID GroupID
jBean 12345 London
kWood apple REQUESTS

tblAccess
London

If User: kWood (Manager) then frmREQUESTS would be open. If user jBean then
frmFieldReq would displayed

Code:

Option Compare Database

Private Sub cmdCancel_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT '' AS LoggedInAccess INTO access"
DoCmd.SetWarnings True
DoCmd.Quit acQuitSaveAll
End Sub

Private Sub cmdOK_Click()
DoCmd.SetWarnings False

'Check to see if they are a valid user

If DCount("[user]", "tblUser", "[user] = '" & txtUser & "'") = 0 Then
MsgBox "Get Away"
DoCmd.RunSQL "SELECT '' AS LoggedInAccess INTO access"
Exit Sub
End If
'Check password
Dim tempPass
tempPass = DLookup("[password]", "tblUSer", "[user] = '" & txtUser & "'")
If IsNull(txtPassword) Or txtPassword <tempPass Then
MsgBox "Wrong Password"
DoCmd.RunSQL "SELECT '' AS LoggedInAccess INTO access"
Exit Sub
End If

'Write Field Office Name (access) in access Table
Dim FO
FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")

DoCmd.RunSQL "SELECT '" & FO & "' AS LoggedInAccess INTO access"


'Write Username (access) in access Table
' Dim User
' User = DLookup("[GROUPID]", "tblUSer", "[user] = '" & txtUser & "'")
' DoCmd.RunSQL "SELECT '" & GROUP & "' AS LoggedInAccess INTO access"
' DoCmd.Close acForm, "frmLogin"
' DoCmd.OpenForm "Requests", acNormal


' Else
' FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")
' If IsNull(FOID) = FO Then
' DoCmd.Close acForm, "frmLogin"
' DoCmd.OpenForm "frmFieldReq"

'Test it
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "frmFieldReq"

'DoCmd.OpenQuery "qryPOEs"'

DoCmd.SetWarnings True
thank you for your Help

End Sub

storrboy wrote:

>There's a fair bit there and I don't know that I can pinpoint
everything as I'm familiar with the project, but I'll comment on what
I see. I'll also offer these few tips.

1) Go to Tools Options Editor Tab...Make sure Require Variable
Declaration is checked. This will enforce you to use Dim statements
for your variables and cut down on the number of ambiguous
references.

2) Periodically and before executing any code choose Compile [project
name] from the Debug menu. This will make the compiler exam your code
and pick out any syntax errors. (I would suspect you'd get more than a
few here) Especially when variables must be declared.

My comments preceeded by *****

****Where does txtUser come from? If it is a control on the form this
code is in you should (probably have to) include the form
reference...as in Me!txtUser. Same applies to the password section
and anywhere else a control reference is made.

> If DCount("[user]", "tblUser", "[user] = '" & txtUser & "'") = 0 Then
MsgBox "Get Away"
DoCmd.RunSQL "SELECT '' AS LoggedInAccess INTO access"
Exit Sub
End If

******User is an Access Object. If this is a variable of your own
meaning, then it should be renamed.

> 'Write Username (access) in access Table
Dim User
User = DLookup("[GROUPID]", "tblUSer", "[user] = '" & txtUser & "'")

*****Where does (GroupID) = requests come from? If GroupID is a
reference to a table field, then it can't work unless this form is
bound to a table. If it is intended to be a variable, then it will
evaluate to null or "" because it has not been Dim'd anywhere. The =
requests in that same line should likely be = "requests". Quotes are
need to indicate this is a value or literal string and not another
variable. If it is a variable then it has not been Dim'd either and
will Null or "". Therefore this statement will always be false and
the "Requests" form will not be used. The If structure will
automatically try to execute whatever is in the Else portion.

> If (GroupID) = requests Then
DoCmd.RunSQL "SELECT '" & GROUP & "' AS LoggedInAccess INTO access"
DoCmd.Close acForm, "frmLogin"
DoCmd.OpenForm "Requests", acNormal

Else

******What datatype is FO? If FO is not boolean (true/false) or it can
never be -1 or 0 then the statement If IsNull(FOID) = FO will always
be false. IsNull returns a boolean, so if FO is meant to be text
asking if IsNull = FO is false.

> FO = DLookup("[FOID]", "tblUSer", "[user] = '" & txtUser & "'")
If IsNull(FOID) = FO Then

[quoted text clipped - 10 lines]

>>
End Sub

As mentioned by rwr in another post - you have two open If blocks. One
starting @ If (GroupID) = requests Then the other @ If IsNull(FOID) =
FO Then. These need to be closed.

--
Message posted via http://www.accessmonster.com