Solved

SQL Server Login.

Posted on 2001-06-21
12
374 Views
Last Modified: 2010-05-02
Question I Am Trying To Write An Application That Will Be Used To Login To An Application I Am Writing. What It Will Do As Follows.

1.) The Execute The Program A Logon Box Will Appear.
    A.) Logon Box Is Connected To A Table Login On My
        SQL Server Where All Usernames And Passwords
        Will Be Stored.
2.) Next If The Login Is Correct It Will Connect To A
    Payroll Table But Only To There Record In The Table
    If The Login.UserID = Payroll.UserID
           And Login.Password = Payroll.Password
3.) If There Is A Y In The Login.Admin Field The Can
    Change Or View All Records In The Payroll Table


So Far And Now I Am Confused On What To Do Next And How
To Code It.

<-----------------------------Form---------------------->
Private Sub cmdLogin_Click()
Retrive txtUserID.Text
End Sub
Private Sub Form_Load()
txtUserID.Text = ""
txtPWD.Text = ""
End Sub
Public Function Retrive(Search As String)
Top:
 
Dim sSQL
    sSQL = "SELECT UserID,"
    sSQL = sSQL & "Password, Admin "
    sSQL = sSQL & "FROM Login WHERE (UserID = UserID) And (Password = Password) "
    DTLogin.rsdbo_LoginOk.Open sSQL
    If txtUserID.Text = "" Then
    MsgBox "Username Is Blank"
    txtUserID.SetFocus
    DTLogin.rsdbo_LoginOk.Close
    End If
    If txtPWD.Text = "" Then
    txtPWD.SetFocus
    End If
    If txtUserID.Text <> DTLogin.rsdbo_LoginOk.Fields(0) Then
    GoTo Bottom
    Else
    GoTo Top
    txtUserID.Text = DTLogin.rsdbo_LoginOk.Fields(0)
    If txtPWD.Text <> DTLogin.rsdbo_LoginOk.Fields(1) Then
    GoTo Bottom
    Else
    txtPWD.Text = DTLogin.rsdbo_LoginOk.Fields(1)
    If DTLogin.rsdbo_LoginOk.RecordCount = 0 Then
    GoTo Bottom
    Else
        MsgBox "Login Succeeded", vbInformation
        Form1.Show
        DTLogin.rsdbo_LoginOk.Close
        Exit Function
        End If
        End If
        End If
Bottom:
    DTLogin.rsdbo_LoginOk.Close
        MsgBox "You do are not authorised to view this site/software", vbCritical
Exit Function
End Function
<---------------------------End Form--------------------->

<----------------------------Module---------------------->
 Sub main()
    Dim rs As ADODB.Recordset
    Dim sCN As String
    Dim sSQL As String
    sCN = " Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=BLANK;Initial Catalog=PAYROLL;Data Source=MyServer"
    Set cn = New ADODB.Connection
    cn.ConnectionString = sCN
    cn.Open
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = cn
    Login.Show
 End Sub
<-------------------------End Module-------------------->
0
Comment
Question by:VBSysCOM1
  • 6
  • 6
12 Comments
 
LVL 2

Expert Comment

by:TravisHall
Comment Utility
Stop. Back up. Consider your design carefully.

Do you really need to handle this security with your own tables and such? Is there a good reason why you can't use SQL Server's built-in security to handle this?

I would take one of two (very similar) approaches, depending on how I wanted to handle security otherwise.

Firstly, if you really want that logon dialog, use SQL Server authentication for logging in to SQL Server - that means you need to revise your connection string, so that it includes user name and password.

Create a login for each user. In the Payroll table, have a User column, and the values in this column will be the user names. Create a stored procedure containing the statement "SELECT * FROM Payroll WHERE User = SYSTEM_USER", or something along those lines - this will retrieve the information in the table for that user only, and most users will execute this stored procedure to get their data.

Also, create a role for that database - say, PayrollAdmin. Put each of your administrative users into this role. (Get rid of that "Admin" column, it is not necessary.)

Now, give all of your users permission to execute that stored procedure. Give the PayrollAdmin role permission to access the Payroll table directly, but make sure other ordinary users do not have such permission.

If your application needs to know who is an admin and who isn't, you can use the IS_MEMBER SQL function to determine whether a user is in the PayrollAdmin role - "SELECT IS_MEMBER('PayrollAdmin')".

And that's it.

