Modifying subform records for cartesian product

[manning_news]

Using A2K. I've got a database with client info and each client has a
subform which contains types of income and the amount they each
receive. The record source of the subform is a cartesian product of
the client ID and the types of income of which there is currently 16
types. So every client has 16 records.

What I'd like to do is to allow the user to modify a type from any
client's subform and the changes affect all clients. So if the user
wanted to change the income type "Worker's Comp" to "Worker's
Compensation", all they would have to do is change it on the current
client's subform and it would be changed for all client's.

I've got a SQL statement in the AfterUpdate event of the income type
field to modify the type:

strSQL = "UPDATE tblIncomeAtEntry SET
tblIncomeAtEntry.IncomeType = " & _
conQuote & Me.txtIncomeType & conQuote & " " & _
"WHERE (((tblIncomeAtEntry.IncomeType) = " & _
conQuote & Me.txtIncomeType.OldValue & conQuote & "));"
dbs.Execute strSQL, dbFailOnError

Modifying a type actually modifies the record but then it also modifies
the very first record in the subform, which is not a good thing.

Originally the SQL statement excluded the current client's ID #. The
SQL looked something like this: "AND tblIncomeAtEntry.ID <> <current
client ID #>"

Any suggestions or advice would be appreciated.

[Larry Linson]

<ma**********@hotmail.com> wrote

Using A2K. I've got a database with
client info and each client has a subform
which contains types of income and the
amount they each receive. The record
source of the subform is a cartesian product of
the client ID and the types of income of
which there is currently 16 types. So
every client has 16 records.

What I'd like to do is to allow the user
to modify a type from any client's subform
and the changes affect all clients. So if the user
wanted to change the income type "Worker's
Comp" to "Worker's Compensation", all they
would have to do is change it on the current
client's subform and it would be changed for
all client's.

I've got a SQL statement in the AfterUpdate
event of the income type field to modify the type:

strSQL = "UPDATE tblIncomeAtEntry SET
tblIncomeAtEntry.IncomeType = " & _
conQuote & Me.txtIncomeType & conQuote & " " & _
"WHERE (((tblIncomeAtEntry.IncomeType) = " & _
conQuote & Me.txtIncomeType.OldValue &_
conQuote & "));"
dbs.Execute strSQL, dbFailOnError

Modifying a type actually modifies the record
but then it also modifies the very first record
in the subform, which is not a good thing.

It is not clear to me why this would happen, based on the SQL you show. I am
assuming you mean that it happens even if the "very first record in the
subform" is something other than the record with the appropriate original
"type".

Does the _database_ itself get properly updated? Could it just be a matter
of your needing to Requery?

Larry Linson
Microsoft Access MVP

[manning_news]

Thanks for your response. I see the problem. I update a lookup table
as well and wasn't editing the correct record in the lookup table.

Larry Linson wrote:

<ma**********@hotmail.com> wrote

> Using A2K. I've got a database with
> client info and each client has a subform
> which contains types of income and the
> amount they each receive. The record
> source of the subform is a cartesian product of
> the client ID and the types of income of
>which there is currently 16 types. So
> every client has 16 records.
>
> What I'd like to do is to allow the user
> to modify a type from any client's subform
> and the changes affect all clients. So if the user
> wanted to change the income type "Worker's
> Comp" to "Worker's Compensation", all they
> would have to do is change it on the current
> client's subform and it would be changed for
> all client's.
>
> I've got a SQL statement in the AfterUpdate
> event of the income type field to modify the type:
>
> strSQL = "UPDATE tblIncomeAtEntry SET
> tblIncomeAtEntry.IncomeType = " & _
> conQuote & Me.txtIncomeType & conQuote & " " & _
> "WHERE (((tblIncomeAtEntry.IncomeType) = " & _
> conQuote & Me.txtIncomeType.OldValue &_
> conQuote & "));"
> dbs.Execute strSQL, dbFailOnError
>
> Modifying a type actually modifies the record
> but then it also modifies the very first record
> in the subform, which is not a good thing.

It is not clear to me why this would happen, based on the SQL you show. I am
assuming you mean that it happens even if the "very first record in the
subform" is something other than the record with the appropriate original
"type".

Does the _database_ itself get properly updated? Could it just be a matter
of your needing to Requery?

Larry Linson
Microsoft Access MVP