Solved Update a Yes/No column via button click.

[mamradzelvy]

Hi,
I'm trying today to make a button on a bound form that would change a "no" value to "yes" within my table on the currently open record.

I've tried this:

Private Sub BtnFinalise_Click()
DoCmd.RunSQL "UPDATE Tabule1 SET TabStatus = 'Yes' WHERE ID LIKE '" & Me!txtID.Value & "';"
End Sub

However this results in an error, which in rough translation says "MS Access can not update all records in update query."

my assumption is that running an sql command is not the correct approach to a bound form control and this results in problem because i'm currently working with said Table?

Edit: forgot to actually ask a question regarding the issue- What would be the proper way to go about this?

 

[bob fitz]

If you only need to update the current record why not just use something like:
Me.TabStatus = True

or even have a bound check box to click instead of a button.

 

[arnelgp]

add the TabStatus to your form.
rename the Textbox to txtTabStatus.
Set the textbox's Visible property to No.

on the click event of BtFinalise:

Me.txtTabStatus = "Yes"
Me.Dirty = False

 

[CJ_London]

if TabStatus is a yes/no field, why not just change the control type to a checkbox?

Also as Bob has indicated yes/no fields are numeric - 0=false/no, -1=true/yes

 

[mamradzelvy]

If you only need to update the current record why not just use something like:
Me.TabStatus = True

or even have a bound check box to click instead of a button.

Thanks for your input,
I have decided against checkboxes as the yes/no value tells me whether the record within my table should be still be displayed in the main form or not and the check box might be confusing for use.
Also the fact that i can not adjust the checkbox's size helped in that decision.

I'm going to try both of these tips now.

 

[mamradzelvy]

if TabStatus is a yes/no field, why not just change the control type to a checkbox?

Also as Bob has indicated yes/no fields are numeric - 0=false/no, -1=true/yes

Oh i had it set to "1" instead of "Yes" but i read this on support.office.com :

So i figured i'll give it a try but that didn't initially make no diference in the occuring errors, and as i progressed i forgot the option to revert back to the original "1", which now has resolved my issue and my code:

DoCmd.RunSQL "UPDATE Tabule1 SET TabStatus = '1' WHERE ID LIKE '" & Me!txtID.Value & "';"

now is working correctly!

I'm still going to try out your guys' tips just to have more options for the future, thank you all very much!

 

[deletedT]

Also the fact that i can not adjust the checkbox's size helped in that decision.

You can not change the size of checkbox but if you have problem in center positioning them you can try this.
I set the controls layout to Stacked.
Add a cell to the position I need the checkbox, Click the cell and then split vertically and once again split Horizontally.
It divides the cell into 4 cells. I drag the checkbox to one of the cells and by resizing the cells, I make the position of the checkbox to be centered.

 

[mamradzelvy]

To bypass this problem, I set the controls layout to Stacked.
Add a cell to the position I need the checkbox, Click the cell and then split vertically and once again split Horizentally.
It devides the cell into 4 cells. I drag the checkbox to one of the cells and by resizing the cells, I make the position of the checkbox to be centered.

View attachment 81565

Thank you, however the position itself is not what bothered me regarding the quote you made. It was the inconsistence with the size of other visual objects on my form and the interference with the overall visual layout of my design.