Another Access 2007 Question on Error Messages to appear.

Hi everyone,

I have problems writing some error messages and a thought about when someone retires or leaves and one has to get rid of the User listed in a table using VBA code.

On error messages,  I need an error message to appear, on a Login screen.  I want the user to enter their name as Last Name, First Name.  If they do not enter the name in this way, I want to give an error message and exit the sub.

How can I write VBA code to check entering of name this way in the textbox?

Along this same line on error messages, I want the Access Type Textbox to be "Admin" or "User"  if they put something else in the textbox, I want an error message to appear how can I check for this and exit the sub?

When a person retires or just leaves a job, I need a way to delete the user and password from a table holding this information.  How can I do this? Do I just delete the person out of the employee table manually?  I need a way to automate this?

Any help with these Access issues would be very helpful.

jjc9809
jjc9809Asked:
Who is Participating?
 
fhlio_adminCommented:
Wow... this is like a few questions...

First item:  Name... I assume that you have FirstName and LastName as fields in your table.
Here is some code..

Dim rst as DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT MYUSERTABLE.* FROM MYUSERTABLE WHERE MYUSERTABLE.LastName & ', ' & MYUSERTABLE.FirstName <> '" & txtUsername.Value & "'")

If rst.RecordCount = 0 Then Exit Sub

Open in new window


Second... Access Type... I suggest you change this to a combo box.  Use the Limit To List property as True.  Set the default value as "User".  Row Source Type as "Value List".  Row Source as: "User";"Admin".

Third... in building applications, I commonly will use something like a DELETE_SWITCH field as a deleted flag... It is just a yes/no value, but I can qualify it in my statements throughout the application.  This gives the ability to track even after a user quits.

You can change it by this code:
Dim rst as DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT MYUSERTABLE.* FROM MYUSERTABLE WHERE MYUSERTABLE.LastName & ', ' & MYUSERTABLE.FirstName <> '" & txtUsername.Value & "'")

If rst.RecordCount > 0 Then
  rst.Edit
  rst!DELETE_SWITCH = True
  rst.Update
End If

Open in new window


Of course if you do that, you should change the first code to as follows:
Dim rst as DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT MYUSERTABLE.* FROM MYUSERTABLE WHERE MYUSERTABLE.LastName & ', ' & MYUSERTABLE.FirstName <> '" & txtUsername.Value & "' AND MYUSERTABLE.DELETE_SWITCH = False")

If rst.RecordCount = 0 Then Exit Sub

Open in new window

0
 
Anthony BerenguelCommented:
jj,

+"How can I write VBA code to check entering of name this way in the textbox?"
Do you have a table in which the names are separate fields? If not there's really no way to tell if a string of text is a first name or a last name.  Furthermore, when you need to store people's names you should use two separate fields.

+I want the Access Type Textbox to be "Admin" or "User"
The user is entering 'Admin' or 'User' into a textbox? If so, this is not a good practice. At least give them a combo box (dropdown menu)  to choose the value -- combo boxen are a good way to validate data. But better than having the user choose 'Admin' or 'User' from a combo box, you can take a cleaner approach:

1) Set up a users table with the following fields
-username - text field to store usernames
-firstName - text field to store first names
-lastName - text field to store last names
-group - text field to store the user's group (e.g. 'admin', 'user', etc.)
-status - text field to store the user's employment status (e.g. 'active', 'inactive','terminated','LTD','STD', etc)

2) Then you can use environ("username") in your code to get the user's username -- the one they would use to sign in to their computer. Once you know the username you can use DLOOKUP to get retrieve the group (i.e. 'admin', 'user', etc.).

+"When a person retires or just leaves a job, I need a way to delete the user and password from a table holding this information. "
If you have a user table set up this way you would easily be able to take care of this problem by updating the user's status to the desired value.

As I'm writing this I realize I'm suggesting you restructure your database a bit. I apologize if this is not what you wanted to hear. However, I am willing to help you with the suggestions I have made.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.