Solved Update query - Yes/No fields - "Not" operator not working (1 Viewer)

StlthBM

New member
Local time
Today, 20:41
Joined
Nov 2, 2021
Messages
5
I want to run an update query so that when I update the Yes/No field of an individual record to "Yes", all other records in the table are updated to "No". No more than one record in the table would have the "Yes" value.

My approach is to use an update query that has the criteria that where ID is not the ID number on the current form, it will update the fields to "No"

I keep getting the attached error when

I want to run this update query when I select "Yes" on the form

Part of a requirements management database. Here's the simple use case
1. Project team brainstorm a number of Need statements for a system under development. Want to keep track of their good ideas
2. People get to work writing requirements, thinking they know the high level goal
3. Project sponsor changes their mind about the high level goal
4. System designers have already written 100+ requirements, most are fine, some need to be changed. All have to be verified against the new direction


See pics DB1.jpg DB2.jpg How this should work.jpg

Any help on the specific stuff, or just general database design would be great

BTW- I can usually spell SQL correctly 2 times out of 3, so if you can keep it in the Access GUI (hey, look at me go) that would be great
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:41
Joined
May 7, 2009
Messages
19,175
you can add code to the ysnEndorsed Textbox.
so instead of SQL Update query, you can use VBA.
bring fsubNeedStmt subform in Design view and click on ysnEndorsed textbox.
on it's Property->Event->On Click (choose Event Procedure):
Code:
Private Sub ysnEndorsed_Click()
    Dim varID As Variant
    varID = Me!ID
    Me!ysnEndorsed = True
    Me.Dirty = False
    With Me.RecordsetClone
        .MoveFirst
        Do Until .EOF
            If !ID <> varID Then
                .Edit
                !ysnEndorsed = False
                .Update
            End If
            .MoveNext
        Loop
    End With
End Sub
 

Cotswold

Active member
Local time
Today, 12:41
Joined
Dec 31, 2020
Messages
521
Have you looked/tried at using IIF() in your query?
 

StlthBM

New member
Local time
Today, 20:41
Joined
Nov 2, 2021
Messages
5
you can add code to the ysnEndorsed Textbox.
so instead of SQL Update query, you can use VBA.
bring fsubNeedStmt subform in Design view and click on ysnEndorsed textbox.
on it's Property->Event->On Click (choose Event Procedure):
Code:
Private Sub ysnEndorsed_Click()
    Dim varID As Variant
    varID = Me!ID
    Me!ysnEndorsed = True
    Me.Dirty = False
    With Me.RecordsetClone
        .MoveFirst
        Do Until .EOF
            If !ID <> varID Then
                .Edit
                !ysnEndorsed = False
                .Update
            End If
            .MoveNext
        Loop
    End With
End Sub
Wow - I don't know what you did, but it work

I should learn more VBA
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 19, 2002
Messages
42,981
Using an update query as @Cotswald suggested is far better than a VBA loop. Especially since it is faster.

However, a far simpler and therefore better solution is to add a second table. In that table you store the ID of the "yes" record. Then you have only one record to update should you need to change who is "yes" and you never need to worry about making everyone else "no"
 

GPGeorge

Grover Park George
Local time
Today, 05:41
Joined
Nov 25, 2004
Messages
1,776
I want to run an update query so that when I update the Yes/No field of an individual record to "Yes", all other records in the table are updated to "No". No more than one record in the table would have the "Yes" value.

My approach is to use an update query that has the criteria that where ID is not the ID number on the current form, it will update the fields to "No"

I keep getting the attached error when

I want to run this update query when I select "Yes" on the form

Part of a requirements management database. Here's the simple use case
1. Project team brainstorm a number of Need statements for a system under development. Want to keep track of their good ideas
2. People get to work writing requirements, thinking they know the high level goal
3. Project sponsor changes their mind about the high level goal
4. System designers have already written 100+ requirements, most are fine, some need to be changed. All have to be verified against the new direction


See pics View attachment 97446 View attachment 97447 View attachment 97448

Any help on the specific stuff, or just general database design would be great

BTW- I can usually spell SQL correctly 2 times out of 3, so if you can keep it in the Access GUI (hey, look at me go) that would be great
Pat's solution is best, I think.

However, two simple queries do this:

Update tblYourTableNameGoesHere SET ysndorsed = 0

Update tblYourTableNameGoesHere SET ysndorsed = -1 WHERE tblYourTableNameGoesHere.ID = Forms!frmEditing.txtID
 

Users who are viewing this thread

Top Bottom