Newbie doesn't understand Expression syntax - Help please

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

I haven't found anyplace that really explains how to
write expressions, so some general logic would be helpful.

For example, let's say I have a form called FrmA. In it,
the user enter selects a name from a combo box that is
based on a query:

SELECT qryEmployees.EmployeeID, qryEmployees.FullName
FROM qryEmployees;

The EmployeeID is stored in TblA in a field called
EmployeeID.

On FrmA, I want to get the phone number from tblEmployees
that correspondes to EmployeeID, and display it on the
form.

So, how do you write the expression to do that: Help
doesn't explain exactly how to do it (at least in a way I
can understand). It would be nice to see the actual
expression for my example, along with an explantion of
why it is written the way it is, so I can do similar
things in the future.

Many thanks for your patience,
Christine
 
I haven't found anyplace that really explains how to
write expressions, so some general logic would be helpful.

For example, let's say I have a form called FrmA. In it,
the user enter selects a name from a combo box that is
based on a query:

SELECT qryEmployees.EmployeeID, qryEmployees.FullName
FROM qryEmployees;

The EmployeeID is stored in TblA in a field called
EmployeeID.

On FrmA, I want to get the phone number from tblEmployees
that correspondes to EmployeeID, and display it on the
form.

So, how do you write the expression to do that: Help
doesn't explain exactly how to do it (at least in a way I
can understand). It would be nice to see the actual
expression for my example, along with an explantion of
why it is written the way it is, so I can do similar
things in the future.

Many thanks for your patience,
Christine
Click to expand...

As the control source of an UNBOUND control on the form:
=DLookUp("[EmpPhone]","tblEmployees","[EmployeeID] = " &
Me![EmployeeID])

To verbalize it:
Look up the Employee Phone number in the Employees table where the
EmplyoyeeID matches the EmployeeID on this form.
 
Many thanks, Fred, for your quick response! I still seem
to be missing something. I modified your code ever so
slightly:

Yours:
=DLookUp("[EmpPhone]","tblEmployees","[EmployeeID] = " &
Me![EmployeeID])

Mine:
=DLookUp("[Phone]","tblEmployees","[EmployeeID] = " &
[Me]![OriginatorID])

You see that I have stored the EmployeeID in a field in
the table and form as OriginatorID. This is because the
table will eventually have a number of different
EmployeeID's stored in various fields, such as ManagerID,
SupervisorID, etc.

When I run the form, the unbound Phone field on the form
says "#Name?"

There's something I still don't understand!
Cheers,
Christine
 
You may have normalization problems. Possibly you want tables 1) Employee
and 2) Role (or something similar to identify the multi-level heirarchy).
You might also have 3) Phone and 4) PhoneType tables (Work, Home, Fax, Cell,
Shoe, etc.). Then set up form - subform to handle there parent:child,
one:many relationships. You'll also need tables to describe the
relationships Employee-Role and Employee-Phone and Phone-PhoneType.
 
Many thanks, Fred, for your quick response! I still seem
to be missing something. I modified your code ever so
slightly:

Yours:
=DLookUp("[EmpPhone]","tblEmployees","[EmployeeID] = " &
Me![EmployeeID])

Mine:
=DLookUp("[Phone]","tblEmployees","[EmployeeID] = " &
[Me]![OriginatorID])

You see that I have stored the EmployeeID in a field in
the table and form as OriginatorID. This is because the
table will eventually have a number of different
EmployeeID's stored in various fields, such as ManagerID,
SupervisorID, etc.

When I run the form, the unbound Phone field on the form
says "#Name?"

There's something I still don't understand!
Cheers,
Christine
Click to expand...

Did you start with a new unbound control, or did you simply remove the
control source of an existing control?
Make sure that the name of that control is not the same as the name of
any field used in the expression, i.e. if it is named "Phone" change
it to txtPhone, or whatever else you want.
Also, make sure you have correctly spelled all the field and table
names used in the expression.

Your new expression, as written, is correct if [EmployeeID] is a
Number DataType, and [OriginatorID] is the name of a control on this
form.
 
I don't think you can use "Me" if you enter the expression in the
ControlSource row of the Properties window.

Try:

=DLookUp("[Phone]","tblEmployees","[EmployeeID] = " &
Form![OriginatorID])

or even simpler:

=DLookUp("[Phone]","tblEmployees","[EmployeeID] = " &
[OriginatorID])

HTH
Van T. Dinh
MVP (Access)
 
I don't think you can use "Me" if you enter the expression in the
ControlSource row of the Properties window.

Try:

=DLookUp("[Phone]","tblEmployees","[EmployeeID] = " &
Form![OriginatorID])

or even simpler:

=DLookUp("[Phone]","tblEmployees","[EmployeeID] = " &
[OriginatorID])

HTH
Van T. Dinh
MVP (Access)
Click to expand...
* snipped *

Van,
You are absolutely correct about not using Me! in a control source
expression.
I can't imagine how that got past me since I've pointed it out to
other posters often enough.

Thanks for your input.
 
Back
Top