The second approach is to do pretty much all the same thing, but use Windows NT authentication instead of SQL Server authentication. You can only do this if you are running Windows NT, of course, but I much prefer to do this if I can. (Here, we use Windows NT, thank goodness.) You do everything pretty much the same, but in the Payroll table's User column you put the user's Windows NT domain and user name ("MYDOMAIN\myusername"). You create a Windows NT user group called, say, PayrollUsers, and another user group called, say, PayrollAdmins, and put your users in the appropriate groups. Give both of these groups access to your database using SQL Server's security features, and put the PayrollAdmins group into the PayrollAdmin role in your database. The PayrollUsers group does not get direct access to the Payroll table.

With this second approach, you don't use a login dialog in your application, and instead you know who the user is because of who has logged onto the machine running the application. Users, of course, will have to be aware that they are responsible for ensuring that their own login is secure - they should not leave their machine logged on, unsecured and unattended. (Just like they should not leave your application running and logged in, if you use a login dialog.)

SQL Server 7 has some very powerful security features. Your applications and the admin that you have to do to keep them running can be greatly simplified if you use those security features correctly and to their full potential.
0
 

Author Comment

by:VBSysCOM1
Comment Utility
Ok Trav,
        I see what your are saying and it does seem like a better approach to this problem than the one I was trying to use. There is one thing I do not understand and that is.
Now I need each of the users when they login only to logon to there record and punch in for the day and not to be able to edit or change the data after punching in except the ones that have the permisions to do so.

Here is an example of the table.

UserID Size = 10    
Pwd    Size = 12
fname  Size = 25
lname  Size = 15
tin    Size = 5 Military Time
tout   Size = 5 Military Time
chkin  Size = 1  
chkout Size = 1
date Size = 10
admin Size = 1

Now here is what I want it to do. If "UserID" and "Pwd" = Assigned Username And Password then "tin" = time and "chkin" =  2 and "date" = date. This this info will be added to the record then updated and exit back to the login screen. Now if that Username  and the field admin = 2 then Then show a button That allows this user to adjust any of the other users time.

Now at the end of the day when user logins. If "UserID" and "Pwd" = Assigned Username And Password then and "chkin" =  2 and "date" = today. Then prompt the user with a message ask are you sure you want to logout use the vbYesNo if no is selected go back to login screen. If Yes is selected then "tout" = time and "chkout" = 2 update the record and go back to the login screen and wait for the next user to punch in or punch out.

Now here is my problem. I don't know how to code this because of my lack of SQL programming in VB. So if you could give me a sample on how to excute this I will be most greatful and would raise the points to this question.

I know that this is alot to ask someone but helping me with this would not only make this program work but also would incress my VB skills.


Thank You In Advance,
                     Kevin R. Durler
                     
0
 
LVL 2

Expert Comment

by:TravisHall
Comment Utility
Hmmm. Okay, there's a fair bit to this, but let's have a go at it. Now remember, I'm typing this straight into a browser window, because I don't have your database to play with and test against, so you'll have to think a bit about what I say, modify it where necessary to suit your needs, and test carefully, because it's going to be untested stream-of-consciousness stuff from me.

Firstly, your table format seems a bit strange. Are you saving everything as varchar or char? That's not a great approach. I'd be looking at something like:

Payroll table:
UserID sysname
tin    smalldatetime
tout   smalldatetime, allow nulls
chkin  tinyint
chkout tinyint
date   datetime, default value CONVERT(datetime, FLOOR(CONVERT(float, GETDATE())))

Users table:
UserID sysname
fname  varchar(25)
lname  varchar(15)

sysname is a synonym for varchar(somethingorother), and is the right size for storing SQL Server login names. If you use it appropriately, you don't have to worry about MS changing SQL Server to allow bigger login names later on.

Storing times as military times isn't terribly good practice, because formatting really should be handled as part of the user interface. Better to store a time as a time (datetime or smalldatetime). According to the schema I described before, we don't need password or admin at all, and you seem to be storing small numbers in chkin and chkout, so we'll use a small numeric data type.

You don't want to store the user's details in every record in Payroll - that takes up a lot of extra space. A separate table for storing the user's real-life names should do fine. This data would be entered beforehand, when the user is added to the system.

Now, you need a stored procedure to add a record for when a user first logs in on a given day. Use SQL Server Enterprise Manager to put the following code in a stored procedure called (say) up_StartWork:

IF NOT EXISTS(SELECT * FROM Payroll WHERE UserID = SYSTEM_NAME AND date = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE()))))
BEGIN
  INSERT INTO Payroll (UserID, tin, chkin) VALUES (SYSTEM_USER, GETDATE(), 2)
  RETURN 0
