How do I add to a subform?

[Dustin]

Hi,
I have a warranty claim form that needs to have a subform that will hold the
parts used on that claim. With the parts list in a combo box, how do I select
the parts and add them to the subform?

Thanks,
Dustin

 

[KARL DEWEY]

In creating the query used for the subform form, include the field that will
relate it to the main form - maybe claim number.
Create a form and include the field that will relate it to the main form.
Set the text box for that field Visible property to No. Use a combo box
instead of text box for parts list. Save the form,
In the main form design view click on the Tool Box icon (looks like a hammer
and pick crossed). Move over the icon and select the Subform/Subreport.
Click on menu VIEW - Properties. Set the Master Link and Child Links to the
field that will relate it to the main form.

 

[John W. Vinson]

Hi,
I have a warranty claim form that needs to have a subform that will hold the
parts used on that claim. With the parts list in a combo box, how do I select
the parts and add them to the subform?

Firs off... Forms and Subforms do NOT "hold" or contain data. Forms are *just
tools*, windows that let you manipulate data stored in Tables.

And, a combo box is not data - it's just another tool, a way to select a value
from one table and store it in another table.

You should have three Tables to hold your data: Claims (your mainform); Parts
(which will be used to build the rowsource query for a combo box); and
PartsUsed, which will be the Recordsource for your subform. One of (perhaps
the only) controls on the Subform will be the combo box selecting parts, bound
to a PartID field in the PartsUsed table.

If you're using the Lookup Wizard to put a combo box in one of your tables...
bear in mind that it's never necessary to use Lookup Fields in tables, and
that it can cause considerable confusion.

John W. Vinson [MVP]

 

[Dustin]

OK that definetly got me on the right track. But where do I put the combo box
with the parts? In the main form or should I be able to have a drop down in
the subform?

Thanks to both

Dustin

 

[John W. Vinson]

OK that definetly got me on the right track. But where do I put the combo box
with the parts? In the main form or should I be able to have a drop down in
the subform?

Well, think about it.

The main form is based on the Claims table. The Claims table does not have a
field for parts (it can't; a field can hold only one value, and you have many
parts for a claim).

The subform is based on the PartsUsed table, which DOES have a field for the
part - you'll add as many records to this table as the claim needs.

Therefore the combo box should be on the Subform, since it is bound to the
PartsUsed table.

John W. Vinson [MVP]

 

[Dustin]

Hey thanks,
Its all working good accept that when I try to select a part it tells me
that,
You cant assign a value to this object.
*The object may be a control on a read only form
*The object may be on a form that is open in design view
*The value maybe to large for this field
None of my forms are open in design view. And I dont think the value is to
large?

It lets me enter it after I click ok. So I am not sure what I did wrong
there and how do I get the part description to populate after I select the
part?

Thanks Dustin

 

[John W. Vinson]

Hey thanks,
Its all working good accept that when I try to select a part it tells me
that,
You cant assign a value to this object.
*The object may be a control on a read only form
*The object may be on a form that is open in design view
*The value maybe to large for this field
None of my forms are open in design view. And I dont think the value is to
large?

You may have the wrong column bound. What is the Control Source of the combo
box? What is that field's Datatype? Is it a Lookup Field in your table (if so,
it may *appear* to contain a text description but in fact it contains a
concealed number). What is the Rowsource of the combo, and the bound column?

It lets me enter it after I click ok. So I am not sure what I did wrong
there and how do I get the part description to populate after I select the
part?

If you're trying to store the partID and the description in the PartsUsed
table... *DON'T*. This table should contain only the Part ID. Unless the part
ID field is meaningful and familiar to users, you should consider using the
combo to *store* the partID but display the description. It's also possible to
put a textbox on the form with a control source like

=cboParts.Column(n)

where cboParts is the name of the combo box, and n is the zero based index of
the field you want to display.

John W. Vinson [MVP]

 

[Dustin]

Hi John,

I figured out my problem, I had a primary key in the child field.

