Inserting UserId into table after login screen (1 Viewer)

syntax

New member
Local time
Today, 04:19
Joined
Jul 29, 2008
Messages
9
Hello All,

I have database which should handle a call system for IT.
I have a login screen form with username and password which works fine.
what I would like to do is that:
when the user enter the system with his username and password (from tblUsers) and open a new call (tblCalls) , the frmOpenNewCall will "know" that the user who open the call and belong to the user who entered the system.
the porpose of this is that when user enter the system, the system will know the belonging of the form and can be accossiacte with it.
for example :

frmLoginScreen

UserName:xxx
Password:1234


frmOpenNewCall

1.Call ID ---- 54 (Automatically)
2.Call Type -- Hardware (User will fill)
3.Call Description (User will fill)
4.Call Status (User will fill)
5.UserID-----the user Id that loged in the system( will be automatically updated !)




Here is my Vb code so far:
Option Compare Database
Private Sub Login_Click()
End Sub
Private Sub cboEmployee_AfterUpdate()
txtPassword.SetFocus

End Sub
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus


Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'Check value of password in tblUsers to see if this matches value chosen in combo box

If Me.txtPassword = DLookup("Password", "tblUsers", "ID = '" & Me.cboEmployee.Value & "'") Then

DoCmd.CLOSE acForm, "frmUserLogin", acSaveNo

MsgBox " ! Welcome"
DoCmd.OpenForm "frmUserOperationSelection", acNormal
DoCmd.CLOSE acForm, "frmMainMenu", acSaveNo

'check to see which user loged in the system

'?????????????????????????
'?????????????????????????



'check if usermade wrong password 3 times
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If
Else
MsgBox "Invalid Password. Please try again."



End If



End Sub
Private Sub UserName_AfterUpdate()
End Sub



10x in advance...
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:19
Joined
Aug 11, 2003
Messages
11,696
Your best solution would be not to close but to hide your login screen. That way you can retrieve who loged in by refering to your login screen.

Please use [ code ] and [/ code ], without the spaces, around your code when you post code, this is unreadable!
 

CyberLynx

Stuck On My Opinions
Local time
Today, 04:19
Joined
Jan 31, 2008
Messages
585
Or maybe something like this:

Code:
Private Sub cmdLogin_Click()
   'Check to see if data is entered into the UserName combo box
   If Len(Nz(Me.cboEmployee, "")) = 0 Then
      MsgBox "You must enter or select a User Name.", vbOKOnly, "Required Data"
      Me.cboEmployee.Undo: Me.cboEmployee.SetFocus
      Exit Sub
   End If
   
   'Check to see if data is entered into the password box
   If Len(Nz(Me.txtPassword, "")) = 0 Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
      Me.txtPassword.SetFocus
      Exit Sub
   End If
   
   'Check value of password in tblUsers to see if this matches
   'value chosen in combo box
   Dim PassWrd As String
   Dim UserNme As String
   Dim UserID As Long
   Static intLogonAttempts As Integer
   
   'The following DLookup assumes that the cboEmployee
   'ComboBox contains more than one column of data with
   'the first column (Column 0) listing the Employees'
   'Record ID and the second Column listing the Employee
   'names.  Normally, Column(0) is set so as not to be
   'visible.
   PassWrd = DLookup("Password", "tblUsers", "ID=" & Me.cboEmployee.Column(0))
   UserID = Me.cboEmployee.Column(0)
   UserNme = Me.cboEmployee.Column(1)
   
   If Me.txtPassword = PassWrd Then
      DoCmd.Close acForm, "frmUserLogin", acSaveNo
      MsgBox "Welcome " & UserNme
      intLogonAttempts = 0
      'Pass the User's ID to the OpenArgs property of the
      'frmUserOperationSelection Form that will be opened.
      DoCmd.OpenForm "frmUserOperationSelection", acNormal, , , , , CStr(UserID)
      DoCmd.Close acForm, "frmMainMenu", acSaveNo
      Exit Sub
   Else
      'check if usermade wrong password 3 times
      intLogonAttempts = intLogonAttempts + 1
      If intLogonAttempts < 2 Then
         MsgBox "Invalid Password. Please try again."
         Me.txtPassword.Undo: Me.txtPassword.SetFocus
      Else
         MsgBox "You currently do not have access to this Database." & vbCr & _
                "Please contact Admin.", vbCritical, "Restricted Access!"
         intLogonAttempts = 0
         Application.Quit
      End If
   End If
End Sub

Then in the frmUserOperationSelection Forms' OnCurrent event:

Code:
Private Sub Form_Current()
   If Me.NewRecord = True And Len(Nz(Me.OpenArgs, "")) > 0 Then
      Me.UserID = CLng(Me.OpenArgs)
   End If
End Sub

.
 

syntax

New member
Local time
Today, 04:19
Joined
Jul 29, 2008
Messages
9
First of All

Thank you so much.
The code that you wrote for me looks great.
but it gives me debug screen
on this line:


PassWrd = DLookup("Password", "tblUsers", "ID='" & Me.cboEmployee.Column(0))


with the messege ""run-time error '3075':
syntax error in string and query... 'ID='037469455'

ANY IDEA??
10x again
 

CyberLynx

Stuck On My Opinions
Local time
Today, 04:19
Joined
Jan 31, 2008
Messages
585
That's because the line you posted as giving the Error isn't the same as the line I placed in the Code modification. You added a apostrophe.

Is the Table Field ID a Text DataType?

