If we are talking about the same field I have been calling Employee ID that
you are calling Applicant ID, then yes, it is the correct field. Why you are
getting that message, I don't know.
When does the message appear?
:
I have created the subform and inserted it into the main form. But when I
view it, I get a box that says "This expression is typed incorrectly, or it
is too complex to be evaluated. For example, a numeric expression may
contain too many complicated elements. Try simplifying the experssion by
assigning parts of the expression to variables."
Note: When establishing the subform in the main form, I I defined the
applicantID as the field linking my main form to the subform. Is this the
problem?
:
There is no many to many relationship. That is what your Employee Job Skills
table is for, to resolve the many to many. So, the relationship is:
Employee Applicant is one to many to Employee Job Skills
Based on EmpID
Employee Job Skills is one to many to JobSkill Categories
Based on SkillID
First create your sub form stand alone with the query as the record source.
Then in design mode for the main form, use the subform/report icon to place
and size the area for the sub form, and identify your subform as the form to
be incldued as the sub form.
:
I had not defined the "Job Skill Description" in the "Job Skill Categories"
Table as a Lookup combo box with the Row Source Type "Table/Query" and Row
Source "Employee Job Skills".
Then, I defined my one-to-many relationships as:
ApplicantID in "Employee Applicants" table to ApplicantID in "Employee Job
Skills" table
SkillID in "Job Skill Categories" table to SkillID in "Employee Job Skills"
Howis the Many-to-many relationship defined?
Let me know if that is what I should have done.
Then, tell me how I should create the subform? Should I go to the main form
and drag the subform icon to the detail section? If so, should I base it on
the query? If so, should I include all fields from the query? Is so, do I
change one of these fields, like "Job Skill Description" to a combo box? Or,
should I add a combo box to the subform? When I tried this, I used the query
for the values of my combo box. Then, I defined the applicantID as the field
linking my main form to the subform. When I tried to view the subform, a box
appeared that said I had typed the expression wrong.
:
No, you are exactly on track.
You can just change the row source of the combo box to be the Skills table.
Then create the text box and identify the field in your query as the control
source.
You have defined the tables precisely as they should be. Good work. And
yes, the junction table is the Employee Skills table.
:
Thank you for the clarification. I think my junction table is what you call
the "Employee Skills" table.
What you call the Skill table is named "Job Skill Categories". It has two
fields:
SkillID - autonumber - root key
Job Skill description - text
My junction table is now renamed to "Employee Job Skills". It has the
following fields:
SkillID - number
ApplicantID - number
Then I created a query based on the "Employee Job Skills" table and added
fields from the main Employee Applicant Table.
So far, so good. Now I tried to create a subform called "Job Skills" in my
main form
called "Employee Applicants". I based the subform on the query and defined
the relationship between the main form and the subform as ApplicantID.
Now, how do I create a combo box in the subform? Do I change the field "Job
Skill Description" to a combo box? If so, how do I make it unbound? When I
tried this, the properties for the field changed to a combo box said it is
bound to column 1.
What am I doing wrong?
:
No, remeber what I said about putting the description in the Employee Skills
table. It should be in the Skills table.
Your main form should be based on the Employee table.
Your subform should be based on the Employees Skills table.
For your subform, create a query that would be based on the Skill table and
the Employee Skill table. In that query you would need:
Skill![EmpID]
Skill![SkillID]
Skill![SkillDescription]
EmpSkill![EmpID]
and whatever other fields you have in the Employee Skill table.
Also on the subform your combo box would be unbound and visible only when
you want to add a new skill for the employee. The row source should be the
Skill table. It would have the SkillID and SkillDescription as I described
earlier. Then when you select the skill, in the After Update event of the
combo box, put the SkillID value in the bound text box for the SkillID. You
will get the value for Skill![EmpID] from the main form.
I know this all seems confusing now, but follow it through and it will make
sense once it is all done. This will be a valuable lesson because this is a
very common situation you will run into a lot.
:
I think I need to go back to the drawing board. I created a form from data
in the Employee Applicant Database. I then attempted to create a subform to
lookup the job skills for each employee and choose from a drop down box all
that apply. This I expected would update the Junction Table with records
relating the ApplicantID with one or more skillIDs.
The subform uses fields from the junction table. Is this right? I had to
add a text field named "job skill" to this table to show the related skill in
the skills table. I don't know how to define this field so it knows where to
get the proper text though.
Maybe I should use the skills table. But then how do I define which fields
link the main form to the subform?
I just thourghly confused.....but I really appreciate your patience with me.
:
No Problem. You can have multiple columns for a combo box. The bound column
should be the SkillID because that is what you will use to retrieve it from
the database. The column with skill can be the one the user sees. Let's
assume you make column 0 the SkillID and column 1 the skill. Set the column
width for column 0 to 0, and for column2 set it wide enough to see the entire
description. So now, if you reference Me.cboSkill, it will return the value
of the SkillID for the selected row of the combo box. You will get the same
results with Me.cboSkill.Column(0). Me.cboSkill.Column(1) will return the
description.
(you are working hard at this, aren't you?)
:
I have two fields in the skill table. SkillID and skill. SkillID is an
autonumber and skill is a text field describing the skill i.e. clerical,
accounting, housekeeping, grounds worker, etc.
I want to create records for the junction table by selecting from a list in
a form. I have created a subform to accomplish this. The problem is that
the drop down box shows me the skill ID to choose from instead of the field
with the skill description.
:
Depends on what the record source for the sub form is. It should probably be
a query that has both the Employee Skill table and the Skill table included.
Then make the control source for the skill description whatever field has the
description. If you are considering putting the description in the Employee
Skill table, DON'T! That would violate one of the basic rules of database
normalization.
:
How do I make the skill description show in the subform in the combo box?
:
I would think all your ID fields would be autonumber.
The Junction table (Employee Skills Table) does not need anthing else. You
can make its primary key the combination of the two fields.
This table is only used to group the skills the employee has and keep track
of information about that skill for the employee. For example, you may will
have a Skill Level. It would go in the Employee Skills Table.
So, when you want to look at the skills an employee has, you would navigate
to that employee's record. Your Employee Skills table would then appear to
be filtered on that employee. The description for the skill would be in the
Skills table.
:
I completed the tables as you indicated. Now the junction table only has two
fields. What is the data type for each of these fields? Number? Do I need
a root key for the junction table?
:
Employee table needs EmpID and EmpName
Skills table needs SkillID and SkillDescription
Employee SkillsTable needs EmpID and SkillID
Create a one to many relation between Employee table and Employee Skills
table on EmpID
Create a one to many relation between Employee Skills table and Skills table
on SkillID
:
I have a table for employee applicant data that includes an AutoID field for
the ApplicantID. I want to identify which skills each applicant has. I have
added a table called "Skills" with an AutoID field for the skill number and a
text field with the name of the skill. I have also created a junction table,
but I'm not sure it is set up correctly. The junction table has a field
SkillID as data type AutoNumber and ApplicantID as a data type number. I
have a third field called ApplicantSkill. How do I set up the many-to-many
relationship so each applicant can select one or more skills?
