Only one record can be current (1 Viewer)

[tcarnahan]

I have a table with fields for the year, dues, and a Boolean field that indicates whether or not its record is the current year record.

I have a form based on the table that is used to update the table.

My business rule is that only one record can have the "current year" field checked.

I am working with the Form_Before_Update event. What I would like to happen is if the person selects the "current year" check box and if there is already another record with its check box selected, my code will realize that the user has selected a second record and undo the check box and cancel the event.

Question: would anyone know of code that would check for the second record being checked and back out the user's check for that record?

Note: I have to account for not only the user clicking the record selector to try and save the record, but also the case where they check the box, then click on the "close form" button that I created.

 

[Galaxiom]

It could be done by triggering an update query against the table but I would probably not use the flag that way for the reason you are now considering.

Any alternative would be to store the key of the current year record in another table and select on the basis of that value. Since it can only have one value it doesn't require other values to be changed when it is updated.

 

[tcarnahan]

Since the dues for this organization change each year, I needed to associate the year and the dues amount. It seemed reasonable to include the boolean field to indicate which record represented the current year.

If I understand correctly, are you saying to create a one record, one field table and put the current year value in it. then link the year from the small table to the year in the larger and skip the boolean field?

Thanks,
Tom

 

[Galaxiom]

Normally there would be a date field and the record with the Max date would be selected.

Having the Boolean or the extra table would breach normalization if the record could be determined from the date.

 

[gemma-the-husky]

just general queries about what you are trying to do, as it seems strange

are these records summaries?
why can only one record be the current year record?
why do you need a record marked as current record?
I presume the user who designates a "current record" has to release the flag on closing his form? what happens if his access crashes, or similar, leaving the flag in place?

 

[tcarnahan]

These records are not summaries. The table was intended to be a lookup table. I am keeping historical records of what the members have paid and the lookup table is a historical listing of what the dues amounts were for the organization. I have business rule logic that says "if the the member has not paid their dues for the current year (the fiscal year changes in July), they don't get their name in a directory that we produce. I wanted a way to 1) figure out the current year and current year's dues, and 2) to determine if the person had paid at least the amount of the current years dues. However, I could not have two records designated as the current year. The intent was to have the user determine when the "year changed" and which record represented this year's (current dues).

I am not sure this is making sense, but since I started, I have changed to having a one-field, one-record table that contains the year number of the "current year". Joining it to the list of dues by year solved my problem.