Subform

[Lowner]

I have a subForm that works perfectly in single form view, but I can not get
the same information that I refered to from the subForm to show up on the
main form when I change the view from single form to Datasheet view, I keep
getting name# or error# messages in that main form. I need to information to
show up in datasheet view. any ideas or suggestions would be highly
appreciated. Thanks in advance

 

[Pat Hartman \(MVP\)]

Since the subform will be showing multiple rows in datasheet view, exactly
which row did you want to "copy" the data from? The only row you can
reference in a sub form is the current row which may not even be visible.
It doesn't make logical sense to copy data from a many-side table for
display on a form showing its parent table.

 

[Lowner]

Ok, I have an Order form, with an Item number , quantity, price etc. on the
main form. The subform is the list of the actual items, including the above
item number and the item description and other information that is not needed
at this time. I can put a column for item descprtion on the main form, but I
figured that since the item number has already been entered by the user, that
it would make some sense to just have the item number populate. In Datasheet
view, the sub form is not visible, but I created a Text Box that referenced
the subform location. The reference on the main form looks like this
Description=[descriptions Subform]!description. The information shows up in
the text box when I do a single form view, the subform at that time is also
visible. When I switch to Datasheet view, there is a column that access adds
called Description, but that is when I get the Name or Error message...hope
this helps or sheds light on my error and how it may be fixed

 

[Pat Hartman \(MVP\)]

I am totally confused about your structure. Typically an Order can be for
many items, which you imply yours is, so again I ask - WHICH description
should show on the main form if the subform contains multiple items? That
is the question that Access is asking with the error message. How can
Access choose which description to display from many choices?

It is possible that rather than referencing a form field (forms don't store
data, tables store data) you need to create a query that joins the two
tables and use that for the recordsource for the form. That way, you will
have the description in the form's Field Collection and so you can choose it
from the combo and bind it to a control rather than trying to use an
expression to obtain the data.

Ok, I have an Order form, with an Item number , quantity, price etc. on
the
main form. The subform is the list of the actual items, including the
above
item number and the item description and other information that is not
needed
at this time. I can put a column for item descprtion on the main form,
but I
figured that since the item number has already been entered by the user,
that
it would make some sense to just have the item number populate. In
Datasheet
view, the sub form is not visible, but I created a Text Box that
referenced
the subform location. The reference on the main form looks like this
Description=[descriptions Subform]!description. The information shows up
in
the text box when I do a single form view, the subform at that time is
also
visible. When I switch to Datasheet view, there is a column that access
adds
called Description, but that is when I get the Name or Error
message...hope
this helps or sheds light on my error and how it may be fixed

Since the subform will be showing multiple rows in datasheet view,
exactly
which row did you want to "copy" the data from? The only row you can
reference in a sub form is the current row which may not even be visible.
It doesn't make logical sense to copy data from a many-side table for
display on a form showing its parent table.

 

[Lowner]

Well, each item has an item number, and each item has a description, the item
number is the unique field. The orders/main Form have the item numbers, and
the Description sub form has each item number and its corresponding item
description i.e. item # 0111 item Description would be "Spring water 16 OZ.".
Thus if the order has item 0111 , I would want for access to look in the
Description sub Form, use the Item # 0111 to find the item description to put
on Main Form. if that is any clearer. My method works when I use the Single
From view, but it does not work when I try to use the DataSheet. I want to
be able to enter data in the item # field and have the Item description show
up. I will be sure to try your other suggestions too and see if they will
rectify my issue. Thanks again, any further assistance is still highly
appreciated. Thanks for your time and patience.

I am totally confused about your structure. Typically an Order can be for
many items, which you imply yours is, so again I ask - WHICH description
should show on the main form if the subform contains multiple items? That
is the question that Access is asking with the error message. How can
Access choose which description to display from many choices?

It is possible that rather than referencing a form field (forms don't store
data, tables store data) you need to create a query that joins the two
tables and use that for the recordsource for the form. That way, you will
have the description in the form's Field Collection and so you can choose it
from the combo and bind it to a control rather than trying to use an
expression to obtain the data.

Ok, I have an Order form, with an Item number , quantity, price etc. on
the
main form. The subform is the list of the actual items, including the
above
item number and the item description and other information that is not
needed
at this time. I can put a column for item descprtion on the main form,
but I
figured that since the item number has already been entered by the user,
that
it would make some sense to just have the item number populate. In
Datasheet
view, the sub form is not visible, but I created a Text Box that
referenced
the subform location. The reference on the main form looks like this
Description=[descriptions Subform]!description. The information shows up
in
the text box when I do a single form view, the subform at that time is
also
visible. When I switch to Datasheet view, there is a column that access
adds
called Description, but that is when I get the Name or Error
message...hope
this helps or sheds light on my error and how it may be fixed

