Filling In Data on Continuous Form

[Guest]

Using Access 2000, I want to fill in the employee name on a particular line
of a continuous form after entering the employee number on that line. When I
look up the value in the employee table, and enter that value (now
strEmployeeName) into the control with the following:

Me.EmployeeName = strEmployeeName

the values for all the employee names in all lines on the form change, and I
just want to change the value on the one line. Is that possible? How can I
do it?

Any help would be appreciated

Thanks

 

[Allen Browne]

The answer as to how to do this in a form will depend on how your tables are
set up.

Presumably you have an Employee table, with fields EmployeeID (primary key),
Surname, FirstName, etc.

Then it sounds like this continuous form is bound to another table that has
EmployeeID as a foreign key (e.g. for that employee's shifts or sales or
....) If so, storing the EmployeeID is the right approach, but it is
important that you do not add the employee name fields to this table as
well. Instead, you have Access look up the name for you, and you never have
to worry about it being wrong.

To do that, create a query using both tables. In query design, add the *
from your important table to the query grid, and add just the name field(s)
from the Employee table. Save the query.

Now open your form in design view, and set its RecordSource property to this
query. You will now be able to add the text box(es) you want for the
employee name field(s). You will want to set the Locked property of these
text boxes to Yes (so they cannot be accidentally changed.)