Only 1 Boolean True (1 Viewer)

[monvani]

I have a table with several fields, but one of them is a Yes/No field. I have this on a form, and I want the user to only be able to select Yes on one record. When the form opens, there should be a yes on one record, and if the user tries to click the checkbox on another record to set it to Yes, i want the original Yes record to be converted to No, and the newly clicked record to assume the Yes.

Is this possible? I'm pretty good with VBA in Excel, but I just can't get the syntax right to loop through the records to test each one, or find a "unique" property for the checkbox field, etc.

Please help!

 

[Banana]

Interesting requirement. Can I ask you why you want this to work this way?

But I suppose the simplest solution is to clear the original check with a query.

In the checkbox's BeforeUpdate event do this:

If Not Me.<NameOfTheYesNofield> Then
   CurrentDb.Execute "UPDATE <NameOfTableWithYesNofield> SET <NameOfTheYesNoField> = 0;", dbFailOnError
End If

(Replace all the <>s with actual names and remove the <>s themselves as well)

This will update the table, clearing the check wherever it may be prior to you actually updating the record. The If/Then prevent erroneous write conflict due to user clicking on the checkbox again (and clearing it anyway).

HTH.

 

[Galaxiom]

Have you considered changing your data structure. This could be done much more efficiently in a single field.
Each current checkbox field would be replaced by a value in the new field. A value of zero would mean none checked. Any other value would indicate the choice.

Use an Option group on the form rather than multiple checkboxes.

 

[monvani]

Ok, so I'm writing some reports that are produced monthly, and in the header of the report, I have the month it is being created for (example "October 2009"). Because of processing calendars and the fact that some reports will be created during the prior month, this must be set manually and for whatever the current report is being worked on.

I have a table that has the following fields:
Month (Text)
Active (Yes/No)

Basically, the user will goto this form, click the check next to the active month (and ideally any other existing checks (perhaps from last month) will automatically clear and the new selection will be made. This will be referenced by dlookup on the report header and display appropriately.

Unfortunately right now, it is still allowing more than one check to be present.

Banana, I tried using your code, but I am getting some strange Write Conflict errors messages. Also, it is allowing multiple selections for some time, and only when clicked from No to Yes to No do I really get any response from the form.

Here is my exact Event Procedure being called on "Before Update" of the Active (Yes/No) field:

Private Sub active_BeforeUpdate(Cancel As Integer)

If Not Me.active Then
CurrentDb.Execute "UPDATE tblMonths SET Active = NO;", dbFailOnError
End If

End Sub

 

[Galaxiom]

Sounds to me like the database structure is the real problem and this procedure is a workaround. Explain more about your database structure. We may be able to improve it and avoid the problem entirely. Post an image of the Relationships window.

 

[monvani]

A sample of my database is attached.

Please see the form, I want to put a check next to the month that will display at the top of the sample report.

I know there are so many different ways to do things in Access, but this is part of a huge database, and I really don't want to tinker with anything but this form.

I just want only one of the [Active] boxes on the form to be able to be checked yes. In my simple mind, there would be a After Update event that would loop through all the records in the tblMonths after a record was set to yes, and set any that are set to Yes to No (except for the record being edited).

I also think a option group would be nice, but I don't see how that's possible across records.

Please help! Again, I know there are probably 100 better ways of doing this (aren't there always?), but I'd really just like to know how to do this as it would help me with other applications as well. Really, it would help my development skills if I could just get the basic syntax of how to loop through each of the records in a recordset and test the values of a certain field(s).

The current code was my try from earlier advice on this post that doesn't seem to be working right.

I appreciate any help that can be given. View attachment Test.zip

 

[Galaxiom]

The option group was an idea for my original misunderstood concept of your strucure. I thought the checkboxes were in different fields on the same record rather than on separate records. As you have established it isn't the solution for your problem. Sorry, I should have said this when I better understood the problem.

You are quite right that you cannot randomly tinker with a database structure even if it is clearly wrong. Especially if it is someone else's design. All the effects of any change must be established.

You can do it with one sql statement rather than a loop.
Quick and dirty.
This should set all the others to false except the current record if the current record is set to true.

After Update event of the checkbox control:

If [tblMonths].[Active] Then
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblMonths SET Active = False WHERE tblMonths.ID <> Forms!tblMonths!ID"
End If

NB Be sure to also place a DoCmd.SetWarnings True command in the exit section of the procedure to ensure it is turned back on even if there is an error. Otherwise you get no warnings about anything anywhere in the db.

 

[Banana]

The reason why my suggestion didn't work was most likely because the record was on the same page that was being edited by the form. Since any editing via form and any executed SQL is considered to be two "connections", Access thinks there's two different users trying to edit the same record, but that's not the case. Therefore if you already dirty the record (recall that Dirty event fires before the control's BeforeUpdate), then it will be a thorn in your side.

Galaxiom's suggestion to do it in AfterUpdate may work, providing that the record isn't still dirty. If it's causing the same problem, a shoddy workaround would be to save the record first before running the SQL. I say shoddy because it may force unnecessary save when you may have other logics that are pending validation.

In that case, the only alternate is to loop the recordset. The reason why I didn't (and I suspect Galaxiom didn't as well) suggest this is because it's slower and more work but in context of editing data via form, is only way to do anything meaningful without getting that write conflict error you had.

With Me.Recordsetclone
   .MoveFirst
   .Do Until .EOF
       If Not .ID = Me.ID Then
               .ID = 0
       End If
       .Movenext
   .Loop
End With

 

[monvani]

confused:
Hmmm....not sure why it's not working. Getting the "Invalid Outside Procedure" error.

View attachment 28894 :

 

[monvani]

confused:
Hmmm....not sure why it's not working. Getting the "Invalid Outside Procedure" error.

View attachment 28894 :

 

[monvani]

confused:
Hmmm....not sure why it's not working. Getting the "Invalid Outside Procedure" error.
View attachment Test.zip

 

[Banana]

The error indicates that you've pasted the code outside of the event handler.

It should be between the "Private Sub..." and "End Sub" for the appropriate event. Is that not the case?

 

[monvani]

Thanks, it's working now. I really appreciate your help!

 

[Banana]

Great. Best of luck with the rest of your project.