After Update field in subform.

[Guest]

I have loads of clients and they each have 16 records in their subform and no
more.
When each action (record) is completed by the client the done tick box is
ticked.
I want e.g. after update of ticking record 4, then record 7's tick box
should be unticked (not the done tick box a different one) - is that possible

The 2 records are slightly connected because there are 2 id's in the subform
(from the many side of the relationship) one id is different in record 4 and
record 7 but one is the same. so maybe that could help

Thanks so much

 

[Guest]

Are records 4 & 7 the ONLY records that have THAT common ID? If not, then how
can you ensure that ONLY record 7 gets updated and not all the records having
that common ID?

As long as you can positively identify record 7 (and only record 7) from the
informaton in #4, then do this:

Make an update query where:

1. You update TickBox99 to True
2. The criteria for FieldX (the common information) is the common
information found on your form (e.g. =[Forms]![MyForm]![commonID]. This will
limit the update to those records having that commonID.
3. The criteria for the primary excludes the record currently on the form
(e.g. <>[Forms]![MyForm]![fldPrimaryKey]). Ths will ensure that #4 (which
also has the commonID) does not get updated along with #7

 

[Guest]

Ill explain record 4 has 2 ID's AA and AW
Record 7 has DA and AW
So AW is the same but it is the only record AW that has the other ID DA -
get it so how should i write the formula based on that.

Are records 4 & 7 the ONLY records that have THAT common ID? If not, then how
can you ensure that ONLY record 7 gets updated and not all the records having
that common ID?

As long as you can positively identify record 7 (and only record 7) from the
informaton in #4, then do this:

Make an update query where:

1. You update TickBox99 to True
2. The criteria for FieldX (the common information) is the common
information found on your form (e.g. =[Forms]![MyForm]![commonID]. This will
limit the update to those records having that commonID.
3. The criteria for the primary excludes the record currently on the form
(e.g. <>[Forms]![MyForm]![fldPrimaryKey]). Ths will ensure that #4 (which
also has the commonID) does not get updated along with #7

I have loads of clients and they each have 16 records in their subform and no
more.
When each action (record) is completed by the client the done tick box is
ticked.
I want e.g. after update of ticking record 4, then record 7's tick box
should be unticked (not the done tick box a different one) - is that possible

The 2 records are slightly connected because there are 2 id's in the subform
(from the many side of the relationship) one id is different in record 4 and
record 7 but one is the same. so maybe that could help

Thanks so much

 

[Guest]

Here is the answer, but it assumes that ONLY records 4 & 7 have the same AW
entry. If other records haved the same AW entry, they will be updated also.
Pardon my persistence on the field names. In your most recent example, you
have record 4 open, and its AW entry is the same as record 7's AW entry.
Since record 4 does not have the same DA entry as record 7, it seems it can
use only AW to look up record 7, and if there are others with the same AW
record, then how will it find JUST record 7?

On your form, add this to the click event of the first check box:

Private Check1_Click()
DoCmd.OpenQuery "Query1"
End Sub

Query1 looks like this:

UPDATE Table1 SET Table1.Check2 = [Forms]![Form1]![Check1]
WHERE (((Table1.AW)=[Forms]![Form1]![AW]) AND
((Table1.KeyID)<>[Forms]![Form1]![KeyID]));

Table1 is the name of the table that is the recordsource of your form Form1.
AW is the name of the field that contains the same data for both record 4 &
record 7. Check1 & Check2 are two checkboxes bound to yes/no fields of the
same names. KeyID is the name of the primary key in Table1.

Thus, when you click Check1 on record 4 on Form1, the query updates Check2
to equal Check1 for record 7. It does not update Check2 for record 4, since
that is the record currently open on the form.

You can modify the update query to make Check2 whatever you want; in my
example, I just set it to equal what was done to Check1.

Ill explain record 4 has 2 ID's AA and AW
Record 7 has DA and AW
So AW is the same but it is the only record AW that has the other ID DA -
get it so how should i write the formula based on that.

Are records 4 & 7 the ONLY records that have THAT common ID? If not, then how
can you ensure that ONLY record 7 gets updated and not all the records having
that common ID?

As long as you can positively identify record 7 (and only record 7) from the
informaton in #4, then do this:

Make an update query where:

1. You update TickBox99 to True
2. The criteria for FieldX (the common information) is the common
information found on your form (e.g. =[Forms]![MyForm]![commonID]. This will
limit the update to those records having that commonID.
3. The criteria for the primary excludes the record currently on the form
(e.g. <>[Forms]![MyForm]![fldPrimaryKey]). Ths will ensure that #4 (which
also has the commonID) does not get updated along with #7

I have loads of clients and they each have 16 records in their subform and no
more.
When each action (record) is completed by the client the done tick box is
ticked.
I want e.g. after update of ticking record 4, then record 7's tick box
should be unticked (not the done tick box a different one) - is that possible

The 2 records are slightly connected because there are 2 id's in the subform
(from the many side of the relationship) one id is different in record 4 and
record 7 but one is the same. so maybe that could help

Thanks so much

 

[Guest]

So there are 2 other records taht have AW entry but record 7 is the only one
with the DA id that also has AW so can u say to update all AW entries only if
ID action is DA?

Here is the answer, but it assumes that ONLY records 4 & 7 have the same AW
entry. If other records haved the same AW entry, they will be updated also.
Pardon my persistence on the field names. In your most recent example, you
have record 4 open, and its AW entry is the same as record 7's AW entry.
Since record 4 does not have the same DA entry as record 7, it seems it can
use only AW to look up record 7, and if there are others with the same AW
record, then how will it find JUST record 7?

