DLookup function to check if the password matches

[Sniper-BoOyA-]

Howdy,

Ive been trying make a Login form for our people in the lab, so if they enter data, their login initials will be automaticly entered in a txt box. Just so we can keep track of who is working on what etc.

Ive found the following code from 6 years ago that is pretty much what i had in mind.

Option Compare Database
Private intLogonAttempts As Integer
Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.cboEmployee.SetFocus
End Sub
Private Sub cboEmployee_AfterUpdate()
strEmpName = cboEmployee
'Na selecteren Gebr naam, focus op wachtwoord veld
Me.txtPassword.SetFocus
End Sub
Private Sub cmdLogin_Click()
'Gebruikersnaam geselecteerd?
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "Selecteer uw Gebruikersnaam.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus
Exit Sub
End If
'Wachtwoord ingevuld?
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "Voer uw wachtwoord in.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'Controlleren of opgegeven wachtwoord klopt.
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
lngMyEmpID = Me.cboEmployee.Value
'SLuit logon and start Start bij succesvol login

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Start"
Else
MsgBox "Wachtwoord komt niet overeen, probeer het nogmaals.", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'Na 3 foute pogingen, sluiten db

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "Toegang Geweigerd. Neem contact op met de Administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub
Private Sub Annuleren_Click()
On Error GoTo Err_Annuleren_Click
Dim stDocName As String
stDocName = "afsluitendb"
DoCmd.RunMacro stDocName
Exit_Annuleren_Click:
Exit Sub
Err_Annuleren_Click:
MsgBox Err.Description
Resume Exit_Annuleren_Click

End Sub

This code simply puts the EmpID into a string, and i added a public module so i can retrieve that information in other forms. And it works just fine.

But what i want is that it puts the EmpInitials in the string instead of the ID, so it doesnt show a number, but the actual initial (doh)

But if i either change the Dlookup into

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[strEmpInitials]=" & Me.cboEmployee.Value)

or edit the dropdown box so it takes Initials instead of ID, it stops working.

Then the Dlookup function does not work anymore.

Do you guys have any ideas ??

I wish i could upload the .mdb file but it turns out that its bigger than the limit....

Thanks in advance!

 

[JANR]

Instead of using Dlookup you can use Dcount like this:

If DCount("*", "tblMedewerkers", "[strAfkorting]='" & Forms!frmLogin!cboAfkorting & "'") > 0 Then
    DoCmd.Close, acForm, "Loginformname"
    DoCmd.OpenForm "Start"
Else
   MsgBox "Wachtwoord komt niet overeen", , "probeer het nogmaals.", vbOKOnly, "Wachtwoord Onjuist!"
   intLogonAttempts = intLogonAttempts + 1
   txtPassword.SetFocus
End If

