To subform or not to subform 1

[nastar1]

How best to display multiple records in one table on a form using a master table.

Say I have a table with construction job related fields like:

JobID
JobName
JobLocation
JobTools

And another table with only the types of tools like:

ToolID
ToolType

I'd like to create a form using the JobID table and for each record in that table also display on the form all the related ToolType from the ToolID table.

Is there an easier way to do this than to use the SubForm?
I ask because I'm having trouble getting a grip on how to make the subform work.

Thanks

 

[nastar1]

In case it helps, I expect that the main form will typically only have 1 to 5 ToolType elements to display per Job.

I'd prefer to display a text box on the main form that would display any and all ToolType entries rather than use a subform.

 

[PHV]

What about a ListBox ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nastar1]

The Listbox would be able to provide the dropdown with all the possible ToolTypes from the ToolID table.

What happens if for instance, the user wished to select all five ToolTypes in the Listbox? How would they be added to the Form and underlying tables, so that if they accessed the JobType record again, all of those added ToolTypes would be displayed?

Basic stuff I'm sure, but I visit Access so infrequently, I lose any frame of reference.

I've already got a one to many relationship between the JobID table and the ToolID table, since any one job could have many Tool types assigned. But I'm going blank from there.

Thanks

 

[PHV]

I've already got a one to many relationship between the JobID table and the ToolID table
The standard way is main form with a linked subform.
Provided the relationships are properly set, silmply follow the form wizard.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GingerR]

Ok, you're talking about Many-to-Many relationships.
You can have many tools for a job, and a tool can be on many jobs, right?

So, you have your current Job table (needs no reference to ToolTypes).
And you have your ToolTypes table.

You need a third table, JobToolTypes.
It has two fields, JobID and ToolTypeID.

JobID ToolTypeID
1 4
1 6
3 6
3 7
3 8

Make a form from this table, make it a datasheet and call it JobToolTypes. In the DESIGN mode, make the ToolTypeID text box be a combo box instead: make it's rowsource be the table ToolTypes, with the index (ToolTypeID) width of zero and the ToolType (text, I presume) be an inch or whatver. Save and close that form.

Plop that new form into the main form. When you do, a wizard should come up asking how to link the "Master" and "Child" forms: pick JobID. If the wizard doesn't come up, look at the properties of the subform and set the Master and Child properties to JobID yourself.

So....you open the "main" form, which has a JOB and related JOB info on it. The subform shows all of the TOOL TYPES for that job. To add another Tool Type, you pick from the drop-down list and because you have the Master/Child links set, it automatically adds that Tool Type to the third table for that particular Job ID. Get it?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nastar1]

Thanks, that sounds like what I am trying to do. I'll give it a try tomorrow.

You are correct, my mistake, a many-to-many relationship is what I have on my hands.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nastar1]

I decided against using a subform. The best option for what I was trying to do was using a Listbox that would allow for writing multi-select items to a textbox. I was only trying to capture a single field with many possibilities that could be written to a text box.

Thanks for the tips.