Do you need to know what record contains the value or not?
-No, it is dynamic records
I think you misunderstood that because you said no then showed which record has the yes value in it (e.g. Record_ID_1)
Getting late here so I will post what I have for you to play with.
I made this a function because it's easier to run from the immediate window. You can change it to a sub if you need to.
The table containing the YES as text is in
tblYesValues - change table name in code to your table name.
The first field in your table of yes values must be a number that id's the record. Since it appears that it is not, this won't work as written. You will have to tweak or we'll deal with it tomorrow.
The table that will show which record id's in that table is
tblYes. Create that with field names:
YesID - autonumber (included in case it's needed; best to have it IMO)
RecID - Long, no default value, not required, no index
FieldName - short text, not required, no index
Do that to test the code. Once you have it working, you can use your own target table and change the table and field names in the sql variable.
Suggest you just paste as function into a standard module. Type function name in immediate window and hit return. Then tweak/alter only after it is working as required. Don't run until you save the project but I would compile first after pasting.
VBA Code:
Function dhen21dx()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim sql As String
On Error GoTo errHandler
Set db = CurrentDb
Set rs = db.OpenRecordset("tblYesValues", dbOpenSnapshot)
rs.MoveFirst
If Not (rs.BOF And rs.EOF) Then
db.Execute "DELETE * FROM tblYes", dbFailOnError
Do Until rs.EOF
For i = 1 To rs.Fields.Count - 1
If rs.Fields(i) = "Yes" Then
sql = "INSERT INTO tblYes (RecID, FieldName) VALUES (" & rs.Fields(0) & ",'" & rs.Fields(i).Name & "')"
db.Execute sql, dbFailOnError
End If
Next i
rs.MoveNext
Loop
End If
exitHere:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Function