END
RETURN 1


In another stored procedure, say, up_EndWork, put the code:
IF EXISTS(SELECT * FROM Payroll WHERE UserID = SYSTEM_NAME AND date = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE()))))
BEGIN
  UPDATE Payroll SET tout = GETDATE, chkout = 2 WHERE UserID = SYSTEM_USER AND date = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE())))
  RETURN 1
END
RETURN 0


Now, some VB code. Don't connect to the database until after the user has entered his details, and alter your connection code to something like:

Dim sCN As String
Dim sSQL As String
sCN = " Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial
Catalog=PAYROLL;Data Source=MyServer"
Set cn = New ADODB.Connection
cn.Open sCN, txtUserID.Text, txtPWD.Text


Now, having gotten a connection, you will need some code to execute your stored procedures, something like:
Dim cm As ADODB.Command
Dim rs As ADODB.Recordset
Set cm.ActiveConnection = cn
cm.CommandText = "up_StartWork"
cm.CommandType = adCmdStoredProc
cm.Execute

Set rs = cn.Execute("SELECT IS_MEMBER('PayrollAdmin')")
cmdShowAllUsers.Visible = (rs(0) = 1)
rs.Close

'Check to see if the user has already logged in to the system today
If cm.Parameters("RETURN_VALUE").Value = 1 Then
  If MsgBox("Are you sure you wish to log off for the day?", vbYesNo) = vbYes Then
    cm.CommandText = "up_EndWork"
    cm.Execute
    If cm.Parameters("RETURN_VALUE").Value = 1 Then
      'There was an error and you had better write some code to deal with the problem
    End If
  End If
End If

cn.Close


Of course, you will have more functionality to add in, so you will have to modify and add to this code. It should give you the general idea, though. This code just handles checking in and checking out each day, and working out whether your user is an admin. I'm not sure precisely what you want to do with regards to data display and letting users alter data, so I haven't done anything like that.

BTW, have you used Option Explicit in each module? I don't see a definition for cn in your original code, so I was just wondering.

And when it comes to displaying those times in "military time", use VB's format$ function - format$(time, "hh:nn").

Does that help any?
0
 

Author Comment

by:VBSysCOM1
Comment Utility
Travis,
       I created 2 tables on the SQL Server.
       
       Payroll
       With The Folling Fields:
       UserID varchar
       tin smalldatetime
       tout smalldatetime
       chkin tinyint
       chkout tinyint
       datein datetime

       USERS:
       UserID varchar
       fname varchar
       lname varchar

UP_STARTWORK:
CREATE PROCEDURE [up_startwork] AS
IF NOT EXISTS(SELECT * FROM Payroll WHERE UserID =SYSTEM_USER AND datein = CONVERT(datetime, FLOOR(CONVERT(float,
GETDATE()))))
BEGIN
INSERT INTO Payroll (UserID, tin, chkin) VALUES (SYSTEM_USER, GETDATE(), 2)
RETURN 0
END
RETURN 1


UP_ENDWORK:
Then I Created 2 Stored Procedures
CREATE PROCEDURE [up_endwork] AS
IF EXISTS(SELECT * FROM Payroll WHERE UserID = SYSTEM_USER AND date = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE()))))
BEGIN
UPDATE Payroll SET tout = DATE, chkout = 2 WHERE UserID = SYSTEM_USER AND date = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE())))
RETURN 1
END
RETURN 0

Next I Created From Scratch A New Project And Placed The VB Code Under A Command Button Called cmdPunchIn

Private Sub cmdPunchIn_Click()
Dim cn As ADODB.Connection
Dim sCN As String
Dim sSQL As String
sCN = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial " & _
      "Catalog=PAYROLL;Data Source=MyServer"
Set cn = New ADODB.Connection
cn.Open sCN, txtuserid.Text, txtpwd.Text
Dim cm As ADODB.Command
Dim rs As ADODB.Recordset
Set cm.ActiveConnection = cn
cm.CommandText = "up_StartWork"
cm.CommandType = adCmdStoredProc
cm.Execute
Set rs = cn.Execute("SELECT IS_MEMBER('PayrollAdmin')")
cmdShowAllUsers.Visible = (rs(0) = 1)
rs.Close
If cm.Parameters("RETURN_VALUE").Value = 1 Then
 If MsgBox("Are you sure you wish to log off for the day?", vbYesNo) = vbYes Then
   cm.CommandText = "up_EndWork"
   cm.Execute
   If cm.Parameters("RETURN_VALUE").Value = 1 Then
   End If
 End If
