How to connect multiple check boxes in Access 2016 to column in SQL Server?

Question

I have a column on a database in SQL Server 2017 that can hold 5 different values (let's say A, B, C, D, and E). Within SQL Server if two or more are chosen they are separated by a space.

I am creating a form in Access 2016 that I need to connect to each option within this column such that if any checkbox associated with the different values is chosen it will show the box checked on the form and also put the value in the database on the server.

Example: A - unchecked B - checked C - unchecked D - unchecked E - checked

The column shows: B E

Any help would be greatly appreciated.

Edit:

I created 5 new columns associated with A, B, C, D, and E and migrated data into each column where appropriate. So, with this, I still need an Access checkbox to be checked if the data has the letter in its respective column and when checked in Access to enter the letter.

Answer 1

As mentioned in the comments, this is a bad idea. However, if there is no other way, it can be done with a bit of VBA.

Firstly, you will need two procedures - one to get the data out of this field and set the check boxes, and the other to get the status of the check boxes and write back to this field:

 Sub sSetCheckBox()
    Me!chkA = Nz(InStr(Me!CheckData, "A"))
    Me!chkB = Nz(InStr(Me!CheckData, "B"))
    Me!chkC = Nz(InStr(Me!CheckData, "C"))
    Me!chkD = Nz(InStr(Me!CheckData, "D"))
    Me!chkE = Nz(InStr(Me!CheckData, "E"))
End Sub

Sub sGetCheckBox()
    Me!CheckData = ""
    If Nz(Me!chkA) Then Me!CheckData = Me!CheckData & "A"
    If Nz(Me!chkB) Then Me!CheckData = Me!CheckData & "B"
    If Nz(Me!chkC) Then Me!CheckData = Me!CheckData & "C"
    If Nz(Me!chkD) Then Me!CheckData = Me!CheckData & "D"
    If Nz(Me!chkE) Then Me!CheckData = Me!CheckData & "E"
End Sub

You will then need to call the first procedure in the form's Current event, to get the checkboxes displaying correctly when the form opens and when the user navigates between records:

Private Sub Form_Current()
    Call sSetCheckBox
End Sub

And then you will need to use the second procedure when the user clicks on a check box:

Private Sub chkA_Click()
    Call sGetCheckBox
End Sub

Regards,