Value Not in List- Twist!

[ksgirl]

This might sound kind of strange, but I was wondering if there was a way to check if an item is "Not in List" without using a combo-box. Here is my situation: I have a field 'Employee ID' on a form, where the employee enters in their own ID number. My problem is that I need something that will check the employee table to see if the number exists and display a message like "This is not a valid employee number" if an error occurs.....but, I don't want a combo-box. The reason for this is because I don't want the employee to be able to click the down arrow on the combo-box to see all the different numbers that are in the table, so that they would be able to just pick whoever's Id they want. The employee should only know their own ID to enter into the field. The combo-box works great except for that fact, but I still think there might be a way to do it without using a combo-box. I've been searching through the forums but can't seem to find anything similar to this problem.....does anyone have any suggestions?

 

[ColinEssex]

Hi

One way (not necessarily the best perhaps) is to write some code so that after the number is entered in the field, the code loops through the recordset to check its valid - if it isn't then display a messageBox and get them to choose another number.

If this is ok, you can email me and I'll knock you up a sample if I can.

Someone may have a better solution though

Col

 

[ksgirl]

Sure!!! I'd like to have a look at it! Anything is worth a try for me! Thanks!

 

[Jeff Bailey]

Put this code in the Before Update event procedure for the text box that holds the EmployeeID (I'm assuming a name of EmployeeID for the table field holding the ID numbers)

Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset("NameofTableHoldingEmployeeIDs",dbOpenDynaset)

***note: Set etc. as far as ... dbOpenDynaset) is all one line

rs.FindFirst "[EmployeeID] = " & Me!TextBoxName
if rs.nomatch then
msgbox "not a valid EmployeeID"
Cancel = True
end if


If the EmployeeID is not a numeric field you'll need a slightly different code in one line ...

rs.FindFirst "[EmployeeID] = " & Me!TextBoxName becomes

rs.FindFirst "[EmployeeID] = " & chr(34) &Me!TextBoxName & chr(34)

 

[ksgirl]

I think this might work, but I am getting an error!.....

rs.FindFirst is highlighted.....and the error says "Compile Error. Data Member not found."

I'm not sure what this means! Any suggestions?

 

[Pat Hartman]

What you've asked for won't really achieve your objective. What is to prevent someone from just trying other numbers? EmployeeIDs tend to be assigned in a sequential manner so knowing your own would give you a starting place for a search.

If you want to restrict people to looking at only their own records, use some other more personal way of finding a record such as SSN. It is less likely that a person would get a "hit" by trying random numbers. Or, establish a userID/Password to restrict access.

Another suggestion is to not show any identifying information on a form that shows sensitive data. So even if someone happens on a number that brings up information, they won't know who's information it is.

 

[ksgirl]

Thanks for your reply Pat, and I do agree with you mostly. Let me explain where I'm coming from. This form is where a user enters information into the form to be saved in the tables. On the form is the "Employee ID" field, where the user has to put there ID in, so for that record there is an account of who is placing the information. That is really all the field is used for. There only 15 or so people that will be able to use this database, out of the 500 some people (and yes their ID's are in sequential order) who work here. So of course their employee number can range anywhere in this range. For instance let say there are employee's with the following numbers (104, 276, 423, 300, etc....) that will be able to use the database.... Do you get my drift? So.....if by accident they are flying through the form and accidently type in 105 (by hitting a wrong key) then there would be a safety check on the form to tell the user that the Employee ID they entered is invalid. I know the combo-box would work, and yes all the employee ID's in the table would be exposed for them to pick which ever one they wanted, but I was just wanting something that would let the user know that the ID they typed in couldn't be stored....etc....
I hope this makes more sense?
Any ideas now?

PS. Thanks Pat!

 

[Tim K.]

I guess you are using A2K or higher. Just add the red part below.

Dim rs as DAO.Recordset

 

[ksgirl]

Thanks, Tim! That totally slipped my mind..... I feel so stupid now.... I should have thought of that myself!! Thanks again!

 

[KelMcc]

Looks like you may have it solved, but here's my simplistic idea... (I tried to post this earlier, but the BB is pretty slow for me today)

---I'm pretty new to access, but I think your answer is easier than you think.

I have a db where I want to make sure that people are using the correct employee names (whereas you care about Employee ID). So, for instance, on one form I have a field called "Employee" where the person picks types in there name. If it doesn't match the list of names in the table it links to, they get the generic Access error of "You can't add or change a record because a related record is required in the table BA".

This is because a have a table called "BA" which is my masterlist of employees. All my tables that use employee names is linked to the masterlist table for validation.

So, if you have a table, call it EmpID, then establish links to all your tables that will use EmpIDs, you can validate this pretty easily.

The only thing I can't do for you is tell you how to get a unique message.

I hope this has helped and I haven't oversimplified your problem.