Hi, Ryan.
First things first:
- In a relational database application, it’s a good idea that every table
have a primary key, so add one to Action_Resources. An AutoNumber is most
convenient.
- I’m surprised that the Actions table doesn’t have an Action_Name field to
describe the action. Presumably, you’d want to display this rather than a
code on your forms & reports.
Answering your questions:
- You only need one combo box because all you need to *store* in your table
is the resource ID. This is a “foreign key†because it relates to the
primary key of another table. The ID fully defines the selected record.
What is stored differs, though, from what is *displayed*. A combo box
performs more than one task. When a selection is made, it stores the data
from the selected row from the column specified in the Bound Column property
into the field specified by the ControlSource property. It then *displays*
the first non-zero-column width column. The RowSource I defined has two
columns--the ID and a calculated field made up of the first and last name.
- Your question about whether to include the “ActionID†in your subform gets
at a subtle distinction between fields and controls, which is often confusing
at first—it certainly was to me. ActionID is a *field*, which only exists in
a table. It has a data type—Integer, Text, Date/Time, Yes/No, etc.
Form textboxes, combo boxes, etc. are *controls*, and have no data type;
they are merely containers. If they are bound to a field in the form’s
RecordSource, then data entered into the control will also be saved to the
field. So if you’re question was if you needed to add a control bound to the
ActionID, the answer is No. The subform already *has* the ActionID in a
sense, because you’ve linked the subform to the main form by this field.
Access will automatically add the ActionID to the child record.
- With your table and field names, the RowSource SQL is:
SELECT Resources.Res_ID, [Res_lname] & “, “ & [Res_fname] As FullName
FROM Resources
ORDER BY [Res_lname] & “, “ & [Res_fname];
Hope that helps.
Sprinks
ryan_eng said:
Sprinks,
I think I'm almost there but I'm still a bit confused with your solution.
Let me just show you exactly the fields I'm working with:
Table: Resources
Fields: Res_ID (primary key), Res_fname, Res_lname
Table: Actions
Fields (relevent): Action_ID (primary key)
Linking Table: Action_Resources
Fields: Resource_ID, Action_ID
You said I need only one combo box but I'm not sure how I get both the
resource ID and the employee name in it with that SQL expression. Can you
just clarify it with the table/field names I have listed so there is no
confusion? Also, I'm assuming that Action_ID has to be included in the
subform so is it ok to just throw it in the header so it isn't shown?
Thanks for the prompt response!
Ryan
Sprinks said:
Hi, Ryan.
Base your subform on the ActionResources, and link it to the main form by
the ActionItemID.
You need a single combo box. You'll want to *store* the ResourceID but
display the name. For LastName, FirstName format, the Row Source would be
something like (adjust field and table names as appropriate):
SELECT Employees.EmpID, [LastName] & ", " & [FirstName] AS FullName
FROM Employees
ORDER BY [LastName] & ", " & [FirstName];
Set the Bound Column to 1 to store the EmpID, and the ColumnWidths property
to 0";x to hide the key field from view and display the name after selection,
where x equals a number large enough to display the widest name. Set
ColumnCount to 2.
Sprinks
:
Hi all,
I have a table that stores "resources" (employee first name, last name and
unique ID) and another table for action items. Since an action item can be
assigned to more than one "resource" I needed a many to many relationship
which I achieved by making a third table which simply stores Action Item ID
and Resource ID. So far so good.
I created a form for entering new action items. Most of the required data is
obviously stored in the action item table, but for the resources allocated to
the action item, I need a subform that allows entry of multiple resources.
All I need the subform to show is basically one column for the entry of the
resources and I can't seem to achieve that. I want this column to comprise of
combo boxes that allow the user to select a resource by the employee name,
each time having the related resource ID and main form action item ID stored
in the many-to-many linking table.
I hope I've been clear. If you need me to clear anything up I'll be happy to
do so!
Thanks
Ryan
