How to use VBA variable in SQL string? Enter Parameter Value error

I get a popup box asking for the parameter value for the value of CXIid

Enter Parameter Value
for CXI00012.

I tried ' " & CXIid & " ' but then I get a result of 0 rows being updated. However when I put a value in the where clause or into the Enter Parameter Value prompt I get the correct row updated.

How do I get it to recognize CXIid as a value?

Private Sub cmdSave_Click()

Dim CXIid As String
Dim testSQL As String

CXIid = Form_sf_ParticipantView.CXI_ID

testSQL = "UPDATE [tbl_Participants]" & _
            "SET tbl_Participants.Consent = (Forms.frmReturn.cboConsent.value)" & _
            "WHERE ((tbl_Participants.CXI_ID = " & CXIid & " ));"

DoCmd.RunSQL testSQL

Answer

You have a few different types of mistakes… the variables cannot be within the quotes, and since the field CXI_ID is a String, it does need quotes.

This assumes that the Consent field is numeric and the combobox is returning a numeric value as well.

testSQL = "UPDATE [tbl_Participants]" & _
          " SET tbl_Participants.Consent = " & Forms.frmReturn.cboConsent.value & _
          " WHERE tbl_Participants.CXI_ID = '" & CXIid & "'"

_{Also note the spaces I added before SET and WHERE. Those are important.}

Use MsgBox testSQL or Debug.Print testSQL to double check what testSQL is set to before you run it.

If the field CXI_ID is not a string but you are just using a string as the type of variable holding the value for some reason:

testSQL = "UPDATE [tbl_Participants]" & _
          " SET tbl_Participants.Consent = " & Forms.frmReturn.cboConsent.value & _
          " WHERE tbl_Participants.CXI_ID = " & CXIid 

If they are both strings:

testSQL = "UPDATE [tbl_Participants]" & _
          " SET tbl_Participants.Consent = '" & Forms.frmReturn.cboConsent.value  & "'" & _
          " WHERE tbl_Participants.CXI_ID = '" & CXIid & "'"