Datasheet View insert value in to field when checkbox is checked

Question

I have a form as a Datasheet View and inside I have added a column for checkbox.

Let us say that the column with check-boxes is column A and the one from the right it is column B.

How can I insert some text in column B when the checkbox from column A is marked as checked. I need somehow to find out the current row on witch the checkbox is and take the ID for the record from the row on with checkobox is and run a SQL insert for that specific ID in the database table, something like: SQL = "UPDATE table SET columnB='string' WHERE ID= ROWid;"

How can I do the row selection part from the datasheet view?

Thinkman Thinkman · Accepted Answer · 2015-11-10T20:16:02

Basically Access is not working with rows but with datasets. This means that particularly here Access is reacting very differently than Excel.

Do you have only one single checkbox checked or do you allow the user for checking different checkboxes before inserting your desired value?

Add AfterUpdate Event on Checkbox and add:

Private Sub Checkbox_AfterUpdate()

Dim strUser As String

strUser = CurrentUser()

If [Checkbox] = True Then
    [UserName] = strUser
Else
    [UserName] = ""
End If
End Sub

[UserName] is your (text)field in the right colum. With a user management you can use the login name. If not you could use the current user or the Windows login name.

CurrentUser() is mostly returning "Admin". If you're looking for the windows username have a look here:

Retrieve the user name from Windows

Datasheet View insert value in to field when checkbox is checked

1 Answers