Hello,
I would like to extract a value from a particular row for a field that contains more than one value (i.e. multiple rows).
I will first describe the setup. The main form is called “Extract” and the subform is called “ExtractCategory”. The ExtractCategory subform contains two fields called “CategoryId” and “Description”. Both of these fields can contain multiple values. In this case, the ExtractCategory Table has a many-to-many relationship with the Extract table (i.e. many categories can be applied to many extracts).
For example, the screen may look something like the following:
Extract Id: 001
Extract Text: Blah blah blah ….
Category Id Description
001 Finance
002 Taxes
003 Exemption
I would like to be able to extract a value at any row from the Description field within a VB program. If I just refer to the description field in VB it only returns the value which currently has focus. So, if the user is on the second row, referring to the Description will return the value “Taxes”. However, I may want to obtain the value in the third row, the first row, etc.
Likewise, when I am in the Extract form and attempt to interrogate the Description field (using the command “Forms![Extract]![ExtractCategory].Form![Description]” it always returns the first row (which in the example above would be “Finance”).
I have looked for an answer to this questions but as of yet have come up empty. I have also tried referring to description as an array (such as Description(3) or Description[3]) but nothing appears to allow me to do this.
My questions are as follows:
1. How do I extract a value from a particular row for a field with multiple values / rows within the active form.
2. How would the statement “Forms![Extract]![ExtractCategory ].Form![Description]” have to be modified in order to effect the same purpose as in the first question. In this case the “ExtractCategory” subform would not have focus and I would want to extract the value while the operator was in the “Extract” form.
I apologize if I am using the term multi-valued incorrectly. However, in the Unidata environment where I used to work it was standard terminology to refer to a field that contained multiple values as a multi-valued field. In the case of Unidata, each value would be separated by a delimiter and one could refer to each value as follows:
Description<1> for the first value “Finance”.
Description<2> for the second value “Taxes”, etc.
Thanks in advance for any help with this problem.