Textbox value to loop through table and find match

[Miff3436]

Hi All,

I have an MS Access 2010 db that has a main form called switchboard. This has 4 command buttons that open diffrent forms. Also on the main switchboard form i have an unbound textbox called TxtUserName that captures the users environ"username" when the switchboard form is opened.

I have a table called "tblAccessUsers" that i manually enter who i want to use my db. This table will have up to 50 names added to it. Their is only one field name in this table and it is "User Login".

When the user hits any of the commandbuttons on the main switchboard form i need some code that will look at the value in TxtUserName and loop through tblAccessUsers for an exact match. If it finds a match then it will carry out the open form command or if not prompt the user with a message box.

My knowledge of Access and especially VB is quite limited. I managed to create this using a DLookup but that only returns the first record in the table. The logic works but it will not look past the first record.

Thanks for your time

miff3436

 

[spikepl]

For reasons unknown you totally ignored my answer to this same issue in your previous
question.

The way this works is that the helpee asks a question and the helper attempts to provide an answer. Then the helpee is then supposed to ask if there is something that the helpee does not understand in the answer provided. Just ignoring it does not move the issue forward.

So one more time: find and read the description of DLookup in the documentation. Pay attention to the criteria for selecting what you wish to look up. Use google or use the Access online help activated by F1.

 

[Miff3436]

I did not "JUST" ignore your answer from the previous question i did not get what you were saying, apologies if i am no where near your level but as stated i am quite new to Access, surely you had to start somewhere !

The reason i use forums is because the help functions are hard to understand for us mere beginners. I always look through google first and use forums if i cant find what i am trying to achieve. Normaly they are very helpful places.

 

[DennisOJensen]

[FONT="Courier New"]DLookup("[UserLogin]", "tblAccessUsers", "UserLogin = " + lblUserName.Caption)[/FONT]

Note you should not have spaces in your field names so I removed it in the above rendition of how to implement your request. Second you should not use a Textbox to show the person logged in as a Label works just as well but cannot be mucked with.

Current User: lblUserName

The above is just basically 2 labels one for the "Current User:" text and the other that holds the name of the current user.

Now the above DLookup which is a Query is the same as doing the following

[FONT="Courier New"]SELECT UserLogin
  FROM tblAccessUsers
 WHERE UserLogin = lblUserName.Caption[/FONT]

The result set of this Query should only be 1 record the record you are looking for. If you do not get a result then the User does not exist in your table.

I hope that cleared up the confusion but as spikepl said you should really try googling the functions and or googling examples of the function there are plenty of them and they do outline how to use a DLookup as well as how a DLookup works As it is -- it appears that you are too lazy to do your own work and want to be spoon fed the answers -- if this is not the case than feedback and such is always a very good indicator that you are trying to at least understand the answers