[COLOR=#0000ff]If txtPassword = strWachtwoord Then[/COLOR]
[COLOR=#0000ff]DoCmd.Close[/COLOR]
[COLOR=#0000ff]DoCmd.OpenForm ("Start")[/COLOR]

You can't do this, first txtPassword is a control on your form so to refrence this in code you must use Me.txtPassword.

But shoulden't you check both username AND Password since it is possible that some users CAN have the same password.

Dim StrWhere As String
 
StrWhere = ""
StrWHere = strWhere & "[strAfkorting]= '" & Me.cboAfkorting & "'"
strWhere = strWhere & " AND "
strWhere = strWhere & "[password]= '" & Me.txtPassword & "'"
If Dcount("*", "tblMedewerkers", strWhere ) > 0 Then
    [COLOR=red]DoCmd.Close, acForm, "Loginformname"[/COLOR]
    DoCmd.OpenForm "Start"
Else
   MsgBox "Wachtwoord komt niet overeen", , "probeer het nogmaals.", vbOKOnly, "Wachtwoord Onjuist!"
   intLogonAttempts = intLogonAttempts + 1
   txtPassword.SetFocus
End If

Make note of the close argument in red, make a habbit of specifying what to close.

Hope this helps.

JR

 

[Sniper-BoOyA-]

Thanks Janr, i will have a look.. But when you replied i edited the original post with a different code i made earlier today I still have the one u replied too though so i will have a look at that aswell, thanks!

 

[Sniper-BoOyA-]

Ok, i dont know if its helpfull but i put the forms and table in question into an empty database, so i can attach it to this reply.

As you can see the result of the dropdown menu is EmpID, and saved into a public string(module1). Which is being used on Formulier1

Then it checks if the password matches using the DLookup function with the EmpID as the criteria.

Thats excactly what i want allthough, i want the DropdownMenu to select the Initials, and save it into the String...

I want the Dlookup function to take the Initials as the criteria, instead of the EmpId.

Ive tried to:

- Edit the table (remove ID and put Initials as first)
- Edit the Dropdown Menu by changing the Select Query behind it, and then change the Dlookup function..

But then the Dlookup gets messed up and the 'login' button doesnt work anymore.

This is the original concept i worked on, and as u can see i almost had it to work, But i want the Initials instead of a simple EmpId.

Anyways, as i mentioned before ive attached the smaller version of the database to this reply, so if you want to have a peek, you can.

Thnks again for your time and effort. And sry for being a pain in the butt with my questions and comments.

 

[DCrake]

If you look in the sample apps section you will find an example of password login form that dos all you need including session logging.

 

[JANR]

Try this.

It was just a matter of cleaning up your combobox with the right names and hide some columns.

As a sidenote you don't need the public variabel, you can just refer to the combobox directly since the bound column is the empID

JR

 

[Sniper-BoOyA-]

Nice !

And how do i show the result of the dropdown menu on Formulier1 as Initials isntead of the EmpID?

Because the whole idea behind this is that the initials of the user who logged in, will be linked to a textbox so he wont have to select the initials himself.

Right now the people in the lab have to enter their initials everytime they add lab-results etc... The idea is to do it automaticly when they enter data in a specific form. Which will be programmed later ofcourse..

Right now it still shows the EmpID on formulier 1, instead of the EmpInitials..

 

[JANR]

You do know you can refrence hidden columns in a combobox by refrencing the column index like this:

cboname.Column(x) where x is the index, just remember that the first column has a index of 0.

So if initials is in the 3. column then you find it like this

cboAfkorting.Column(2)

JR

 

[Sniper-BoOyA-]

Thanks m8, ill have another go at it.. And ill let u know what the result is

 

[Sniper-BoOyA-]

Good morning everyone,

its me again

Ive tried to put the : strInitials(Me.CboEmployee.Column(2) On all the forms to see whether it should work or not, but i cant figure it out..

Am i supposed to put it in the AfterUpdate() of the cbo box? Or am i supposed to define the textbox on Formulier1 as strInitials(Me.CboEmployee.Column(2)

Sorry for bothering you again, been trying to get it to work all night, which resulted in me not sleeping

 

[JANR]

Didn't you have a Public variable in a module called StrEmpName ?

Why don't you pass the initial to it from the loginform and have it availible through out the session.

Private cboEmployee_AfterUpdate()
StrEmpName = Me.cboEmployee.Column(2)
End Sub

And in the Standard module:

Public StrEmpName As String

JR

 

[Sniper-BoOyA-]

OMG OMG OMG It worked

Thanks a lot man! I couldnt have done it without your help.

 

[JANR]

Glad to help. Good luck with the rest of your project.

JR

 

[Sniper-BoOyA-]

Good morning,

Ive been working on the database over the last couple of days and i made a form where people from the lab can enter data..

Currently, the person using the database has to choose his/her initials from a dropdownmenu.And everything will be put into tables.

Since the string strEmpName is working now, i could change the dropdownmenu to a regular text box and define it as strEmpName

texboxname = strEmpName

But how will that affect the rest of the 'data entry' process.. The initials of the person who logs in the database wont be saved, right ?

Or should do you suggest a different way of doing this??

 

[JANR]

As long as the textbox is unbound, then it won't be saved to table.

JR

 

[Sniper-BoOyA-]

Right, so is there a way to link a table record to the strEmpName?

Cause right now i have a table with data-entry of labresults, and at the end of that table i have the Initials of the person who did the tests, named Laborant.

Currently Laborant is linked to tblLaborant which is a table with all the personal information, including initials.

But now that i use a login form, i either have to save the result of the dropdownmenu into a table, or i'd have to link the me.cboemployee.Column(1) to Laborant in the table...

What do you suggest is the best way? And how should i do it?

 

[JANR]

If you put your Unbound textbox on your form with the controlsource:

= strEmpName

And create a subform of your labresults table. Then Link the Master/Child properties to this textbox.

MasterField: NameOfTextbox
ChildField: NameOfForeignKey field in labresults table

You don't have to show the childfield but just include it in the recordsource.

The problem is that you have a text field as a foreign key and that is not prefreble, you should use the NumberID of the laborant instead.

You can do this in 2 ways.

1. Make a public variable like you did before in the loginform.
2. Create a hidden textbox on your parentform and on the loadEvent populate this textbox using Dlookup to find the laborants ID.
This textbox will be the MasterRelationship with the subform.

Private Sub frmLabResults_OnLoad()
Me.LaborantID = Dlookup("LaborantID", "tblLaborant", "[Laborant]= '" & strEmpName & "')
End Sub

JR

 

[Sniper-BoOyA-]

Well,

I have a form called Bitumen, with dataentry from labtests with Bitumen(doh).

And in order to gather all the information needed i made a query which combines 2 tables.

Table Bitumen
and Table Labgegevens (Labdata)

The primarykey is Labnr which is a unique number for the tests in the lab.

In the table Bitumen theres a record called Laborant, where the initials of the laborant who did the test in question will be entered, using a dropdown menu on the form Bitumen.

That dropdownmenu has tblLaborant as its source, and tblLaborant is a linked table (Dont know the english word for gekoppelde tabel).

But that is just there so u can see who has done what.

Ive added a textbox on the form Bitumen, and in the VBA code i defined it as

Private Sub Form_Load()
Afkorting = strEmpName
End Sub

So far so good, but the strEmpName wont be saved in the table Bitumen with the rest of the lab results.

If i can find a way to define the table-record Laborant with strEmpName or atleast let them have the same Value, then i can remove the dropdownmenu which is currently being used.

Sorry for my bad explanation, i would attach the database, but it has gone over 6mb so i cant upload it.
-------------------------------------------------------------

Anyways, to reply to ur previous post, giving the unbound textbox the controlsourde =strEmpName didnt quite work. The result was ' #name?? '

And to be honest i have never workd with subforms before, but i gave it a shot, added the subform, and selected Laborant from queryBitumen, but i couldnt find anything about Masterfield or Childfield.

Anyways, Ill keep on messing with it (think ive had to restore the file 18 times now ).

edit: Ive stripped the database, and its a lot smaller now. I hope this will help you getting a better idea this way.

 

[JANR]

I coulden't open your db, but to get a return value from strEmpName you create a function in the same module and then you can use the properties of your contol on the form to access it.

Function fGetstrEmpName() As String
fGetstrEmpName = strEmpName
End Function

So in the controlsource you put:

=fGetstrEmpName()

I have put together a VERY simple db on 2 diffrent metodes. One with a Subform and one without. Look at the difrent properties of the form objects, also very little VBA required to make this work.

With Subform's as would be the best approach there isen't any VBA involved exept to get the username populated.

JR

 

[Sniper-BoOyA-]

Thanks man, ill have a look, thanks again for ur help.