Interrogating Multi-Valued Fields in Access (1 Viewer)

[PhaseNine]

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.

 

[The_Doc_Man]

In general, what you describe isn't an accurate picture of reality. No field is ALLOWED to be multi-valued in a single row. This would violate the basic concept of Access, that you can find a single datum from a table with a single key.

What you describe is probably one of two things: a sub-form in continuous display mode, in which each row is a different record, or perhaps a list-box or combo-box with multiple columns displayed.

In a control such as a combobox or listbox, there are potentially multiple rows that can contain distinct values. You can look at the help text for these two control types which are only allowed on forms. Basically, there is a row index that lets you pick a row, and there is a column index that lets you pick a particular column. You can determine by iterating through the control's rows whether a particular row has been selected (for the multi-select list box case). Combo boxes don't have multi-select so you can just look up the row index of the selected row.

The other case is the continuous form. To find a specific column or row, query that form's recordsource with a VBA sequence that opens the source as a recordset (more specifically, as a Dynaset). Then pick up your stuff via recordset operations.

The problem with trying to grab something when a given row doesn't have focus is that in the continuous form case, I don't think such a syntax exists. But the recordset can be built and used to query the same table. Or, if the sub-form can be found using the right syntax, you could also look at the sub-form's .RecordsetClone to search the same recordset.

 

[boblarson]

Sorry Doc But it is a multivalued field. It is Access 2007 and it has such. It really is stored in a hidden system table and therefore makes it a pain in the butt compared to just using a normal junction table. I, and most other developers, don't like that they put them into Access 2007/2010.

 

[The_Doc_Man]

Must be a new feature that I missed. I instantly hate it because it defies all logic of normalization as I understand the rules of such.

 

[boblarson]

Must be a new feature that I missed. I instantly hate it because it defies all logic of normalization as I understand the rules of such.

Yep, as do many of us, because it requires a lot more work to deal with it than it does in a more open method. It still stores the data in a separate table but you don't have as simple of methods to access it.

 

[Galaxiom]

The debate about the politics of multivalue field is all very well but nobody has actually answered the original question.

The multivalue field in Access is much maligned for its departure from the relational model. It can be rigourously implimented as per the relational model as a many to many join with a junction table and most developers prefer this approach.

Access multivalues are not really the same as a multivalue field in UniData. Access multivalues must be drawn from a lookup of possible values. In this way they are just an easy way to impliment a form with many to many relationships. They come at a cost of being much more difficult to query. Personally I feel they have little to offer.

Although UniData multivalues can be used in this way they are more typically specific values for a particular attribute. Essentally they are an integrated one to one relationship that harks back to when tables were store in delimited text lists. They work because UniData has the means to query them.

I have not used Access multivalues but I imagine they can be queried by parsing the the field to determine the presence of a particular value. However the need to do this would indicate the that a traditional structure would be better suited.

Here is a page about point behind Access mutivalue fileds.
http://office.microsoft.com/en-au/access/HA100311171033.aspx

 

[PhaseNine]

Thanks everyone for all your responses. I suppose I need to not only learn new syntax and features with VB, but do a complete re-framing since my Unidata days. I have to get my head out of the "delimited" style database and into the world of the SQL / Access database.

One thing that I was thinking was that, while both the main form and the subform were "on screen", that the values being displayed were stored in memory somewhere and accessible to the VB program. I know this is true to some extent, but in the case of the Category Id & Descriptions fields I guess only one row is stored in memory at a time so that when one references either CategoryId or Description it will only return the row value that has focus. However, one thing I have to remember is that the values stored in the CategoryId & Description fields will already exist in the junction table and I can just do a regular table query if I need to interrogate any of these values.

Thanks again everyone for all the input.

 

[The_Doc_Man]

However, one thing I have to remember is that the values stored in the CategoryId & Description fields will already exist in the junction table and I can just do a regular table query if I need to interrogate any of these values.

I think this is an excellent insight and may reveal an easier path for you to get where you want to go. I was not aware of the multi-valued field ability because my site has not switched to Ac2007 yet. As Bob points out, since it deviates from a "pure" relational mode, it is a bit more difficult to manage. My comments earlier were based on somewhat more traditional relational model and I think you can reach your goals better that way anyway. At least, the syntax for that is better understood.

 

[Galaxiom]

As I understand it Access stores records in "pages" of 25 records. However it isn't really so important how values are stored because we don't interact with the data at that level as was done in the days of csv tables. The methods provided to retreive the values are what really counts.

The art is in the way those methods are applied to the structure such as selecting the table on one side of the junction table where the least amount of data needs to be retreived before applying the join rather than working directly at at data on the many side.

As you have said the pointer to the current record in the recordset is the focus but the certainly not the only source. However do try to work within the recordsource query whenever possible as this is the most rapidly accessible data. Remember the recordset can contain fields that are not displayed on the form.

Another important aspect of the recordset is the RecordsetClone. It is a copy of the recordset that can be navigated independently and is combined with the Bookmark in many search techniques.

With VBA you will find there is a way to do anything that you can imagine. It is just a matter of finding it. Google is the developer's best friend.

BTW Welcome to the site. You will find that on the whole people here are incredibly supportive and contribute from an incredible breadth of experience.

 

[JamesMcS]

Would this be as easy as just using a query? Not teaching grandma to suck eggs here but I thought that was the primary purpose of a query - to retrieve data from a recordset.

Oh, if the mods are checking this thread out now, please could you create some sort of pointless argument forum so people can have a go at each other without getting in the way of others learning about Access? I've seen plenty of slaggings-off from one of the users on this thread, and it's getting a bit old now...

 

[JamesMcS]

However sometimes hilarious, Galaxiom, love it

 

[Galaxiom]

Would this be as easy as just using a query? Not teaching grandma to suck eggs here but I thought that was the primary purpose of a query - to retrieve data from a recordset.

Not sure of the context of your post James but I assume you mean the reference to RecordsetClone and Bookmark. These are used to move the focus in the form to the record designated by the search. It is usually tidier than opening another form based on another query.

 

[JamesMcS]

Ah, makes sense now, that was the result I was going for.... thanks!