On your form, add this to the click event of the first check box:

Private Check1_Click()
DoCmd.OpenQuery "Query1"
End Sub

Query1 looks like this:

UPDATE Table1 SET Table1.Check2 = [Forms]![Form1]![Check1]
WHERE (((Table1.AW)=[Forms]![Form1]![AW]) AND
((Table1.KeyID)<>[Forms]![Form1]![KeyID]));

Table1 is the name of the table that is the recordsource of your form Form1.
AW is the name of the field that contains the same data for both record 4 &
record 7. Check1 & Check2 are two checkboxes bound to yes/no fields of the
same names. KeyID is the name of the primary key in Table1.

Thus, when you click Check1 on record 4 on Form1, the query updates Check2
to equal Check1 for record 7. It does not update Check2 for record 4, since
that is the record currently open on the form.

You can modify the update query to make Check2 whatever you want; in my
example, I just set it to equal what was done to Check1.

Ill explain record 4 has 2 ID's AA and AW
Record 7 has DA and AW
So AW is the same but it is the only record AW that has the other ID DA -
get it so how should i write the formula based on that.

Are records 4 & 7 the ONLY records that have THAT common ID? If not, then how
can you ensure that ONLY record 7 gets updated and not all the records having
that common ID?

As long as you can positively identify record 7 (and only record 7) from the
informaton in #4, then do this:

Make an update query where:

1. You update TickBox99 to True
2. The criteria for FieldX (the common information) is the common
information found on your form (e.g. =[Forms]![MyForm]![commonID]. This will
limit the update to those records having that commonID.
3. The criteria for the primary excludes the record currently on the form
(e.g. <>[Forms]![MyForm]![fldPrimaryKey]). Ths will ensure that #4 (which
also has the commonID) does not get updated along with #7

:

I have loads of clients and they each have 16 records in their subform and no
more.
When each action (record) is completed by the client the done tick box is
ticked.
I want e.g. after update of ticking record 4, then record 7's tick box
should be unticked (not the done tick box a different one) - is that possible

The 2 records are slightly connected because there are 2 id's in the subform
(from the many side of the relationship) one id is different in record 4 and
record 7 but one is the same. so maybe that could help

Thanks so much

 

[Guest]

Yes.

Just add another non-update field (i.e. used only to filter by the criteria)
to your query. I am guessing that the field is something like ID action and
the criteria is DA.

My only question was this: How do you determine from the contents of record
#4 whether record #7 has DA or whether it has something else?

As long as you have a way of determining this, you should be fine. In any
case, you probably don't want to hard-code the "DA" into your query. You want
to find a way to reference that criteria that is relative to the information
on the current record (#4 in your example).

If you know what that ID action field should contain, you can use VBA to put
its value into a hidden text box on the form, then enter
[Forms]![frmForm]![txtTextBox] as the criteria. That way, "DA" will change to
whatever is necessary in order for any particular record to identify any
other relevant record for update.

So there are 2 other records taht have AW entry but record 7 is the only one
with the DA id that also has AW so can u say to update all AW entries only if
ID action is DA?

Here is the answer, but it assumes that ONLY records 4 & 7 have the same AW
entry. If other records haved the same AW entry, they will be updated also.
Pardon my persistence on the field names. In your most recent example, you
have record 4 open, and its AW entry is the same as record 7's AW entry.
Since record 4 does not have the same DA entry as record 7, it seems it can
use only AW to look up record 7, and if there are others with the same AW
record, then how will it find JUST record 7?

On your form, add this to the click event of the first check box:

Private Check1_Click()
DoCmd.OpenQuery "Query1"
End Sub

Query1 looks like this:

UPDATE Table1 SET Table1.Check2 = [Forms]![Form1]![Check1]
WHERE (((Table1.AW)=[Forms]![Form1]![AW]) AND
((Table1.KeyID)<>[Forms]![Form1]![KeyID]));

Table1 is the name of the table that is the recordsource of your form Form1.
AW is the name of the field that contains the same data for both record 4 &
record 7. Check1 & Check2 are two checkboxes bound to yes/no fields of the
same names. KeyID is the name of the primary key in Table1.

Thus, when you click Check1 on record 4 on Form1, the query updates Check2
to equal Check1 for record 7. It does not update Check2 for record 4, since
that is the record currently open on the form.

You can modify the update query to make Check2 whatever you want; in my
example, I just set it to equal what was done to Check1.

Ill explain record 4 has 2 ID's AA and AW
Record 7 has DA and AW
So AW is the same but it is the only record AW that has the other ID DA -
get it so how should i write the formula based on that.

:

Are records 4 & 7 the ONLY records that have THAT common ID? If not, then how
can you ensure that ONLY record 7 gets updated and not all the records having
that common ID?

As long as you can positively identify record 7 (and only record 7) from the
informaton in #4, then do this:

Make an update query where:

1. You update TickBox99 to True
2. The criteria for FieldX (the common information) is the common
information found on your form (e.g. =[Forms]![MyForm]![commonID]. This will
limit the update to those records having that commonID.
3. The criteria for the primary excludes the record currently on the form
(e.g. <>[Forms]![MyForm]![fldPrimaryKey]). Ths will ensure that #4 (which
also has the commonID) does not get updated along with #7

:

I have loads of clients and they each have 16 records in their subform and no
more.
When each action (record) is completed by the client the done tick box is
ticked.
I want e.g. after update of ticking record 4, then record 7's tick box
should be unticked (not the done tick box a different one) - is that possible

The 2 records are slightly connected because there are 2 id's in the subform
(from the many side of the relationship) one id is different in record 4 and
record 7 but one is the same. so maybe that could help

Thanks so much