login problem

[cbhavsar]

hello everybody,

I am new with access and vba. I am creating a project where i have two type of user one is 'Admin' and another is 'User'. Now when anybody try to login into database , database should look up automatically into Usertable and find the username and its user type. I have tried this following code;

Dim strPW As String
Dim strUser As String
Dim vFound As Variant
 
EmployeeName.SetFocus
strUser = EmployeeName.Value
 
 vFound = DLookup("[UserName]", "Users", "[UserType] = 'Administrator'")
 uFound = DLookup("[UserName]", "Users", "[UserType]= 'User' ")
 
 
 If vFound = EmployeeName.Value Then
 stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
  End If
 
If uFound = EmployeeName.Value Then
 stDocName = "Welcom1Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 End If
 

But the problem it is just locate the first record with Admin or user, even it does not check other records for right username with its usertype from User table to login correctly
please help me with this it is really urgent i have to submit in two days at my internship.

[Denburt]

here was one members answer:

dim s as string
on error resume next
s = dbengine(0).users("Fred").name
if err.number <> 0 then s = ""
on error goto 0
 
if s = "" then
' user not defined.
else
' user is defined.
end if
 

Found Here

[cbhavsar]

Thank you for your Answer

But I think what i am looking for is something different let me expain again. I have user table , it has three different column. These columns are UserType, Username and Password. Now the UserType can be either Admin or User.

Now when anybody open my database they have to enter their user name and password, upon entering their username and password the database automatically check for matching username and password, once it finds the matching username and password , the database looks for usertype in the table if it usertype is Admin then open Admin form and if user type is User then open User form. Here is the code, but it does not look beyond the very first record, i would really appreciate if some one can help me, i have to submit on my internship within two days.
Dim strPW As String
Dim strUser As String
Dim vFound As Variant


EmployeeName.SetFocus

strUser = EmployeeName.Value



vFound = DLookup("[UserName]", "Users", "[UserType] = 'Administrator'")
uFound = DLookup("[UserName]", "Users", "[UserType]= 'Guest'")


If vFound = EmployeeName.Value Then
stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
If uFound = EmployeeName.Value Then
stDocName = "Welcom1Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria


End If

[MMcCarthy]

As this is an internship project I can't write the code for you. Check out the posting guidelines in the FAQ. However, there are a couple of things I can point out.

1. Be careful to declare all variables that you use. vFound is declared but uFound is not.
2. DLookup can only return a single value. You are trying to return all users of a particular UserType. Since you have the UserName then look up the UserType for that UserName and open the appropriate form based on the returned UserType. This way you will only need one DLookup statement and one variable.
3. Allow for a null return on the DLookup by using the NZ() function.

NZ(Dlookup(.......), "Value to return if Null")

Hope this helps.

Mary

[cbhavsar]

Hello Mary,

I am still facing the same problem , i am still getting stuck with first record. Do you have any other suggestions

[Rabbit]

Let me offer a new persepctive. You seem to want to check to see if the text inputted by the user exists in the table and then to see if they are an admin or a user.

If so, you are going about it the wrong way with DLookup, for the first part anyways. DLookup is used to return a field of a record, not to check for it's existence among a group. You may want to take a look at DCount.

[MMcCarthy]

Hello Mary,

I am still facing the same problem , i am still getting stuck with first record. Do you have any other suggestions

This is what I meant ...

 
vFound = DLookup("[UserType]", "Users", "[UserName] ='" & strUser & "'")

This will return the UserType of the current user rather than all users of a usertype.

Mary

[cbhavsar]

Thank you

It is working now. I have another question regarding Access.

I have two forms. One is main form and another is subform. I have two fields in main form and subform has few other fields and also a command button to refresh it. I can refresh the field of submain with the following code
Form1.ActiveForm.Refresh();
But how can i refresh field of Mainform with the help of subform command button. Please give me some suggestions. I would really appreciate it.

[cbhavsar]

i am not sure about html, i am using for VB and it is entirely different story i guess

[MMcCarthy]

Try this ...

 
Forms!MainFormName.Refresh();

[cbhavsar]

Hello EveryBody,

I am developing application where i have field called box no ,and when i save box no field , it goes to column of Users. Now, my problem is when i edit the box no from 30 to 20 with the help of Edit method, It makes change with only first record it finds in table. I have many dulicate records with the same boxno, i want to change it from 30 to 20 with one click of command button. Is it possible
Please help me