Decode field and generate continuous subform records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field on a main form. It contains a series of three digit codes
that each references a separate value. I have a table that has these values
along with the descriptions for each value. What I would like to do is that
when the main form is opened and a record is chosen on the mainform, the
field with the series of codes would be decoded and then perform a lookup in
a reference table to find the description and then display each record on the
subform.

To do this, I have created two functions. The first function parses this
field for the 3 values and then next 3 values and so on. A second function
is then called from the first to perform a dlookup to the reference table
with descriiptions.......

How do I create record entries within the subform by displaying the final
value from my dlookup function?

Thanks,
 
The only sensible way to do this is to normalize the data structure at some
point, so that you are not storing 3 values in the one field, i.e. you have
a related table that has 3 records instead of storing the 3 values in one
field. You can then join that to your lookup table and show the lookup value
very simply and efficiently.

If you cannot do that, and there are always exactly 3 values, you could join
the table to itself (based on the primary key), and then parse the Left(),
Middle(), and Right() of the field, possibly based on Instr() if they are
not fixed width. This ends up yielding the 3 records, and you can then join
to the lookup table to get the text you want to display. This approach will
be very inefficient (slow to execute).
 
Thanks for the reply. Unfortuantily the data is originally from an AS400 and
will not be changing in structure anytime soon. Also the codes could have 1
three character code or it could have to up about 10 or so. I guess I could
just start witht left$ function and then use mid$ function for the rest with
an if condition. If end of field reach I could insert use a check character
I guess to show that that calculated field is null........

Thanks for the tip. I'll start to work in this direction...
 
You could use the Split() function to parse the field into an array in code,
and then append a record to a local related table for each one. That assumes
you can work with a local copy, i.e. the data is not constantly changing.
 
Back
Top