Uncheck a box

[Uschi via AccessMonster.com]

I am working on a database that keeps a history of ownership for the
properties in the homeowners association. On the form there is a checkbox
labeled "Is this the current record?" Currently, when a new ownership needs
to be recorded one must find the old record for that property first, uncheck
that box and then add the new record-checking the box, of course.

Is there any way to uncheck the box on the old record when the box is being
checked on the new record?

Fields are Certificate No. (primary key), Name, Address, Date and the
checkbox. Many thanks.

 

[Allen Browne]

It would be possible to use the AfterUpdate event procedure of the form to
execute an Update query statement on the table to set the Current field to
False if the Current field was changed to True in this record.

A better solution might be to add a date field to the table for the date
that the ownership changed hands. The most recent date is the current one.
This is much more reliable, e.g. you don't get yourself into trouble trying
to figure out which one should have been the current one if an entry is made
in error.

 

[Uschi via AccessMonster.com]

Many thanks for reply.
I wouldn't know how to begin setting up an AfterUpdate event procedure. Can
you walk me through this?
Also, there is a date field.

It would be possible to use the AfterUpdate event procedure of the form to
execute an Update query statement on the table to set the Current field to
False if the Current field was changed to True in this record.

A better solution might be to add a date field to the table for the date
that the ownership changed hands. The most recent date is the current one.
This is much more reliable, e.g. you don't get yourself into trouble trying
to figure out which one should have been the current one if an entry is made
in error.

I am working on a database that keeps a history of ownership for the
properties in the homeowners association. On the form there is a checkbox

[quoted text clipped - 10 lines]

Fields are Certificate No. (primary key), Name, Address, Date and the
checkbox. Many thanks.

 

[Allen Browne]

Okay, if you are not familar with code it is all the more reason to drop the
yes/no field from the table, and have it based completely on the most recent
date field.

Delete your exisiting check box, and add another one.
Set its ControlSource to an expression like this:
=([OwnerDate] = DMax("OwnerDate", "Table1", xxxx))
where:
- OwnerDate is the name of your date field,
- Table1 is the name of your table, and
- xxxx represents criteria that defines the unit to look up.

