0

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

Datasheet View rows ms-access

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?

Starlays
  • 1,039
  • 2
  • 15
  • 29

1 Answers1

2

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

Thinkman
  • 58
  • 9
  • Hello. I want to let the user click one one single check-box, for example the one that it highlighted with red, and after that to insert the username, on the right column, of the user that had checked that box, it is literally a check, a mark, that shows that the user has verified that specific record. – Starlays Nov 10 '15 at 20:21
  • Add a After-Update Event on the checkbox and add a vba code like: Nz([UserName],0)="username" – Thinkman Nov 10 '15 at 20:26
  • like? I think it is better to edit your answer and put the solution in there so I can upvote your answer so you will earn the vote points. – Starlays Nov 10 '15 at 20:29
  • Yes like:-)...I've no detailed information about the field names in your db. Use the code stated in my answer and adjust accordingly. Cheers – Thinkman Nov 10 '15 at 21:39
  • Simpler way to get the Windows username: `GetUserName = CreateObject("WScript.Network").UserName` . See http://stackoverflow.com/questions/32562670/how-to-use-system-username-directly-in-ms-access-query/32565953#32565953 - thanks to HansUp. – Andre Nov 10 '15 at 23:12
  • Thank you, I already took the current logged in user in windows but this is a different situation, i need to fill a cell with that information when a user clicks on the check box. – Starlays Nov 15 '15 at 13:37