If it is, then your on the right track. You just need to add the apostrophe at the end...like this:

PassWrd = DLookup("Password", "tblUsers", "ID='" & Me.cboEmployee.Column(0) & "'")

If it's of a Long Integer DataType then get rid of the apostrophe:

PassWrd = DLookup("Password", "tblUsers", "ID=" & Me.cboEmployee.Column(0))

.
 

Pete666

Registered User.
Local time
Today, 11:19
Joined
Aug 29, 2006
Messages
28
I normally use a hidden form that holds all user data based on what name was selected on logon screen.

i.e.
Username
Password
Department
Email Address
TelNo
(all held in tbl_Security)

If you open this form (hidden) when they select their user name on logon, it can be used to authenticate the password and can be used as an auto populate on any form after logon.
 

syntax

New member
Local time
Today, 04:19
Joined
Jul 29, 2008
Messages
9
Great...But..

It Does not give me any error now but also still does not pass the user id into tblCalls....
I would like that when the user enters the system and create new call - the form will know which user create the form and pass his id into the tblCalls beside the detalis the user entered when he opened the call (call type, description type etc...)

p.s

The screen login Leads the user to frmOpenUserSelection and from there he need to click on botton which leads to frmOpenNewCall which is the table i would like to be updated with his username....

hope i passed my issue clearly...
Thanks again you are realy helpfull...
 

CyberLynx

Stuck On My Opinions
Local time
Today, 04:19
Joined
Jan 31, 2008
Messages
585
You obviously don't want to INSERT the UserID into the table tblCalls unless the User actually accesses that particular Form (rmOpenNewCall).

In my opinion the best thing to do is to create a Login Table, something like what Pete666 had suggested. You can then reference the table when the User Info is needed. This, mind you, is only convenient when there is only a single running DB (one user at any given time). If there are multiple Front-Ends and a single Back-End then this can become an issue.

You could I suppose have a local Login Table in the Front-End and a global Login Table in the Back End which contain very similar data but that's not very efficient.

I suppose you could also use Database wide Public Variables to hold the current User info for each Front-End (if there is a FrontEnd/BackEnd situation).

As another method....You can do as namliam and Pete666 suggested, hide the Login Form and pull from it when needed. I personally don't like running unnecessary resources.

Try this:

- Open a Database Code Module
- Declare a public variable...say: Public UserOnID As Long
- In the Code from the previous post, instead of:
Code:
[B][COLOR="Teal"]   Dim UserID As Long
   UserID = Me.cboEmployee.Column(0)[/COLOR][/B]
do this instead:
Code:
[B][COLOR="Teal"]   UserOnID = Me.cboEmployee.Column(0)[/COLOR][/B]

- Then in the OnCurrent event for the frmOpenNewCall Form have this:
Code:
Private Sub Form_Current()
   If Me.NewRecord = True Then Me.UserID = UserOnID
End Sub

That is of course if the UserID is the Form TextBox Name and it is actually bound to the UserID Field in the tblCalls Table.

.
 

syntax

New member
Local time
Today, 04:19
Joined
Jul 29, 2008
Messages
9
Help Again...

I realy Appriciate your kind and help.
But, Unfortunately, I was unable to make it work exactly as I wanted it.
Maybe because it is impossible or maybe because I did not understand how to do it right….
The code is basically working at the moment but does not do all the functionality I wished.
I will be grateful if you can guide me from the beginning how should it be done.
My current tables are: tblUsers – ID, UserName , Password ,Phone , Email.
tblCalls – ID, CallType, CallDesc, CallDate, UserID
Reletionship: UserID from table tblCalls connected to ID from table tblUsers.
My current Forms are: frmMainMenu which leads to frmUserLogin which leads to frmUserOpenSelection which leads to frmOpenNewCalls.

Target: When user login to the system and create new call using the frmOpenNewCall, the system will know to connect or pass all the users details to tblCalls so the IT Manager will know who open the call.
My Questions:
According to my current schema and tables , should I change anything? Should I delete/rebuild the tblUsers and create tbl_security? If so, how should it be releshionshiped with the other tables?
How should I do it?

10x in advance for your patient.

 

namliam

The Mailman - AWF VIP
Local time
Today, 12:19
Joined
Aug 11, 2003
Messages
11,696
Your best solution would be not to close but to hide your login screen. That way you can retrieve who loged in by refering to your login screen.

Alternatively you can retrieve the windows user if you like using: Environ("Username")
 

CyberLynx

Stuck On My Opinions
Local time
Today, 04:19
Joined
Jan 31, 2008
Messages
585
Well syntax, I have attached a simple sample DB for you to look at. It sort of follows your scenario.

.
 

Attachments

  • User LogIn Sample.zip
    32.4 KB · Views: 375

syntax

New member
Local time
Today, 04:19
Joined
Jul 29, 2008
Messages
9
Gr8!

That was really helpful....

But shouldn't be a relationship between these two tables???
Could find any relationship in your file...is it ok?
In addition, I would like to create another table which will be tblComputer and include ComputerID, ComputerDesc, DateOfPurchase etc...
how should I do that? how should i relationship it to my existing tables?

10x again...

 

CyberLynx

Stuck On My Opinions
Local time
Today, 04:19
Joined
Jan 31, 2008
Messages
585
No, there are no relationships in the Sample. It's just a sample. It's all a matter of what relationships you want that pertain to your own particular project. You can implement whatever you want once you have implemented the concept into your Database if that's the road you want to take.

.
 

Users who are viewing this thread

Top Bottom