Hi,
I tried setting them up the way you mentioned and I was able to get it to
work. Thanks a lot for your help.
Karl
"Karl" wrote:
> Hi Marshall,
>
> Thanks for your all your sugguestions. I'll give them a try at work
> tommorrow and let you know what happens. As to your point on the fundamentals
> of my subform. I see your point, I had originally tried it this way but was
> not able to figure out how to bind the products combo box to the product id
> field in components.
>
> I am new to forms and most have what I've learned is through trial and error
> and from these internet postings (which I've found very helpful) (i'm also
> taking a course on Access next week which will hopefully give me a better
> understanding of all this works, but I was hoping to get this database up and
> running before my course).
>
> As for the reason I set it up this way with the subforms is I thought each
> table would need its own subform. Ie. Categories, Products, and components as
> the only way I could figure out how to link them is through master and child
> fields. Originally this worked fine if combo boxes weren't used. However I
> wanted to add the combo boxes so a category could be chosen and then a
> product could be chosen based on the category. However like i said when the
> combo boxes (Categories and Products) were on the same from I couldn't figure
> out how to get components to link up to the product. The purpose of this
> database is to create an inventory or products that will be sorted by
> category. In the end I planned on adding more subforms for maintenence info,
> and purchase info for each components based on a component id. Hopefully this
> theory makes some sense to you. Do you think I should re-attempt to do it
> your way instead?
>
>
> I can not tell you how appreciative I am of all your help, as a new user it
> definately makes access a little less intimadating being able to get expert
> help so easily.
>
>
> "Marshall Barton" wrote:
>
> > Karl wrote:
> >
> > >I have 3 tables that are linked.
> > >
> > >Category Table:
> > > Category ID (primary key) -autonumber
> > > Category Name
> > >
> > >Products Table:
> > > Product ID (primary key) - autonumber
> > > Category ID
> > > Product Name
> > >
> > >Components Table:
> > > Component ID (primary key) - autonumber
> > > Product ID
> > > Equipment Name
> > > Make
> > > Model
> > >
> > >I have created a form (Form1) that has an unbound combo box that lists all
> > >the names of categories for all the products I have. I have a subform (Form
> > >2) with a unbound combo box for products. For this combo box on the products
> > >that are associated with the chosen category are shown. I would like to
> > >create a subform (Form 3) in Form 2 that shows the Component information of
> > >each product selected. My original plan was to simply link these by product
> > >id as a master and child field, however as the product and category combo
> > >boxes are unbound I don’t think this is possible. I tried to make the product
> > >combo box a bound field with the products table the record source and product
> > >name the control source (I also tried to make product id the control source
> > >and it caused my combo box to quit working properly). I then added a text box
> > >with the product id as the control source that would allow me to use as a
> > >master field for the link to the child field (product id on form 3). However
> > >the text box with the product id doesn’t update to the correct product id
> > >number for the product name chosen in the products combo box. Note: I left
> > >the category box unbound.
> >
> >
> > Please read my reply in our other thread. With the
> > additional information here, I think the combo box's
> > RowSource should be:
> >
> > SELECT [Product ID], Product Name
> > FROM Products
> > WHERE Category ID = Forms!mainform.Category
> > ORDER BY [Product Name]
> >
> > The ColumnCount property would be 2
> > The BoundColumn whould be 1
> > The ColumnWidths property would be 0;1
> >
> > subform3's LinkMasterFields would then be set to the product
> > combo box.
> > -----------------------------
> > It all depends on what you are trying to accomplish with
> > these forms, but I am beginning to think that there's
> > something fundamentally odd about about what you are trying
> > to do with these subforms. It doesn't appear to make sense
> > for subform2 to be bound to the products table and at the
> > same time the products combo box to bound to the Product ID
> > field. It seems like you only need one form (bound to the
> > Components table) with an unbound(?) category combo box and
> > the Products combo box bound to the Product ID field in
> > Components???
> >
> > --
> > Marsh
> > MVP [MS Access]
> >
|
Similar Threads
