updating records in multiple tables from a rowsource

[ABL]

I have the rowsource for a form set to a query that joins multiple tables.
I have two questions:
1) how should I refer to items in the rowsource? For example, if the query
starts as:
"SELECT [product].[product_id], [product].[product_name]"
do I refer to this item as:
me!product.product_name
or me!product_name
or me.column(1)?
2) If I create bound controls from this dataset, will changing their values
update the tables from which they arise? That is, if I create a control
bound to [product].[product_name], and I change the product name, and then
me.update (or me.refresh), will the new product_name be saved?

Thank you!

Alden

Note: please don't reply to this email address, only to the group. I don't
check this address and it is used to collect unsolicited emails.

 

[Dirk Goldgar]

I have the rowsource for a form set to a query that joins multiple
tables.

By "rowsource" I take it you mean "recordsource". List and combo boxes
have rowsources.

I have two questions:
1) how should I refer to items in the rowsource? For example, if the
query starts as:
"SELECT [product].[product_id], [product].[product_name]"
do I refer to this item as:
me!product.product_name
or me!product_name
or me.column(1)?

Does your query return multiple fields with the same name, but from
different tables? If it doesn't, you can use (for example)
Me!Product_Name or Me.Product_Name to refer to your product name field.
If your recordsource query does return multiple fields with the same
name, and if you want to refer to the fields themselves rather than the
controls to which they are bound, use aliases for these fields in the
recordsource query to give them different names. Otherwise you're going
to have a hard (though not impossible) time referring to them as
*fields*.

HOWEVER, if you have controls bound to these fields, Access will insist
on giving those controls different names, and you can refer to the field
values by way of the controls.

2) If I create bound controls from this dataset, will changing their
values update the tables from which they arise? That is, if I create
a control bound to [product].[product_name], and I change the product
name, and then me.update (or me.refresh), will the new product_name
be saved?

If you bind controls to the fields from your query, then changing the
control values will update the tables from which the fields are
selected -- *if* the query is updatable. Not all queries are
updatable -- for details, see the help topic, "When can I update data
from a query?". Assuming the recordset is updatable, then your changes
to the bound controls will update the table when any of several possible
actions cause the form's record to be saved. These actions include:
(a) moving to another record, (b) closing the form, (c) refreshing the
form, (d) requerying the form, (e) manually requesting that the record
be saved, by menu item or keystroke command, and probably some other
means I've forgotten.

 

[alden levy]

Yes, I did mean recordsource, not rowsource (I've got list boxes on the
brain).

Thank you for your help.

--Alden

I have the rowsource for a form set to a query that joins multiple
tables.

By "rowsource" I take it you mean "recordsource". List and combo boxes
have rowsources.

I have two questions:
1) how should I refer to items in the rowsource? For example, if the
query starts as:
"SELECT [product].[product_id], [product].[product_name]"
do I refer to this item as:
me!product.product_name
or me!product_name
or me.column(1)?

Does your query return multiple fields with the same name, but from
different tables? If it doesn't, you can use (for example)
Me!Product_Name or Me.Product_Name to refer to your product name field.
If your recordsource query does return multiple fields with the same
name, and if you want to refer to the fields themselves rather than the
controls to which they are bound, use aliases for these fields in the
recordsource query to give them different names. Otherwise you're going
to have a hard (though not impossible) time referring to them as
*fields*.

HOWEVER, if you have controls bound to these fields, Access will insist
on giving those controls different names, and you can refer to the field
values by way of the controls.

2) If I create bound controls from this dataset, will changing their
values update the tables from which they arise? That is, if I create
a control bound to [product].[product_name], and I change the product
name, and then me.update (or me.refresh), will the new product_name
be saved?

If you bind controls to the fields from your query, then changing the
control values will update the tables from which the fields are
selected -- *if* the query is updatable. Not all queries are
updatable -- for details, see the help topic, "When can I update data
from a query?". Assuming the recordset is updatable, then your changes
to the bound controls will update the table when any of several possible
actions cause the form's record to be saved. These actions include:
(a) moving to another record, (b) closing the form, (c) refreshing the
form, (d) requerying the form, (e) manually requesting that the record
be saved, by menu item or keystroke command, and probably some other
means I've forgotten.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)