It functions well, but when I tried the formula for the descriptions I got
this in the descriptions field, #Name?. My formula reads =[Parts Combo
Box].Column(2)
The Parts Combo Box is the query I ran that the Parts List Combo Box runs
off of.

What would I be doing wrong?

Thanks so much!

Dustin




I am having difficulty finding what I did wrong. When I select the part
numbers for a claim it only shows the first part # I selected in the
continous subform. But it is showing all of them in the table

 

[John W. Vinson]

Hi John,

I figured out my problem, I had a primary key in the child field.

It functions well, but when I tried the formula for the descriptions I got
this in the descriptions field, #Name?. My formula reads =[Parts Combo
Box].Column(2)
The Parts Combo Box is the query I ran that the Parts List Combo Box runs
off of.

What would I be doing wrong?

Using a combo box in a Query, or a Table, for starters.

Table and query datasheets are of VERY limited utility. I was assuming you
were using a Form (based on the subject line). The syntax will work if the
Combo Box is a control on your Form, has at least three columns (the (2) means
the third column, it is zero based), and the textbox withthis control source
is on the same form.

Note that a Query has a name property, and a Combo Box has a name property...
but a query is not a combo box, and vice versa. My suggestion will work on a
Form; it will not work in a Table, and you shouldn't be looking at the table
anyway!

John W. Vinson [MVP]

 

[Dustin]

Thanks for responding John,

I think the last post I made might not have been clear. My subform is a form
that contains a combo box that selects the Part No from a query off the parts
list table. When I made the combo box on the subform using wizard it asked me
to select the query or table I wanted to use. The name of the query I used is
Parts Combo Box. Is this the right way to do it or am I still mistaken.

Thanks Dustin

Hi John,

I figured out my problem, I had a primary key in the child field.

It functions well, but when I tried the formula for the descriptions I got
this in the descriptions field, #Name?. My formula reads =[Parts Combo
Box].Column(2)
The Parts Combo Box is the query I ran that the Parts List Combo Box runs
off of.

What would I be doing wrong?

Using a combo box in a Query, or a Table, for starters.

Table and query datasheets are of VERY limited utility. I was assuming you
were using a Form (based on the subject line). The syntax will work if the
Combo Box is a control on your Form, has at least three columns (the (2) means
the third column, it is zero based), and the textbox withthis control source
is on the same form.

Note that a Query has a name property, and a Combo Box has a name property...
but a query is not a combo box, and vice versa. My suggestion will work on a
Form; it will not work in a Table, and you shouldn't be looking at the table
anyway!

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks for responding John,

I think the last post I made might not have been clear. My subform is a form
that contains a combo box that selects the Part No from a query off the parts
list table. When I made the combo box on the subform using wizard it asked me
to select the query or table I wanted to use. The name of the query I used is
Parts Combo Box. Is this the right way to do it or am I still mistaken.

Since I cannot see your database, and do not know the names of your Queries,
nor the names of your Tables, nor what information they contain... all I can
say is I Don't Know.

Is there a query named [Parts Combo Box] when you open the Queries window?

If so, what is its SQL? Select View... SQL and post it here.

Does it return the information that you want to see?

What are the other properties of the combo box: Control Source? Bound Column?
Column Count? Column Widths?

John W. Vinson [MVP]

 

[Dustin]

Thanks for your help John. I got the formula to work the way you said.

Thanks again,
Dustin

Thanks for responding John,

I think the last post I made might not have been clear. My subform is a form
that contains a combo box that selects the Part No from a query off the parts
list table. When I made the combo box on the subform using wizard it asked me
to select the query or table I wanted to use. The name of the query I used is
Parts Combo Box. Is this the right way to do it or am I still mistaken.

Since I cannot see your database, and do not know the names of your Queries,
nor the names of your Tables, nor what information they contain... all I can
say is I Don't Know.

Is there a query named [Parts Combo Box] when you open the Queries window?

If so, what is its SQL? Select View... SQL and post it here.

Does it return the information that you want to see?

What are the other properties of the combo box: Control Source? Bound Column?
Column Count? Column Widths?

John W. Vinson [MVP]