Referencing query columns in a module function

[Jack Spratt]

I have a lot of query fields that I need to compare in a function that I call as part of an expression in a db query column.

I am wondering if it is possible to pass the entire row to the vb function (or can it somehow be accessed) ?

This is what i'd like to do (if this was the right syntax/approach)

something like this in the query column:

=GetGroupDescription([myrow])

something like this in the module:

function GetGroupDescription(myrow As DBROW)

if DBROW.Flag1 = true then GetGroupDescription = "group 1"
if DBROW.Flag2 = true then GetGroupDescription = "group 2"
if DBROW.Flag3 = true then GetGroupDescription = "group 3"

End Function

(the compares are much more complex than this example and involve thirty or more flags !)


thanks

 

[RuralGuy]

Have you considered just opening a RecordSet and walking throught it in code rather than using a query?

 

[Jack Spratt]

Thanks RG but this query is invoked when filling a combo box - also this 'translation' of flags into descriptive text will be needed in other queries. I am keen to set up a function that can be used in any query and by non technical report writers.

 

[RuralGuy]

Admittedly, I do not know your particular criteria, but if there is only one interpretation of the assortment of flags, this may be a situation where it is better to have a existing field with the decode rather than decoding it on the fly.

 

[Jack Spratt]

Good point - yes, and that was considered but (so far !) rejected for other reasons.

So are you saying that it is not possible to 'pass' a whole query row down to a user function ? I would have thought there would have been an Access object of some sort that would be around at this point.

 

[RuralGuy]

I've never done it and so far my research has not turned up the solution. It seems like you should be able to do it somehow. You could certainly pass a unique key to a function and then have the function open a recordset and do a .FindFirst to get to the record.

 

[Jack Spratt]

thanks for the suggestions RG - I may well end up 'decoding' when added or changed for the moment as it's a horrible thought to be passing so many flags !

 

[RuralGuy]

Well for maintainability, I would sure try and keep the encode/decode code in one place in your application.

 

[RuralGuy]

Perhaps if you could describe what the flags mean and how they're set/reset, someone could suggest an easier or more flexable approach.

 

[Jack Spratt]

thanks, I can do all this in one place for now - so reasonably neat and not difficult.

 

[ajetrumpet]

I have a lot of query fields that I need to compare in a function that I call as part of an expression in a db query column.

You want to call a function while inside of an expression?

I am wondering if it is possible to pass the entire row to the vb function

Just asking, but why do you want to do this? Have you written a function that affects all the fields being queried? Is that why you want to pass the entire row's data to the function?
What do the flags represent? A yes/no field (flag = true?)?

You got me curious...

 

[The_Doc_Man]

I checked my help files. While there is a "recordset" object that could be passed to a given routine, there is no "record" object.

I can think of a few ugly ways to do this.

1. Encode the flags into a string and pass the string. Tedious but certainly possible.

2. Store the flags values in an array and pass the array. Tedious and possible but subject to issues if you ever get the array indexes confused. Or if you must add a new flag at some point.

3. Pass in a recordset clone (see Access help) and a record number to choose within that recordset. Of course, the record number points to the flags in question.

4. With thirty or more flags, you might be looking at a seriously un-normalized database. These flags sound like a "repeating group" - which would violate first-normal form. It's a tossup based on your description as to whether this is or is not a true violation of 1NF, but it SOUNDS that way.

5. Your example simplifies things so much that it leads me to questions. One of which is, what do you do when flag1 and flag2 and flag3 are ALL true?

a. If that can't happen, then your flags are NOT normalized and you can replace them all with a single "state" variable. (Think "radio button" as the nearest Access analog.)

b. If that CAN happen, you have a complex problem for which IF-ladders are going to be tedious and inexact. No matter HOW you manage this one, I will strongly urge you to reconsider that your design is in the way of your solution. Time to redesign the data set.

c. If you are not allowed to reconsider the data design, SERIOUSLY consider starting a fist-fight with the idiot who won't recognize that his lovely data design is eating your socks. Bring brass knuckles. You'll lose in the long run, but at least one idiot designer will have gotten a sound thrashing that was richly deserved.

OK, let's NOT get violent. But... if you overconstrain the solutions allowed for your problem you will find that it will take you literally dozens of times longer to resolve the problem.

