loop thru recs to update field based on value in table

[leanne123]

I have a table called IDD185. This table has a field called MESSAGE. The MESSAGE field has a message that includes specific information. I have another field called GENERIC MESSAGE. I want to update GENERIC MESSAGE field with the MESSAGE value excluding the specific information. This GENERIC MESSAGE field can then be used to report and pivot on different types of messages.

ex.
The Message field would have:
IDD185 - Import Shipment Sales Order 0007752519 Successfully Created

0007752519 is the specific information.

Right now it is working by creating a query with wild cards for each type of message that identifies all records with like messages and then updates the GENERIC MESSAGE with just the generic information:

BEGIN QUERY
UPDATE IDD185 SET IDD185.GenericMessage = "IDD185 - Import Shipment Sales Order Successfully Created"
WHERE (((IDD185.GenericMessage) Is Null) AND ((IDD185.Status)=53) AND ((IDD185.Message) Like "*" & "Successfully Created"));
END QUERY

Rather then create a query for each one, I would like to house this information in a table and have some vb code loop thru the table, finding each record that matches the criteria and update the GENERIC MESSAGE field.

For below message:
IDD185 - Import Shipment Sales Order 0007752519 Successfully Created

The Table Messages entry would be:
MessageIdentifier
Like "*" & "Successfully Created"

Generic Message
IDD185 Import Shipment Sales Order Successfully Created

I am not great with loop code, please get me started.

 

[BNPMike]

I suggest you first define the generic messages eg Order Created (you don't need to waste space with IDD185 etc). Then define search strings like you have done above. Then you can write a query that has a series of ORs updating Generic Message to "Order Created" where Message Like "*" & "Successfully Created" etc etc. SQL does the looping for you.

I can't remember what the limit on ORs is. If you need more, first collect the search strings by string manipulation, then join them to a table with the generic messages in.

 

[leanne123]

Problem is I will always be getting new messages. I don't want to hard code these message identifiers anywhere, rather would like to store them in a table accessible by the user so they can update strings when new messages come thru.

I have already built the table, it has 2 fields, the message identfier with the wild cards and the Generic Message (without the specific data).

Now all I need is a code that will translate the actual message to the generic message using the message identifier field.