Cross reference multiple fields in a form.

[Claire]

I am trying to create a database to keep track of employee timesheet
information. Their time may be applied to a job# (which always has a
description and a lead#), or to a lead# (which may or may not have a job#).
I have a table with the fields job# and lead#, and another which has job#
with its description (and multiple other fields). My goal is to have a form
where I can type all of an employees time for a week and have it recorded in
another table which can then be queried by job # or employee or the like.

So far the form is functioning as I wish, and is limiting job#s and lead#s
to those that are on the table.

My problem is as follows:
I would like to be able to enter the job# and have access pull up the job
description and applicable lead #
OR
I would like to enter the lead # and have access pull up the job# and
description if one exists
AND
it would be even better if I could type part of the description and have
access pull up the job# or lead#, but this is not necessary, just elegant.

Currently I have formed relationships so that I can put in the job# and it
will put in the description, or I can put in the lead# with a dummy job#
(which has a blank description).

Is access able to do what I wish or do I have to keep this patch?

Thank you so much!

 

[Allen Browne]

Could the lead change during the duration of the job?

If so, it could be valid data to say that Fred worked on Job 59 with lead
24, while Barney worked on Job 59 with lead 99. In that case, it would make
sense to allow both the job# and the lead# in the table.

If two people working on the same job under different leads would represent
bad data, then I suggest you block it. Open this table in design view, and
place a Validation Rule on the table so that the user must enter either a
job# or a lead#, but not both. The VR goes into the Properties box, not the
lower pane of table design (which is the rule for one field, not for the
table.) The rule will be something like this:
([lead#] Is Null) XOR ([job#] Is Null)
More info on validation rules:
http://allenbrowne.com/ValidationRule.html

You can create an expression in a query field to get the lead. This example
uses the lead# from the Timesheet table if it is available; otherwise it
uses the one from the Job table.
IIf([timesheet].[lead#] Is Null, [job].[lead#], ([timesheet].[lead#])

 

[Claire]

The lead can not change during a job. Pretty much, if I was able to
structure the company to the database, everyone would use the lead # because
it is really the parent. There can be leads without jobs, but a job must
have a lead (and it only has one). Sadly (for the database) once a lead
becomes a job the lead# should not be used.

I understand that I can pull up the lead in a query, but is it possible to
have access supply the lead # or description on the form? I can have it pull
up the description of the job, but if work is done for a lead that does not
have a job #, access still asks for a job #. Is it possible to have a
conditional lookup on a form, or one that looks up another field if it is not
blank, but otherwise allows you to enter a value?

 

[Allen Browne]

In the interface, you can design a field that *displays* the lookup value,
using a DLookup() expression. You can use it's Enter event to SetFocus to
another text box where the user can enter a value. Place the text boxes on
top of each other if it helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The lead can not change during a job. Pretty much, if I was able to
structure the company to the database, everyone would use the lead #
because
it is really the parent. There can be leads without jobs, but a job must
have a lead (and it only has one). Sadly (for the database) once a lead
becomes a job the lead# should not be used.

I understand that I can pull up the lead in a query, but is it possible to
have access supply the lead # or description on the form? I can have it
pull
up the description of the job, but if work is done for a lead that does
not
have a job #, access still asks for a job #. Is it possible to have a
conditional lookup on a form, or one that looks up another field if it is
not
blank, but otherwise allows you to enter a value?

Could the lead change during the duration of the job?

If so, it could be valid data to say that Fred worked on Job 59 with lead
24, while Barney worked on Job 59 with lead 99. In that case, it would
make
sense to allow both the job# and the lead# in the table.

If two people working on the same job under different leads would
represent
bad data, then I suggest you block it. Open this table in design view,
and
place a Validation Rule on the table so that the user must enter either a
job# or a lead#, but not both. The VR goes into the Properties box, not
the
lower pane of table design (which is the rule for one field, not for the
table.) The rule will be something like this:
([lead#] Is Null) XOR ([job#] Is Null)
More info on validation rules:
http://allenbrowne.com/ValidationRule.html

You can create an expression in a query field to get the lead. This
example
uses the lead# from the Timesheet table if it is available; otherwise it
uses the one from the Job table.
IIf([timesheet].[lead#] Is Null, [job].[lead#], ([timesheet].[lead#])

 

[Claire]

Allen,
Thank you so much for the help so far. This is my first time putting an
expression into a text box, which is really what I wanted to do, but I'm
running into an error.

I have entered:
=DLookup("[masterJobName]","Master","[masterJob #]=" &
Forms!HoursSubform!Job #)

Where masterJobName is the field I want to display, Master is the table to
look in, masterJob# is the field I want to compare, and
Forms!HoursSubform!Job# is the field on the form to be compared to the
masterJob#.

When I enter this I receive the error message:
The expression you entered has an invalid date value.

I have checked the format of the various elements, and nothing is formatted
as a date, so I'm not sure if this message is indicating something is still
trying to be a date, or if it's referring to any other problem.

Thanks for any advice,
Claire

In the interface, you can design a field that *displays* the lookup value,
using a DLookup() expression. You can use it's Enter event to SetFocus to
another text box where the user can enter a value. Place the text boxes on
top of each other if it helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The lead can not change during a job. Pretty much, if I was able to
structure the company to the database, everyone would use the lead #
because
it is really the parent. There can be leads without jobs, but a job must
have a lead (and it only has one). Sadly (for the database) once a lead
becomes a job the lead# should not be used.

I understand that I can pull up the lead in a query, but is it possible to
have access supply the lead # or description on the form? I can have it
pull
up the description of the job, but if work is done for a lead that does
not
have a job #, access still asks for a job #. Is it possible to have a
conditional lookup on a form, or one that looks up another field if it is
not
blank, but otherwise allows you to enter a value?

Could the lead change during the duration of the job?

If so, it could be valid data to say that Fred worked on Job 59 with lead
24, while Barney worked on Job 59 with lead 99. In that case, it would
make
sense to allow both the job# and the lead# in the table.

If two people working on the same job under different leads would
represent
bad data, then I suggest you block it. Open this table in design view,
and
place a Validation Rule on the table so that the user must enter either a
job# or a lead#, but not both. The VR goes into the Properties box, not
the
lower pane of table design (which is the rule for one field, not for the
table.) The rule will be something like this:
([lead#] Is Null) XOR ([job#] Is Null)
More info on validation rules:
http://allenbrowne.com/ValidationRule.html

You can create an expression in a query field to get the lead. This
example
uses the lead# from the Timesheet table if it is available; otherwise it
uses the one from the Job table.
IIf([timesheet].[lead#] Is Null, [job].[lead#], ([timesheet].[lead#])

 

[Claire]

Okay, I got myself through that last error, after reading that the brackets
help access with symbols/phrases in it's vocabulary. So, the last part that
includes Job# has brackets around it.

At this point, I no longer have an error, but every record says:
#Name?

The text book is now defined as:
=DLookup("JobName", "Master", "[Job #]=" & [Forms!HoursSubform!Job #])

Looking at your online help, it seems you concatenated the comparisons onto
strings, so I tried:
=DLookup("JobName", "Master", "[Job #]=" “â€& [Forms!HoursSubform!Job #] &
“â€â€ â€)

However this leads access to say:
The expression you entered contains invalid syntax.
You may have entered an operand without an operator.

Any ideas to why my lookup is not looking up?
I have a direct link field resulting in the master job name at the same
time, so I know that the fields do actually exist, and the result that I
*think* I'm telling access to result.

 

[Allen Browne]

Subforms are not open in their own right, so you can't just use
Forms!HourSubform.

Use the complete reference, such as:
[Forms].[YourMainFormNameHere].[HourSubform].[Form]![Job #]
or, if this text box is actually in the subform and so is [Job #], you can
just use:
[Job #]

It can be fun to figure out the right expressions to use. It is worth the
effort if you plan to use Access. Here's an explanation of the .Form bit
above:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

BTW, check the Name property of this text box as well. It must not have the
same name as one of the fields, or Access will get confused.