I'm not being one bit facetious in this next statement: I have found in the past that if you have an insoluble problem, change the problem in small steps until you can find a solution for the modified problem. Then (using the small steps as a guideline), change the problem so that you always implement the steps that worked in terms of making the problem tractable. This is a "divide and conquer" approach.

 

[Jack Spratt]

Adam -
the flags are indeed yes/no fields and the table is simply 'contact' information, however the contact has a series of attributes (one per flag) and this text would describe these attributes in a standard way. The volume of data is not great (under 1000) and so i'm not worried about efficiency.

Another function might look at these flags and decide a sort sequence priority.

There is no requirement to change other fields in the row, just read-only access to columns specified in the query - this function could then used in a calculated field/expression within any query involving that table - not unlike a db procedure.

 

[Jack Spratt]

Doc Man - in terms of design the thirty flags don't violate any basic principles, think of them as a list of specific abilities - such as 'likes to swim' 'likes to ride' 'likes to jog' etc. and the descriptive text might result in 'Active Leisure Sports Enthusiast'.

But hey - your comments are excellent - (they should be put on a 'sticky' somewhere !) I may have only been on this forum for a few posts but I have many years experience at this and you are so right ! Particularly the teasing out of problems, bit by bit.

 

[Rabbie]

Doc Man - in terms of design the thirty flags don't violate any basic principles, think of them as a list of specific abilities - such as 'likes to swim' 'likes to ride' 'likes to jog' etc. and the descriptive text might result in 'Active Leisure Sports Enthusiast'.

But hey - your comments are excellent - (they should be put on a 'sticky' somewhere !) I may have only been on this forum for a few posts but I have many years experience at this and you are so right ! Particularly the teasing out of problems, bit by bit.

It sill looks like a breach of normalisation. What happens if you decide to add another activity ie 'likes to roller-skate' etc. it looks like you need to take a careful look at your design or you will be storing up trouble for the future

 

[Jack Spratt]

Rabbie - thanks, but this is not the problem I have - I just would like to access columns in a VB Module function, when called from a db query.

 

[Jack Spratt]

Thanks Pat, it seems passing the pk is the only way.

 

[ajetrumpet]

the flags are indeed yes/no fields and the table is simply 'contact' information, however the contact has a series of attributes (one per flag) and this text would describe these attributes in a standard way.

I understand this. New column = a word describing the data in the row = value obtained from the expression (which contains the function inside it).

Another function might look at these flags and decide a sort sequence priority.

I have NO IDEA what you said here. Could you repeat it in English?

There is no requirement to change other fields in the row, just read-only access to columns specified in the query

READ-ONLY Access? As in, just performing the function and retrieving the value from it? Are you trying to tell me here, that the function itself has no effect on the output of the other column data?

This still has me curious, and if you want a solution, or something even close to it, or maybe just a little more insight, why don't you post the function code? There is too much generalisation going on here to really answer any part of this without seeing that code and understanding how it relates to the columns being queried.

 

[The_Doc_Man]

Rabbie and I both agree. This is NOT normalized.

I can see a really simple table of attributes:

tblLikesTo
PersonID, foreign key to person table
AttributeID, foreign key from an attribute table (list of currently known attributes)

If all of these are YES/NO, then only have entries if the person likes to do the corresponding attribute.

Now, how do you correlate two entries?

Find the JOIN of the attribute table to itself for personID A and personID B, which tells you how many attributes match. Gives you a score, sort of, and allows you have Access do the counting.

If the attributes are more than just yes/no, you need another field as a "meter" to show strength of preference, but can still compute differences between the attributes for two parent records A & B

Nobody said it would be easy, but it is certainly possible to do. Form the two recordsets as the result of queries, then compare recordsets based on the selected entries in the child tables.

 

[Jack Spratt]

The Doc Man - thank you for coming back with these ideas - for now I'm just going to let the user both set the flags and also choose from standard text wordings that they can 'decode'. I'm sure it could be done along the lines that you suggest, but it's a tiny part of the system.

My main focus now is on figuring out how to store and analyze a quite separate set of survey questionnaire results - with ratings, weightings and goodness knows what else ! So as Arnie says - "I'll be back !"