0

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
2
  • can you show an example of strLookupCriteria at error please. Commented Mar 27, 2017 at 9:51
  • I think you'll need [Country ID]= Commented Mar 27, 2017 at 9:58

1 Answer 1

1

You use spaces in the field name, thus:

strLookupCriteria = "[Country ID] = " & countryID
currentIndex = DLookup("[Current Index]", "recCountries", strLookupCriteria)
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks. When I added in the brackets as you suggested, I received a different error about being unable to locate the record set 'recCountries'. Since that is a variable defined in the subroutine I tried removing the quotation marks. Now I am getting a Type mismatch error on the DLookup instead.
I figured this out and was able to simplify my code. Turns out I did not need to open up the look_countries table as a dataset. See original post for the completed code.
Oh, that makes sense.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.