Entering SELECT WHERE expressions

[Guest]

I have 2 tables named "Employee" and "Data". The "Employee" table has the
names and numbers of each employee. The "Data" table has a field to select
the employee number from a lookup list (from the "Employee" table). The
"Data" table also has another field to select the name. But rather than
having to select it, I want to enter an expression in the default value field
(or run a macro) to automatically select the name of whatever number was
selected in the "Data" table.

What is the correct syntax/formula to enter a SELECT WHERE formula? Or if I
enter it as a macro with a SetValue action, what should be entered in the
Item and Expression field?

 

[Dirk Goldgar]

In

I have 2 tables named "Employee" and "Data". The "Employee" table
has the names and numbers of each employee. The "Data" table has a
field to select the employee number from a lookup list (from the
"Employee" table). The "Data" table also has another field to select
the name. But rather than having to select it, I want to enter an
expression in the default value field (or run a macro) to
automatically select the name of whatever number was selected in the
"Data" table.

What is the correct syntax/formula to enter a SELECT WHERE formula?
Or if I enter it as a macro with a SetValue action, what should be
entered in the Item and Expression field?

If the employee number determines the employee name, then your Data
table really shouldn't have a field in it for the employee name at all.
Instead, you could do all your work with a query that joins the two
tables on the employee number field, and picks up the employee name from
the matching Employee record. This would be, effectively, an
"auto-lookup query".

For example, the SQL might be something like this:

SELECT [Data].*, Employee.EmployeeName
FROM [Data] LEFT JOIN Employee
ON [Data].EmployeeNumber = Employee.EmployeeNumber;

 

[Guest]

The information will actually be entered into a form that is linked to the
Data table. I want the form to display the name when the number is entered
so when we look back at the individual records in the form, we know who the
employees are.

Thank you for your help! I hope it works...it's been driving me crazy all
day!

In

I have 2 tables named "Employee" and "Data". The "Employee" table
has the names and numbers of each employee. The "Data" table has a
field to select the employee number from a lookup list (from the
"Employee" table). The "Data" table also has another field to select
the name. But rather than having to select it, I want to enter an
expression in the default value field (or run a macro) to
automatically select the name of whatever number was selected in the
"Data" table.

What is the correct syntax/formula to enter a SELECT WHERE formula?
Or if I enter it as a macro with a SetValue action, what should be
entered in the Item and Expression field?

If the employee number determines the employee name, then your Data
table really shouldn't have a field in it for the employee name at all.
Instead, you could do all your work with a query that joins the two
tables on the employee number field, and picks up the employee name from
the matching Employee record. This would be, effectively, an
"auto-lookup query".

For example, the SQL might be something like this:

SELECT [Data].*, Employee.EmployeeName
FROM [Data] LEFT JOIN Employee
ON [Data].EmployeeNumber = Employee.EmployeeNumber;


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[John W. Vinson]

The information will actually be entered into a form that is linked to the
Data table. I want the form to display the name when the number is entered
so when we look back at the individual records in the form, we know who the
employees are.

You can use a Combo Box on the form to select the employee, either by employee
number or by name (whichever is going to be more convenient for the user). If
you want to enter the number and display the name, use a Combo Box containing
both fields; put a textbox on the form with a control source

=comboboxname.Column(n)

where n is the zero based index of the name - e.g. 1 if the name is in the
second column.


John W. Vinson [MVP]

 

[Dirk Goldgar]

In

The information will actually be entered into a form that is linked
to the Data table. I want the form to display the name when the
number is entered so when we look back at the individual records in
the form, we know who the employees are.

Thank you for your help! I hope it works...it's been driving me
crazy all day!

If you base your form on an auto-lookup query such as I described, it
will do that automatically, and allow you to modify the employee's name
if it was originally entered wrong. (Note: if you don't want the name
to be updatable, you can lock that control). John Vinson's suggestion
of using a combo box both to choose the employee and as a source of data
for a separate control is a good alternative, but in the simple case you
describe, I like the auto-lookup query better.