?
Solved

Another Access 2007 Question on Error Messages to appear.

Posted on 2012-09-13
2
Medium Priority
?
342 Views
Last Modified: 2012-09-17
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
0
Comment
Question by:jjc9809
2 Comments
 
LVL 10

Assisted Solution

by:Anthony Berenguel
Anthony Berenguel earned 1000 total points
ID: 38397557
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
 
LVL 6

Accepted Solution

by:
fhlio_admin earned 1000 total points
ID: 38397567
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question