End If
cn.Close
End Sub

Now I Run This Application And I Receive A Run-Time Error 91.

And It Points To This Line To Debug
Set cm.ActiveConnection = cn

As I Said Before I don't really now to much about the ado connection and SQL so all the help I can get would be helpful.

Thank You And Have A Great Day!


0
 
LVL 2

Expert Comment

by:TravisHall
Comment Utility
Whoops, missed a line. Well, I did warn you that what I was giving you was straight off the top of my head.

Immediately before:
Set cm.ActiveConnection = cn
you need:
Set cm = New ADODB.Command

(Error 91 is an object variable not set. cm is an object variable, and we need to set it before using it. cn is also an object variable, but we remembered to set that.)

A thought - for consistency, that date field in the Payroll table could be smalldatetime data type, rather than datetime.

I'm guessing that you probably want to display a user's data (and only that user's data) to a user at some stage. As I said, you'll need a stored procedure for that, and I could give you some code for that, but it would probably be beneficial for you to work that out yourself. I know I don't learn anything nearly so well when it is just told to me - far better to puzzle it out myself. But yell if you are really stuck.
0
 

Author Comment

by:VBSysCOM1
Comment Utility
Travis,
       Ok that took care Run-Time Error 91. Now I am receiving a different error.

<INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_PayRoll_Users'. The conflict occurred in database 'PAYROLL', table 'Users', column 'UserID'.> : Integer :

Now I Check The The Stored Procedure 'up-StartWork'

CREATE PROCEDURE [up_startwork] AS
IF NOT EXISTS(SELECT * FROM Payroll WHERE UserID = '*' AND datein = CONVERT(datetime, FLOOR(CONVERT(float,
GETDATE()))))
BEGIN
INSERT INTO Payroll (UserID, tin, chkin) VALUES (SYSTEM_USER, GETDATE(), 2)
RETURN 0
END
RETURN 1

And The Procedure 'up_EndWork'

CREATE PROCEDURE [up_endwork] AS
IF EXISTS(SELECT * FROM Payroll WHERE UserID = SYSTEM_USER AND date = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE()))))
BEGIN
UPDATE Payroll SET tout = DATE, chkout = 2 WHERE UserID = SYSTEM_USER AND date = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE())))
RETURN 1
END
RETURN 0

Note I changed the field Date to DateIn because I thought it would cause problems being that Date is a reserved word in SQL.

Here Is The PayRoll Table

UserID     varchar     50 'Foreign Key Nulls Not Allowed    
tin     smalldatetime 'Nulls Not Allowed          
tout     smalldatetime 'Nulls Allowed          
chkin     tinyint  'Nulls Allowed    
chkout     tinyint      'Nulls Allowed    
DateIn     datetime 'Nulls Allowed    

Here Is The Users Table:

UserID     varchar     50 'Primary Key Nulls Not Allowed
fname     varchar     15 'Nulls Allowed    
lname     varchar     25 'Nulls Allowed

VB Code

No Modules Just One Form

Option Explicit
Dim sCN As String
Dim sSQL As String
Dim cn As ADODB.Connection
Private Sub cmdLogin_Click()
sCN = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial " & _
      "Catalog=PAYROLL;Data Source=205.146.178.9"
Set cn = New ADODB.Connection
cn.Open sCN, txtuserid.Text, txtpwd.Text
Dim cm As ADODB.Command
Dim rs As ADODB.Recordset
Set cm = New ADODB.Command
Set cm.ActiveConnection = cn
cm.CommandText = "up_StartWork"
cm.CommandType = adCmdStoredProc
cm.Execute
Set rs = cn.Execute("SELECT IS_MEMBER('PayrollAdmin')")
Command1.Visible = (rs(0) = 1)
rs.Close
If cm.Parameters("RETURN_VALUE").Value = 1 Then
 If MsgBox("Are you sure you wish to log off for the day?", vbYesNo) = vbYes Then
   cm.CommandText = "up_EndWork"
   cm.Execute
   If cm.Parameters("RETURN_VALUE").Value = 1 Then
   End If
 End If
End If
cn.Close
End Sub    

Not Sure How To Link This Command Button To Use It.
cmdShowAllUsers.Visible = (rs(0) = 1)
So I left it out for now.

