Comparing Data (1 Viewer)

[skwilliams]

I have two tables and input forms. The input form (frmChem) and it's table (tblChem) have various fields and one is a memo field with different chemicals entered but separated by commas. The other form (frmHaz) and table (tblHaz) contain a text box where a chemical is entered to make a list of hazardous ingredients.

Here's what I need to happen. When a record is added to frmChem and the various ingredients are entered in the memo field and separated with commas, the database possibly breaks down the memo field into an array and compares the text with that listed in (frmHaz). If a match is found then a message box is displayed stating that a hazardous ingredient is present.

I don't know how to go about it.

Thanks.

 

[David R]

I don't suppose it's early enough in your project to revamp your structure. You're asking for something that can be done easily with a one:many relationship.

Break off your memo field into its own table (we'll call it tblIngredients). Include a field of compatible type with the PK of tblChem, as well as its OWN PK. I'm assuming here that tblHaz is the lookup table of possible hazardous chemicals. Can you instead make this a table of ALL possible chemical ingredients, and include a checkbox for those that are hazardous?

Now, make a sfmIngredients and include it on your frmChem. I'd run it Continuous or Datasheet. In the blank for ingredient, make this a lookup to the tblChemicals (formerly tblHaz). Let the Lookup wizard do this for you, but make sure you include the 'Hazardous' checkbox in the combo (you can make it hidden).

Now, whenever you're checking for hazardous materials being present (maybe before you finish the record?), use Dcount to see if there are more than 0 records with chkHazardous = -1 in the subform currently.

Hope that helps. Post back if you need more help.

David R