Allow user to look up a record and only update that record

[PlumDingo]

Hi,

I am trying to do the following with my database. I have a database with a table named Employees. I also have a form named Employees that I use to enter information on the form. What I want to do is, only allow the user to see one record at a time. In other words, if they need to look up an employee than they run a report for many employees or use the Lookup form if it is only one employee. If they need to update the information for an employee, I want them to enter the Employee ID of the employee they are looking for and then the record and only that record will come up for them to edit.

In addition sometimes people will be lazy and not want to look up the Employee ID number so I want another form that will allow the user to enter the last name and first initial of the employee (because their first name may not be consistant in the database) and the form return the single record corresponding to that person. If there are multiple people found than, it gives the user a list of names for them to choose one of the employees and then it opens that corresponding record.

I am also trying for it to have the corresponding error messages if the information entered does not yield any results.

My Employee table has the following fields:

Employee Number Primary key
First Name
Last Name
Classification ID
Seniority Date
Supervisor ID

Can anyone help me on this.?

The basic idea is that they are coming in from a switchboard and they will click one of the buttons to update an employee. Then a dialog box should pop up requesting either the last name first initial or the Employee ID and from there the single record to be updated or the list of the matching employees should open.


France
Just trying to get by.

 

[mph1]

Good News!! - Everything that you want to do is possible. The following are several things to do, not in any particular order.

1. Disable the page up & page down keys -

http://www.mvps.org/access/forms/frm0016.htm

2. Disable the scroll wheel. There are various methods. Check the FAQ section here and also Microsoft Knowledge base.

3. On your switchboard, create a button and use the wizard to finish making a search button.

4. If you have a button on the Switchboard that when clicked will take you to the imput form, use the On Click event to go to a new record only. Example,
DoCmd..GotoRecord ac DataForm, "YourFormName", acNewRec

By disabling the keys and wheel, a user can't go to another record, they must use whatever navigation buttons you provide.
In addition, there are other ways to do a search, however, you will need to write code for this. It depends on you level of knowledge and design.

I hope this gives you a good idea on how to procede.


An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[PlumDingo]

It is the code that I am having trouble with. I was trying to use a DLookUp to find the records but it stops after the first match which doesn't mean that is the person they are looking for. I need something to search all the records in the table. THen I thought of a SELECT statement that would find the records that match the crieteria but I was not sure how to write it so that it parses the first initial out of First Name. I was trying to dump those results in a table somehow and use that to display it to the user. If there is more than one, than the user chooses one of the people. If there is only one than it shows one person and the user confirms that this is the person they are looking at.

I am not that good with the code and need some help to write it.

Thanks.

France
Just trying to get by.

 

[PlumDingo]

DOes anyone have any ideas on how to accomplish the above?

Thanks

France
Just trying to get by.

 

[PHV]

Have you tried a ListBox with a dynamically built RowSource ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244