CODEWITHSUNDEEP
ms-accessms-access-2007vba
Patrick
Patrick

Reputation: 113

MS Access 2007 - Changing a checkbox to be unchecked

I have an unbound text box that I want to enter a number in and then it would go in a form. When I do this, I want to change the status of checkbox in another form as well. So it is like inventory. When I add this item to this box, I need the checkbox value to change because it's no longer in inventory. So the form I have is 'Info' and the checkbox is named 'ChkboxAvailableUse'. I have the checkbox correlated to a kit number and location and stuff too. So when I enter the number in an unbound text box called 'AssignKit', I want that number to look for the same kit number the 'Info' form and the 'InvKitNumber' text field, and then change that records checkbox (ChkboxAvailableUse) to change to false. I hope this makes sense. I have the line of code I thought would work below. Any help would be fantastic. Thank you

CurrentDb.Execute " UPDATE Info SET ChkboxAvailableUse = FALSE WHERE InvKitNumber = " & Me.AssignKit & ""

Upvotes: 0

Views: 3536

Answers (2)

Grant
Grant

Reputation: 903

If you just want that form to uncheck or check in the table to match your form or subfrom then you could use a recordset.

Dim myR as Recordset
Dim strSQL as String

'This SELECT string will pull from the assignkit field in the subform 
'and will be used find the matching record in the table
strSQL = "SELECT * FROM info WHERE InvKitNumber = '" & Me!subformnamehere.Form.AssignKit & "'"

'This will make your recordeset variable the one record you want to modify
Set myR = db.OpendRecordset(strSQL, dbOpenDynaset)

'Now that the recordset is pointing to that row, you can change the checkbox
'with something like this, and even use an IF statement
myR.Edit
myR![ChkboxAvailable] = True 
myR.Update

'Then close the recordeset when done
Set myR = Nothing

I hope this helps, let me know if you need me to tweek it.

Upvotes: 1

Andy G
Andy G

Reputation: 19367

The sql UPDATE statement updates fields in a table. Tables don't have checkboxes, forms do, and I assume that the field (in the table) is not called ChkboxAvailableUse ?

If the field is named AvailableUse then you would update this field with:

CurrentDb.Execute "UPDATE Info SET AvailableUse = FALSE WHERE InvKitNumber = " & Me.AssignKit

Then you could ReQuery the (other) form (or a control on the form) so that it reflects these changes.

Upvotes: 0

Related Questions