Thank You For Helping Me With This. I Am Starting To UnderStand Alot About SQL Being Used With VB. I am also raising the points for this because you have been basicly have been writting all the code.    

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:TravisHall
Comment Utility
Whoopsie, again. I just noticed another problem. In up_endwork, "SET tout = DATE" should be "SET tout = GETDATE". As I said, untested code...

With that error, it looks to me like you are trying to insert a record into Payroll with a UserID which does not appear in the Users table. Check to make sure that the user you are testing this for does appear in the Users table. Also, be careful about exactly what the user name is supposed to be. I know that when using Windows NT Authentication, you need to include the user's domain in that entry - "MYDOMAIN\myusername".

Try connecting to your database using the Query Analyzer and that user's authentication details, and execute the query "SELECT SYSTEM_USER". That will tell you exactly what that user's ID should be.

cmdShowAllUsers: Do you mean you don't know what to do when the user clicks on that button? If so, you should do whatever it was that you intended for an admin user to do before I put my 2? in. Maybe display a grid showing all the records of the Payroll table for the current day and allow him to modify those records. If you have set up your security properly, you shouldn't have any particular restrictions on what that user can do, so you can execute whatever SQL code you care to against the database, maybe use a data control (I never like data controls, but others find they make things easier for them). I don't really know what you want to do with it.
0
 
LVL 2

Accepted Solution

by:
TravisHall earned 250 total points
Comment Utility
Kevin, do you need any more help with this? How is it going?

It's just that I haven't heard anything from you in a while, and if you're doing okay now you might like to close off the question. (I hate the way some people leave questions interminably unclosed.)
0
 

Author Comment

by:VBSysCOM1
Comment Utility
Yes I do need more help. What you gave was not working they I wanted it to. Do to my lack of understanding of SQL and ADO I do have what I want now and It does work but then stops working here is what I did see if you can help me with this. I do not want to use SQL Security For This Application. Reason being I want any users from any system to be able to login from any where. There for I created a table called Users and made a few changes to my design and what I want it to do. Here is what I Have so far.

Sql Tables:

  "Users Table:"
userid      varchar      50      
fname      varchar      15      
lname      varchar      25
password varchar 12
Status      int      4      

  "Payroll Table:"
userid      varchar      50      
checkin      datetime 8            
checkout datetime 8

Stored Procedures:

   "LoginUsers:"
