How to create this type of lookup function? (1 Viewer)

[hclifford]

Hi all,

I have 2 tables.
Table 1 contains 2 columns: Details and event type.
Table 2 contains 2 columns: Lookup and event type.

I am required to write a code that will allow me to read the string of each record in the details column, and if the string contains a certain text, it will input the event type based on that.

For example:
If details column contains ":MD", it will input "Database into the event type column.

What I need the code to do is to actually, for each record in Table 1, cycle through (from the first record of the column Lookup to the last) Table 2 and see if any of the text in Details matches the text in Lookup. If there is a match, the event type in Table 2 will be input into the event type in Table 1. If there isn't a match, a null value will be input into the event type.

I know that this is possible just by using if-else statements and there is no need for Table 2. But I am working in the sense that it would be easier for the user to update the lookup list anytime there's a new event.

 

[TJPoorman]

Something like this?

Public Function test()
Dim rsLookup As Recordset

Set rsLookup = CurrentDb.OpenRecordset("SELECT * FROM LookupTable")
Do While Not rsLookup.EOF
    CurrentDb.Execute ("UPDATE DetailsTable SET EventType='" & rsLookup!EventType & "' WHERE Details Like '*" & rs!Lookup & "*'")
    rsLookup.MoveNext
Loop
End Function

 

[hclifford]

Something like this?

Public Function test()
Dim rsLookup As Recordset

Set rsLookup = CurrentDb.OpenRecordset("SELECT * FROM LookupTable")
Do While Not rsLookup.EOF
    CurrentDb.Execute ("UPDATE DetailsTable SET EventType='" & rsLookup!EventType & "' WHERE Details Like '*" & rs!Lookup & "*'")
    rsLookup.MoveNext
Loop
End Function

Hi!

Thanks for replying my question!

I just tried your code, and it works to a certain extent. However, it only inserts the correct event type for the first record. Is it possible to do it such that for each record, the code will loop through the lookup table and the loop will end at the first occurrence of a match before moving on to the next record and looping again?

I have attached a photo showing the result of the code that you provided.