Check fields from hidden form and change data on original form

[crazyhouse]

Ok, so i looked up the information that i needed, but now I see that what i was trying to do was really made for a SQL statement.

Here is what happens. I open a form (hidden) with 20+ fields on it. Any of the fields may have a term on it. (MPI or LPI) <-- text obviously. If they exist in any of the fields, then i need to make a label visible on the form that is actually going to be used. Then the hidden form closes.

The code i tried unsuccesfully to use looked like this

 
if [op1] like (*MPI*) or if [op2] like (*MPI*) then
forms!otherform.label = true
end if
if [op1] like (*LPI*) or if [op2] like (*LPI*) then
forms!otherform.label2 = true
end if

the only time it worked is when i made it look like this

 
if [op1] like (*) then

But of course then it doesnt matter what is in the field.
Is there a way to accomplish what i am doing. I am sure that there is a complicated method of doing it..... I am also quite sure that i am not doing it very efficiently, but I try to actually learn how to read the code that i use, so that i will learn it. So the easier the better :), but if complicated is the only way, then complicated it is. Thanks in advance.

[Stewart Ross]

Hi. If you want to compare string control values to string literal text values you need to enclose the string literals in double quotes:

if ([op1] like "*LPI*") OR ([op1] like "*DPI*") then
   ...
End If

(see our insight article Quotes and Double Quotes - Where and When to Use Them for more on this.)

The use of the wildcard character "*" before and after the pattern allows you to find any occurrence of the given pattern within any other characters in the string - for example "Printer; DPI". You would not need the wildcards before and after the pattern if it is never preceded by or followed by other characters in your string, but you don't say whether or not this would apply in your case.

With 20 controls in your hidden form you are likely to end up with a lot of IF statements. There are more efficient ways to process these, depending on how you name the controls and what you are doing with them. One way is to ensure that they are named, say, Op1, Op2, Op3..Op20. These can then be processed in a loop, like this:

Dim strCtrlName as String
Dim intCtrlNo as Integer
Dim varCtrlValue as Variant
For intCtrlNo = 1 to 20
    strCtrlName = "Op" & intCtrlNo ' this just concatenates the control name with the number to form Op1, Op2 etc
    varCtrlValue =  me.controls(strCtrlName)
    if not IsNull(varCtrlValue) then
       if (varCtrlValue like "*DPI*) or (varCtrlValue like "*LPI*) then
         <do stuff>
       end if
    end if 
Next intCtrlNo

There are many possibilities - I am sure there are other approaches that would work just as well that others could think of.

-Stewart