CREATE PROCEDURE [LoginUsers(                         @UserID as varchar)
AS
(Select * From Users Where UserID = @UserId)
Begin
Update Users Set Status = 1 Where UserId = @UserId
End
If Exists( Select * From Payroll Where Userid = @UserId)
Begin
Update Payroll Set Userid =  @UserId,  Checkin = Getdate()
End
If Not Exists( Select * From Payroll Where Userid =@UserID)
Begin
Insert Into Payroll(Userid, Checkin)VALUES(@UserID, Getdate())
End

          "DayEnd"
CREATE PROCEDURE [DayEnd]
                 (@UserID as varchar)
AS
If Exists(Select * From Users Where UserID = @UserId)
Begin
Update Users Set Status = 2 Where UserId = @UserId
End
If Exists(Select * From Payroll Where Userid = @UserID)
Begin
Update Payroll Set Userid = @UserID, Checkout = GetDate()
End

VB Code:

   "Login Form:"
Option Explicit
Dim IsValidUser As Boolean
Dim sCN As String
Dim sSQL As String
Dim cm As ADODB.Command
Dim cm2 As ADODB.Command
Dim rst As Recordset
Dim rst2 As Recordset
Dim cn As ADODB.Connection
Dim LogoutYN As Integer
Dim vsql As String
Dim dummy As Integer
Dim result As Variant


Private Sub cmdLogin_Click()
Connection
If IsValidUser = True Then
    Login
End If
End Sub

Public Sub Login()
If rst(4) = 0 Or rst(4) = "" Then
Set cm = New ADODB.Command
Set cm.ActiveConnection = cn
cm.CommandText = "LoginUsers" & "('" & Trim(txtuserid.Text) & "')"
cm.CommandType = adCmdStoredProc
cm.Execute
MsgBox "Logged in"
Else
If rst(4) = 1 Then
Call Logout
Else
Exit Sub
End If
End If
End Sub

Public Function Logout()
Dim LoginYN As Integer
Set cm = New ADODB.Command
Set cm.ActiveConnection = cn
If MsgBox("Are you sure you wish to log off for the day?", vbYesNo) = vbYes Then
cm.CommandText = "DayEnd" & "('" & Trim(txtuserid.Text) & "')"
cm.CommandType = adCmdStoredProc
cm.Execute
  MsgBox txtuserid.Text & " Punched Out At: " & Time & " On: " & Date
rst(4) = 0
  rst.Close
  cn.Close
  Else
  Exit Function
End If
End Function

Public Sub Connection()
sCN = "Provider=SQLOLEDB.1;Password='mypassword';Persist Security Info=True;User ID='myusername';Initial Catalog=PAYROLL;Data Source=000.000.000.000"
Set cn = New ADODB.Connection
cn.Open sCN
vsql = "Select * from Users where Userid='" & Trim(txtuserid.Text) & "' AND password='" & Trim(txtpwd.Text) & "';"
Set rst = cn.Execute(vsql)
If rst.EOF Then
    IsValidUser = False
    ErrMsg (1)
    ErrMsg (2)
Else
    IsValidUser = True
End If
End Sub
Public Function ErrMsg(msg As Integer)
Select Case msg
Case 1
If txtuserid.Text = "" Then
MsgBox "Anonymous Login Is Not Excepted!", vbInformation, "Null Username Field"
txtuserid.SetFocus
Err.Clear
Exit Function
Else
MsgBox txtuserid.Text & " Not Found In The Payroll System For: " & txtuserid.Text, vbInformation, "Username."
txtuserid.SetFocus
Err.Clear
Exit Function
End If
Exit Function
Case 2
If txtpwd.Text = "" And txtuserid.Text = "" Then
txtuserid.SetFocus
Exit Function
Else
If txtuserid.Text <> "" And txtpwd.Text = "" Then
MsgBox txtuserid.Text & " You Have Not Entered Your Password Try Again!", vbInformation, "Password Field Is Null"
txtpwd.SetFocus
End If
End If
Exit Function
Case 3
MsgBox txtuserid.Text & " One Of 3 Things Happened...." & vbCrLf & _
                        "1.) Incorrect Username. " & vbCrLf & _
                        "2.) Incorrect Password. " & vbCrLf & _
                        "3.) " & txtuserid.Text & " Is Not Login." & vbCrLf & vbCrLf & _
       "Solution See Payroll Administrator (JUDY Winkowski) For Futher Help.", vbInformation, "Invalid Logout"
Exit Function
End Select
End Function
Private Sub cmdLogout_Click()
Connection
Logout
End Sub
Private Sub cmdShowAll_Click()
AllUsers.Show
End Sub
Private Sub txtuserid_LostFocus()
txtuserid.Text = UCase(txtuserid.Text)
End Sub

  "AllUsers Form"
Private Sub cmdDelete_Click()
adoAllUsers.Recordset.Delete adAffectCurrent
End Sub
Private Sub cmdSearch_Click()
Dim sqltext As String
sqltext = "Select * From Users Where UserID = '" & txtEnterUser.Text & "'"
adoAllUsers.RecordSource = sqltext
adoAllUsers.Refresh
End Sub
Private Sub Form_Load()
lbluserid.Caption = Login.txtuserid.Text
End Sub

So basicly what I want this to do is When user loggs in check the user against the Users table If user and password exsist in the table then update the Status field from either Null or 0 to 1 then Insert The UserId from the Users Table into the Payroll Table and the checkin = Date
Now if user loggs in and the UserID and Password Are equal to the Users Table And The Status = 1 The Prompt User That They are already logged in and give them the option to logout. If they logout set the Status = 0.
0
 

Author Comment

by:VBSysCOM1
Comment Utility
TravisHall,

 Sorry so late but have been away for awhile. I had to make a few of my own adjustments to my app to get it to work the way I wanted but all the code that you gave me was a great help in getting to where I am now with the app. Again thank you for all your help with my problem. Thanks to you coding to the SQL server is not foreign to me now.

Thank You and have a great day!
                               Kevin R. Durler
                               
0
 
LVL 2

Expert Comment

by:TravisHall
Comment Utility
That's okay, Kevin.

I must apologise for not getting back to you about your additional problems. Life outside of the programming world has kept me particularly busy just recently, and I've barely glanced at KX. Sometimes, other things have to have priority. Sorry.
0
 

Author Comment

by:VBSysCOM1
Comment Utility
Hey Travis,
           I do understand what you are saying as I said I had a death in my family and programming was the last thing on my mind also. You have a great day and thank you so much for all your help.


Thank you and have a great day!
                               Kevin R. Durler
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now