Access VBA DLookup: Syntax error (missing operator) in query expression

Question

I have a form that includes a multi-select checkbox listbox. This field pulls values from a 'Countries' table, which has a 'Current Index' column that I need to examine for each country selected. After the field has been updated, I want to find the lowest 'Current Index' value for the selected countries and populate a separate field, 'Lowest Corruption Index' with this value.

I have attached the following VBA code to the checkbox listbox, but when I test it I get the following error. Can anyone point out what I am missing? I'm assuming it has something to do with how I am concatenating the criteria for the DLookup, but couldn't find any useful guidance on this online.

Private Sub Countries_AfterUpdate()

Dim currentIndex As Variant     'variable to hold index of current country during iteration
Dim countryID As Variant        'variable for iterating through array
Dim arrCountryIDs() As Variant  'array of country IDs
Dim recCountries As Recordset   'Countries table recordset
Dim bytLowestIndex As Byte      'variable to track lowest corruption index of selected countries
Dim strLookupCriteria As String 'workaround for trying to concatenate variable into DLookup criteria

'assign contents of Countries field to array
arrCountryIDs = Me![Countries].Value

'assign lookup countries table as recordset
Set recCountries = CurrentDb.OpenRecordset("look_countries")

'set lowest index variable to highest possible value
bytLowestIndex = 100

'iterate through country ID array, look up index, compare to and update lowest index variable
For Each countryID In arrCountryIDs

    strLookupCriteria = "Country ID = " & countryID

    currentIndex = DLookup("Current Index", "recCountries", strLookupCriteria)

    If currentIndex < bytLowestIndex Then bytLowestIndex = currentIndex

Next countryID

'populate lowest corruption index field
Me![Lowest Corruption Index] = bytLowestIndex

'close recordset and empty all variable
recCountries.Close

Erase arrCountryIDs
Set recCountries = Nothing

End Sub

Update: Just in case this is useful to anyone else in the future, here's my final code...

Private Sub Countries_AfterUpdate()

Dim currentIndex As Variant     'variable to hold index of current country during iteration
Dim countryID As Variant        'variable for iterating through array
Dim arrCountryIDs() As Variant  'array of country IDs
Dim bytLowestIndex As Byte      'variable to track lowest corruption index of selected countries

'assign contents of Countries field to array
arrCountryIDs = Me![Countries].Value

'set lowest index variable to highest possible value
bytLowestIndex = 100

'iterate through country ID array
For Each countryID In arrCountryIDs

    'look up index for this country
    currentIndex = DLookup("[Current Index]", "look_countries", "[Country ID] = " & countryID)

    'update the lowest index variable if this value is lower
    If currentIndex < bytLowestIndex Then bytLowestIndex = currentIndex

Next countryID

'populate lowest corruption index field
Me![Lowest Corruption Index] = bytLowestIndex

'clear the array
Erase arrCountryIDs

End Sub

Answer 1

You use spaces in the field name, thus:

strLookupCriteria = "[Country ID] = " & countryID
currentIndex = DLookup("[Current Index]", "recCountries", strLookupCriteria)