Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 24th, 2005, 02:48 PM
Authorized User
 
Join Date: Sep 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Matthew
Default Help My house on Fire

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 admin@makaiit.net
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: admin@makaiit.net
__________________
Matthew Ikechukwu
Email: admin@makaiit.net
 
Old November 24th, 2005, 03:15 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
 
Old November 24th, 2005, 03:21 PM
Authorized User
 
Join Date: Sep 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Matthew
Default


Quote:
quote:Originally posted by echovue
 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: admin@makaiit.net
 
Old November 24th, 2005, 03:26 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
 
Old November 24th, 2005, 05:06 PM
Authorized User
 
Join Date: Sep 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Matthew
Default

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: admin@makaiit.net
 
Old November 25th, 2005, 08:40 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old November 25th, 2005, 05:46 PM
Authorized User
 
Join Date: Sep 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Matthew
Default

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: admin@makaiit.net
 
Old November 25th, 2005, 06:33 PM
Authorized User
 
Join Date: Sep 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Matthew
Default


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: admin@makaiit.net
 
Old November 29th, 2005, 12:35 AM
Authorized User
 
Join Date: Sep 2004
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 30th, 2005, 03:21 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Beer House developer plb BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 47 February 18th, 2008 02:11 PM
The Beer House Arabic Localization nesrine ASP.NET 2.0 Professional 1 February 21st, 2007 12:04 PM
Problem with forum of the beer house kherrerab BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 August 31st, 2006 03:33 PM
The Beer House kooltech BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 May 8th, 2006 10:31 PM
Problem of script functions in-house but not on th david5 Javascript How-To 1 April 4th, 2004 08:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.