Since the subform will be showing multiple rows in datasheet view,
exactly
which row did you want to "copy" the data from? The only row you can
reference in a sub form is the current row which may not even be visible.
It doesn't make logical sense to copy data from a many-side table for
display on a form showing its parent table.

I have a subForm that works perfectly in single form view, but I can not
get
the same information that I refered to from the subForm to show up on
the
main form when I change the view from single form to Datasheet view, I
keep
getting name# or error# messages in that main form. I need to
information
to
show up in datasheet view. any ideas or suggestions would be highly
appreciated. Thanks in advance

 

[Bob Quintal]

Well, each item has an item number, and each item has a
description, the item number is the unique field. The orders/main
Form have the item numbers, and the Description sub form has each
item number and its corresponding item description i.e. item #
0111 item Description would be "Spring water 16 OZ.". Thus if the
order has item 0111 , I would want for access to look in the
Description sub Form, use the Item # 0111 to find the item
description to put on Main Form. if that is any clearer. My
method works when I use the Single From view, but it does not work
when I try to use the DataSheet. I want to be able to enter data
in the item # field and have the Item description show up. I will
be sure to try your other suggestions too and see if they will
rectify my issue. Thanks again, any further assistance is still
highly appreciated. Thanks for your time and patience.

You are trying to use a subform instead of a combobox or listbox.
Remove the subform from your form.

Q

P.S.
Your main form should probably be a subdorm of a Customer Order
form, where you enter the customer's name, address and similar info
common to all the items that the customer is buying at one time,
with the subform holding the details about how many of what part he
is actually using.

I am totally confused about your structure. Typically an Order
can be for many items, which you imply yours is, so again I ask -
WHICH description should show on the main form if the subform
contains multiple items? That is the question that Access is
asking with the error message. How can Access choose which
description to display from many choices?

