Please Help!

Can someone help me look at the code displayed below? Something is terribly wrong with the code. I am trying to manipulate date through code but I can´t get it work.
On the code below I intend to read a Table value from a table called Login.
I have a "logon form" not bound to any table,but intended to grab username and password value and compare it with the value in the table Login.

If every thing goes well the select statement will open menu base on the value. You can send me an Email at [email protected]
Thanks for your help.
--------------------------------here is the code
'Dim MyDB As DAO.Database
'Dim rs As DAO.Recordset
'Dim txtUsername As String
'Dim txtPassword As String
'Dim Departments As String


'Set MyDB = CurrentDb()
'Set rs = MyDB.OpenRecordset("Select * FROM Login", dbOpenSnapShot)


'If Not rs.EOF Then
'txtUsername.SetFocus
'rs("UserName") = txtUsername.Text
'If txtPassword.Visible = True Then
'txtPassword.SetFocus
'rs("Password") = txtPassword.Text
'Select Case Departments

'Case Reception
'txtUsername.SetFocus
'If rs("Username").Value = txtUsername.Text And _
' rs("password").Value = txtPassword.Text Then
'DoCmd.OpenForm "Reception Menu"
'End If
'Case Manager:
'If rs.Fields([UserName]) = txtUsername.Text And _
' rs.Fields([Password]) = txtPassword.Text Then
'DoCmd.OpenForm "administrative Menu"
'End If

'Case Restaurant:
'If rs.Fields([UserName]) = txtUsername.Text And _
' rs.Fields([Password]) = txtPassword.Text Then
'DoCmd.OpenForm "Restaurant Menu"
'End If
'Case Bar:
'If rs.Fields([UserName]) = txtUsername.Text And _
' rs.Fields([Password]) = txtPassword.Text Then
'DoCmd.OpenForm "Bar Menu"
'End If
'Case Else
'If rs.Fields([UserName]) = txtUsername.Text And _
' rs.Fields([Password]) = txtPassword.Text Then
'DoCmd.OpenForm "Bill Menu"

'End If
'End Select



Matthew Ikechukwu
Email: [email protected]

__________________
Matthew Ikechukwu
Email: [email protected]

From what I can see, it doesn't look like the Department variable is set anywhere. Is it being pulled from a control on the form or from another table?

Mike

Mike
EchoVue.com

Actually, Departments are values in the login Table
For example:
Reception
Manager
Accountant
Restaurant
Bar.


Matthew Ikechukwu
Email: [email protected]

Alrighty - but you need to assign Departments the value in the table to the variable. Try adding Department = rs("Department") right before Select Case Department.

I am think that will work, I typically use ADO istead of DOA, but I think the basic idea is the same.

Let me know,

Mike

Mike
EchoVue.com

I got an Error Message "Cannon Update or the record is read-only

Do you have any Idea
In fact, I have a Table in an application Called Login with only two rows Username and Password.

As I said earlier Entered Username as follow
Manager
Reception
etc
I can get the code run. Can you help!

-----------
Dim MyDB As DAO.Database
Dim rs As DAO.Recordset
'Dim txtUserName As String
'Dim txtPassword As String
Dim Departments As String
Dim Reception As String
Dim Manager As String
Dim Restaurant As String
Dim Bar As String
Dim Bill As String

Set MyDB = CurrentDb()
Set rs = MyDB.OpenRecordset("Select * from Login", _
dbOpenSnapshot)



If Not rs.EOF Then
txtUserName.SetFocus
rs("UserName") = txtUserName.Text
If txtPassword.Visible = True Then
txtPassword.SetFocus
rs("Password") = txtPassword.Text
Departments = rs("Login")
Select Case Departments

'Reception = rs("Login")
Case Reception
txtUserName.SetFocus
If rs("Username").Value = txtUserName.Text And _
 rs("password").Value = txtPassword.Text Then
DoCmd.OpenForm "Reception Menu"
End If
Manager = rs("Manager")
Case Manager:
If rs.Fields([UserName]) = txtUserName.Text And _
 rs.Fields([Password]) = txtPassword.Text Then
DoCmd.OpenForm "administrative Menu"
End If

Restaurant = rs("Login")
Case Restaurant:
If rs.Fields([UserName]) = txtUserName.Text And _
 rs.Fields([Password]) = txtPassword.Text Then
DoCmd.OpenForm "Restaurant Menu"
End If

'Bar = rs("Login")
Case Bar:
If rs.Fields([UserName]) = txtUserName.Text And _


Matthew Ikechukwu
Email: [email protected]

This is way the long way around this problem.

Do your error catching on the login form first, and if all the fields are filled out, then open a hidden form by calling the login table:

Dim stUserName As String
Dim stLink As String
Dim stDocName As String
Dim stPassword1 As String
Dim stPassword2 As String

stUserName = Me.UserName
stLink = "[UserName] = " & "'" & stUserName & "'"
stDocName = frmHiddenLogin

DoCmd.OpenForm stDocName, , stLink, , acHidden


Then compare the password like this:

stPassword1 = Me.Password
stPassword2 = frmHiddenLogin.Password

If stPassword1 <> stPassword2 Then

   'close form, messagebox wrong password, exit sub.

Etc.

  Then check the group:

stGroup = frmHiddenLogin.Group

Select Case
  'case opens proper form, etc.

If you use a hidden form for security, then you don't have all these ADO, or worse, DAO issues. I use this method to great effect.

HTH

mmcdonal

Thanks so much. It is nice to know that someone somewhere
care to help other. This trouble has boarded me so much. I have hand code it static but that is not what I want. I am desperately want the Owner of the program to be able to change the user name and password through the user interface in the application.

Ok! I have a simple question? that Is should I have to remain the Login table to frmHiddenLogin?

I did not create a form from the Login Table, I only created the table and Entered the Department values.

Should I Create a form called frmHiddenLogin from the Login Table?




Matthew Ikechukwu
Email: [email protected]

I try to set the code up like this but complier trapped stDocName with Error message "Type Mismatch"
Please Help!
-------------------------------------------------
Dim Title As String
Title = "Sango Plaza Hotel Limited"
Dim frmHiddenLogin As Form
Dim stUserName As String
Dim stLink As String
Dim stDocName As String
Dim stPassword1 As String
Dim stPassword2 As String

stUserName = Me.txtUserName
stLink = "[txtUserName] = " & "'" & stUserName & "'"
stDocName = frmHiddenLogin

DoCmd.OpenForm stDocName, , stLink, , acHidden


'Then compare the password like this:

stPassword1 = Me.txtPassword
stPassword2 = frmHiddenLogin.Password

If stPassword1 <> stPassword2 Then
MsgBox " That cannot be compute", , Title
Exit Sub
End If
Cleartext
'close form, messagebox wrong password, exit sub.

' Then check the group:

stGroup = frmHiddenLogin.Group

Select Case Departments

Reception = frmHiddenLogin.Group
Case Reception
If stPassword1 <> stPassword2 Then
MsgBox " That cannot be compute", , Title
Exit Sub
stUserName = Me.UserName
stLink = "[UserName] = " & "'" & stUserName & "'"
stDocName = frmHiddenLogin
stPassword1 = Me.Password
stPassword2 = frmHiddenLogin.Password
DoCmd.OpenForm "Reception Menu"
End If
Manager = frmHiddenLogin.Group
Case Manager
If stPassword1 <> stPassword2 Then
MsgBox " That cannot be compute", , Title
Exit Sub
stUserName = Me.UserName
stLink = "[UserName] = " & "'" & stUserName & "'"
stDocName = frmHiddenLogin
stPassword1 = Me.Password
stPassword2 = frmHiddenLogin.Password
DoCmd.OpenForm "administrative Menu"
End If

Restaurant = frmHiddenLogin.Group
Case Restaurant
If stPassword1 <> stPassword2 Then
MsgBox " That cannot be compute", , Title
Exit Sub
stUserName = Me.UserName
stLink = "[UserName] = " & "'" & stUserName & "'"
stDocName = frmHiddenLogin
stPassword1 = Me.Password
stPassword2 = frmHiddenLogin.Password
DoCmd.OpenForm "Restaurant Menu"
End If

Bar = frmHiddenLogin.Group
Case Bar
If stPassword1 <> stPassword2 Then
MsgBox " That cannot be compute", , Title
Exit Sub
stUserName = Me.UserName
stLink = "[UserName] = " & "'" & stUserName & "'"
stDocName = frmHiddenLogin
stPassword1 = Me.Password
stPassword2 = frmHiddenLogin.Password
DoCmd.OpenForm "Bar Menu"
End If
Else

MsgBox "You have are unauthorized user", , Title

End Sub

Matthew Ikechukwu
Email: [email protected]

Two things I noticed from your code at the top of the post
1. To assign a value to the recordset you need to use
rs.Edit

rs.Update

2. The recordset is read only because a dbSnapShot is readonly
use dbDynaset to be able to write.



Database Agreements

You forgot the quotes in this line:
stDocName = frmHiddenLogin

Should be:
stDocName = "frmHiddenLogin"

Strings need to always be in quotes, numbers do not use quotes. Quotes signal VBA that the value is to be treated as a string, no quotes that the value is to be treated as a number.



mmcdonal