I'm not sure how your criteria works. You can't use [Certificate No], since
it is primary key and there can be multiple entries for the same address(?),
so perhaps it will be:
"[Address] = """ & [Address] & """"

BTW, if you really do have fields called Name and Date, these will give you
problems. Access will misunderstand Name to mean the name of your form
(instead of the contents of the field called Name), and Date to mean the
system date (instead of the contents of your field named Date.) Uncheck the
Name AutoCorrect boxes under:
Tools | Options | General
Then rename the fields in your table, and change the queries, forms, and
reports so they all use the new name.

More info about bad field names and reserved words:
http://allenbrowne.com/AppIssueBadWord.html
More info about the problems caused by Name AutoCorrect:
http://allenbrowne.com/bug-03.html
More info about how to supply the values for DMax() or DLookup():
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Many thanks for reply.
I wouldn't know how to begin setting up an AfterUpdate event procedure.
Can
you walk me through this?
Also, there is a date field.

It would be possible to use the AfterUpdate event procedure of the form to
execute an Update query statement on the table to set the Current field to
False if the Current field was changed to True in this record.

A better solution might be to add a date field to the table for the date
that the ownership changed hands. The most recent date is the current one.
This is much more reliable, e.g. you don't get yourself into trouble
trying
to figure out which one should have been the current one if an entry is
made
in error.

I am working on a database that keeps a history of ownership for the
properties in the homeowners association. On the form there is a
checkbox

[quoted text clipped - 10 lines]

Fields are Certificate No. (primary key), Name, Address, Date and the
checkbox. Many thanks.

 

[Uschi via AccessMonster.com]

Hi Allen,
I think I'm OK on the field names. The table name is ShareCertificates.
Fields are CertNo, SpNo, DateIssued, MembershipName, Reason for Change in
Ownership, MaintenenceFee and Rent. The checkbox field "Current" is used to
sort records for several reports for the membership. For instance, there is a
Membership History Report. The check box shows the most current membership
for that space.

Yes, there are muliple entries for a Space No.

Also, the Association is required to send a 6 mo. report to the Tax Assessor
with new changes in ownership. Ideally, they want a report with before and
after changes for the Space No.

As I started to do this, one fear came to mind - How do I make a query for
the most current memberships, etc?

I inherited this database. One major nightmare I am having is the DateIssued
field. It was set up as text; I believe to make the field look like
MM/DD/YYYY. Of course, changing the field from Text to Date/Time took out all
of the 0's (01/01/2006 is now 1/1/2006). I made an input mask 00/00/0000, but
that didn't keep the formatting.

Of course, I made the changes on a copy of the DB.

I hope this clarifies my DB situation. AfterUpdate event procedure or work
with the DateIssued field? Your assistance and expertise on this matter will
be greatly appreciated.

Okay, if you are not familar with code it is all the more reason to drop the
yes/no field from the table, and have it based completely on the most recent
date field.

Delete your exisiting check box, and add another one.
Set its ControlSource to an expression like this:
=([OwnerDate] = DMax("OwnerDate", "Table1", xxxx))
where:
- OwnerDate is the name of your date field,
- Table1 is the name of your table, and
- xxxx represents criteria that defines the unit to look up.

I'm not sure how your criteria works. You can't use [Certificate No], since
it is primary key and there can be multiple entries for the same address(?),
so perhaps it will be:
"[Address] = """ & [Address] & """"

BTW, if you really do have fields called Name and Date, these will give you
problems. Access will misunderstand Name to mean the name of your form
(instead of the contents of the field called Name), and Date to mean the
system date (instead of the contents of your field named Date.) Uncheck the
Name AutoCorrect boxes under:
Tools | Options | General
Then rename the fields in your table, and change the queries, forms, and
reports so they all use the new name.

More info about bad field names and reserved words:
http://allenbrowne.com/AppIssueBadWord.html
More info about the problems caused by Name AutoCorrect:
http://allenbrowne.com/bug-03.html
More info about how to supply the values for DMax() or DLookup():
http://allenbrowne.com/casu-07.html

Many thanks for reply.
I wouldn't know how to begin setting up an AfterUpdate event procedure.

[quoted text clipped - 20 lines]

 

[Allen Browne]

Did you try the calculated check box?

One you get that working, this article gives you 4 ways to select the most
recent record:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I think I'm OK on the field names. The table name is ShareCertificates.
Fields are CertNo, SpNo, DateIssued, MembershipName, Reason for Change in
Ownership, MaintenenceFee and Rent. The checkbox field "Current" is used
to
sort records for several reports for the membership. For instance, there
is a
Membership History Report. The check box shows the most current membership
for that space.

Yes, there are muliple entries for a Space No.

Also, the Association is required to send a 6 mo. report to the Tax
Assessor
with new changes in ownership. Ideally, they want a report with before and
after changes for the Space No.

As I started to do this, one fear came to mind - How do I make a query for
the most current memberships, etc?

I inherited this database. One major nightmare I am having is the
DateIssued
field. It was set up as text; I believe to make the field look like
MM/DD/YYYY. Of course, changing the field from Text to Date/Time took out
all
of the 0's (01/01/2006 is now 1/1/2006). I made an input mask 00/00/0000,
but
that didn't keep the formatting.

Of course, I made the changes on a copy of the DB.

I hope this clarifies my DB situation. AfterUpdate event procedure or work
with the DateIssued field? Your assistance and expertise on this matter
will
be greatly appreciated.

Okay, if you are not familar with code it is all the more reason to drop
the
yes/no field from the table, and have it based completely on the most
recent
date field.

Delete your exisiting check box, and add another one.
Set its ControlSource to an expression like this:
=([OwnerDate] = DMax("OwnerDate", "Table1", xxxx))
where:
- OwnerDate is the name of your date field,
- Table1 is the name of your table, and
- xxxx represents criteria that defines the unit to look up.

I'm not sure how your criteria works. You can't use [Certificate No],
since
it is primary key and there can be multiple entries for the same
address(?),
so perhaps it will be:
"[Address] = """ & [Address] & """"

BTW, if you really do have fields called Name and Date, these will give
you
problems. Access will misunderstand Name to mean the name of your form
(instead of the contents of the field called Name), and Date to mean the
system date (instead of the contents of your field named Date.) Uncheck
the
Name AutoCorrect boxes under:
Tools | Options | General
Then rename the fields in your table, and change the queries, forms, and
reports so they all use the new name.

More info about bad field names and reserved words:
http://allenbrowne.com/AppIssueBadWord.html
More info about the problems caused by Name AutoCorrect:
http://allenbrowne.com/bug-03.html
More info about how to supply the values for DMax() or DLookup():
http://allenbrowne.com/casu-07.html

Many thanks for reply.
I wouldn't know how to begin setting up an AfterUpdate event procedure.

[quoted text clipped - 20 lines]

Fields are Certificate No. (primary key), Name, Address, Date and the
checkbox. Many thanks.

 

[Uschi via AccessMonster.com]

A Calculated check box sounds like the way to go. I would need your help
setting it up. I am not familiar with code.

Also, you mentioned AfterUpdate event procedure: Would it be possible then to
made a query each for the AfterUpdate and BeforeUpdate event procedure to
keep track of the changes for the 6 month tax report?

Appreciate your expertise from Australia.

Did you try the calculated check box?

One you get that working, this article gives you 4 ways to select the most
recent record:
http://www.mvps.org/access/queries/qry0020.htm

Hi Allen,
I think I'm OK on the field names. The table name is ShareCertificates.

[quoted text clipped - 76 lines]

 

[Allen Browne]

The 2nd reply explained the calculated check box. It's just about setting
its Control Source to an expression like this:
=([OwnerDate] = DMax("OwnerDate", "Table1", xxxx))

For your new question is about keeping track of all changes, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html
It is not a simple process. You will need to understand some VBA and SQL to
implement it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

A Calculated check box sounds like the way to go. I would need your help
setting it up. I am not familiar with code.

Also, you mentioned AfterUpdate event procedure: Would it be possible then
to
made a query each for the AfterUpdate and BeforeUpdate event procedure to
keep track of the changes for the 6 month tax report?

Appreciate your expertise from Australia.

Did you try the calculated check box?

One you get that working, this article gives you 4 ways to select the most
recent record:
http://www.mvps.org/access/queries/qry0020.htm

Hi Allen,
I think I'm OK on the field names. The table name is ShareCertificates.

[quoted text clipped - 76 lines]

Fields are Certificate No. (primary key), Name, Address, Date and
the
checkbox. Many thanks.

 

[Uschi via AccessMonster.com]

Allen,
I don't understand how this expression will uncheck the checkbox on the old
record when the new record is added for that address.

 

[Allen Browne]

It doesn't. The suggestion is to use a calculated field instead of storing
the dependent value. This avoids the whole issue of having to keep the
records up to date: it changes dynamically as you need it.

Storing the dependent value violates normalization rules, but if you want to
do it anyway, you could use the AfterUpdate event procedure of the form to
execute an Update query statement on the table to set the Current field to
False if the Current field was changed to True in this record.

In the General Declarations section of the form's module (at the top, with
the Option statements), declare a variable to track whether the record
became the current one:
Private mbBecameCurrent As Boolean

In the BeforeUpdate event procedure of the form, set the variable:
With Me.[IsCurrent]
If .Value And Not Nz(.OldValue, False) Then
mbBecameCurrent = True
Else
mbBecameCurrent = False
End If
End With

In the AfterUpdate event procedure of the form, exeucte an Update query
statement to set the value to False for all other matching records:
Dim strSql As String
If mbBecameCurrent Then
strSql = "UPDATE [MyTable] SET IsCurrent = False WHERE
([MyForignKey] = " & Me.MyForeignKey & ") AND (ID <> " & Me.[ID] & ") AND
(IsCurrent = True);"
dbEngine(0)(0).Execute strSql, dbFailOnError
End If

To see what you query statement should look like, mock up a query using any
literal criteria, and then switch it to SQL View (View menu, in query
design.) You will need extra quotes if the keys are Text fields (not Number
fields.) And you may need to test the foreign key field for Null.