It is possible that rather than referencing a form field (forms
don't store data, tables store data) you need to create a query
that joins the two tables and use that for the recordsource for
the form. That way, you will have the description in the form's
Field Collection and so you can choose it from the combo and bind
it to a control rather than trying to use an expression to obtain
the data.

Ok, I have an Order form, with an Item number , quantity, price
etc. on the
main form. The subform is the list of the actual items,
including the above
item number and the item description and other information that
is not needed
at this time. I can put a column for item descprtion on the
main form, but I
figured that since the item number has already been entered by
the user, that
it would make some sense to just have the item number populate.
In Datasheet
view, the sub form is not visible, but I created a Text Box
that referenced
the subform location. The reference on the main form looks
like this Description=[descriptions Subform]!description. The
information shows up in
the text box when I do a single form view, the subform at that
time is also
visible. When I switch to Datasheet view, there is a column
that access adds
called Description, but that is when I get the Name or Error
message...hope
this helps or sheds light on my error and how it may be fixed

:

Since the subform will be showing multiple rows in datasheet
view, exactly
which row did you want to "copy" the data from? The only row
you can reference in a sub form is the current row which may
not even be visible. It doesn't make logical sense to copy
data from a many-side table for display on a form showing its
parent table.

I have a subForm that works perfectly in single form view,
but I can not get
the same information that I refered to from the subForm to
show up on the
main form when I change the view from single form to
Datasheet view, I keep
getting name# or error# messages in that main form. I need
to information
to
show up in datasheet view. any ideas or suggestions would
be highly appreciated. Thanks in advance

 

[Pat Hartman \(MVP\)]

I think Bob hit on the issue. It sounds like you are using a subform when
you should probably be using a combo. If you create a query that binds your
order items to the product table, you Access will auto-fill the lookup
fields from the product table. Be sure to set the locked property of these
lookup fields to yes. You don't want anyone accidentally changing a product
description when thinking he was just changing it on an order.

Well, each item has an item number, and each item has a
description, the item number is the unique field. The orders/main
Form have the item numbers, and the Description sub form has each
item number and its corresponding item description i.e. item #
0111 item Description would be "Spring water 16 OZ.". Thus if the
order has item 0111 , I would want for access to look in the
Description sub Form, use the Item # 0111 to find the item
description to put on Main Form. if that is any clearer. My
method works when I use the Single From view, but it does not work
when I try to use the DataSheet. I want to be able to enter data
in the item # field and have the Item description show up. I will
be sure to try your other suggestions too and see if they will
rectify my issue. Thanks again, any further assistance is still
highly appreciated. Thanks for your time and patience.

You are trying to use a subform instead of a combobox or listbox.
Remove the subform from your form.

Q

P.S.
Your main form should probably be a subdorm of a Customer Order
form, where you enter the customer's name, address and similar info
common to all the items that the customer is buying at one time,
with the subform holding the details about how many of what part he
is actually using.

I am totally confused about your structure. Typically an Order
can be for many items, which you imply yours is, so again I ask -
WHICH description should show on the main form if the subform
contains multiple items? That is the question that Access is
asking with the error message. How can Access choose which
description to display from many choices?

It is possible that rather than referencing a form field (forms
don't store data, tables store data) you need to create a query
that joins the two tables and use that for the recordsource for
the form. That way, you will have the description in the form's
Field Collection and so you can choose it from the combo and bind
it to a control rather than trying to use an expression to obtain
the data.


Ok, I have an Order form, with an Item number , quantity, price
etc. on the
main form. The subform is the list of the actual items,
including the above
item number and the item description and other information that
is not needed
at this time. I can put a column for item descprtion on the
main form, but I
figured that since the item number has already been entered by
the user, that
it would make some sense to just have the item number populate.
In Datasheet
view, the sub form is not visible, but I created a Text Box
that referenced
the subform location. The reference on the main form looks
like this Description=[descriptions Subform]!description. The
information shows up in
the text box when I do a single form view, the subform at that
time is also
visible. When I switch to Datasheet view, there is a column
that access adds
called Description, but that is when I get the Name or Error
message...hope
this helps or sheds light on my error and how it may be fixed

:

Since the subform will be showing multiple rows in datasheet
view, exactly
which row did you want to "copy" the data from? The only row
you can reference in a sub form is the current row which may
not even be visible. It doesn't make logical sense to copy
data from a many-side table for display on a form showing its
parent table.

I have a subForm that works perfectly in single form view,
but I can not get
the same information that I refered to from the subForm to
show up on the
main form when I change the view from single form to
Datasheet view, I keep
getting name# or error# messages in that main form. I need
to information
to
show up in datasheet view. any ideas or suggestions would
be highly appreciated. Thanks in advance

 

[Lowner]

Thanks, I went ahead and changed it to a combo box

I think Bob hit on the issue. It sounds like you are using a subform when
you should probably be using a combo. If you create a query that binds your
order items to the product table, you Access will auto-fill the lookup
fields from the product table. Be sure to set the locked property of these
lookup fields to yes. You don't want anyone accidentally changing a product
description when thinking he was just changing it on an order.

Well, each item has an item number, and each item has a
description, the item number is the unique field. The orders/main
Form have the item numbers, and the Description sub form has each
item number and its corresponding item description i.e. item #
0111 item Description would be "Spring water 16 OZ.". Thus if the
order has item 0111 , I would want for access to look in the
Description sub Form, use the Item # 0111 to find the item
description to put on Main Form. if that is any clearer. My
method works when I use the Single From view, but it does not work
when I try to use the DataSheet. I want to be able to enter data
in the item # field and have the Item description show up. I will
be sure to try your other suggestions too and see if they will
rectify my issue. Thanks again, any further assistance is still
highly appreciated. Thanks for your time and patience.

You are trying to use a subform instead of a combobox or listbox.
Remove the subform from your form.

Q

P.S.
Your main form should probably be a subdorm of a Customer Order
form, where you enter the customer's name, address and similar info
common to all the items that the customer is buying at one time,
with the subform holding the details about how many of what part he
is actually using.

:

I am totally confused about your structure. Typically an Order
can be for many items, which you imply yours is, so again I ask -
WHICH description should show on the main form if the subform
contains multiple items? That is the question that Access is
asking with the error message. How can Access choose which
description to display from many choices?

It is possible that rather than referencing a form field (forms
don't store data, tables store data) you need to create a query
that joins the two tables and use that for the recordsource for
the form. That way, you will have the description in the form's
Field Collection and so you can choose it from the combo and bind
it to a control rather than trying to use an expression to obtain
the data.


Ok, I have an Order form, with an Item number , quantity, price
etc. on the
main form. The subform is the list of the actual items,
including the above
item number and the item description and other information that
is not needed
at this time. I can put a column for item descprtion on the
main form, but I
figured that since the item number has already been entered by
the user, that
it would make some sense to just have the item number populate.
In Datasheet
view, the sub form is not visible, but I created a Text Box
that referenced
the subform location. The reference on the main form looks
like this Description=[descriptions Subform]!description. The
information shows up in
the text box when I do a single form view, the subform at that
time is also
visible. When I switch to Datasheet view, there is a column
that access adds
called Description, but that is when I get the Name or Error
message...hope
this helps or sheds light on my error and how it may be fixed

:

Since the subform will be showing multiple rows in datasheet
view, exactly
which row did you want to "copy" the data from? The only row
you can reference in a sub form is the current row which may
not even be visible. It doesn't make logical sense to copy
data from a many-side table for display on a form showing its
parent table.

I have a subForm that works perfectly in single form view,
but I can not get
the same information that I refered to from the subForm to
show up on the
main form when I change the view from single form to
Datasheet view, I keep
getting name# or error# messages in that main form. I need
to information
to
show up in datasheet view. any ideas or suggestions would
be highly appreciated. Thanks in advance

 

[Lowner]

Thanks Everyone