Dynamic Class Property Name

[Amy Blankenship]

I apologize in advance for the cross posting, but I'm trying to widen the
set of eyes that is able to see this from
microsoft.public.access.formscoding, since I haven't gotten an answer there
yet.

I have a requirement to essentially take the things in a column returned by
a query and reorder them as rows. The rows aren't necessarily in the same
order as the columns, and some require processing. Once all the rows are
assembled, I need to write them all out to file.

To accomplish this, I created a class with properties corresponding to each
row that I need. I run the query and populate the object. I've also
created an array that contains the names of the properties I want to add to
the file, in the order I want them, but I am at a loss as to the syntax to
use to use a string instead of dot syntax to get at these properties.

What I have now is

'set up array of properties to write to file
Dim propArray As Variant, prop As String

propArray = Array("qCount", "qAttempted", "qViewed", "qAnswers",
"checkedButtons", _
"numCrossedOut", "correctCrossedOut", "incorrectCrossedOut",
"markedAs", "crossList", _
"aX", "bx", "cX", "dX", "eX", "isCorrect", "visit1Time",
"otherVisitTime")

For i = 0 To UBound(propArray)
Debug.Print propArray(i)
If i > 0 Then fileContents = fileContents & vbCr
fileContents = fileContents & propArray(i) & ": "
fileContents = fileContents & propArray(i) &
reportInfo(propArray(i))
Next

but this isn't working.

Thanks;

Amy

 

[akphidelt]

I had a similiar problem I think. If it's the same problem I had then I would
look in to Union queries. They are what saved me and worked like a charm.

 

[Amy Blankenship]

I had a similiar problem I think. If it's the same problem I had then I
would
look in to Union queries. They are what saved me and worked like a charm.

Your answer had nothing to do with my question, but I appreciate the effort.

 

[Robert Morley]

Well, other than one minor problem with your code (you've unnecessarily included propArray(i) twice), I don't see any reason why it
wouldn't work. Just to be safe, I'd specify the "Fields" property in what I'm assuming is your recordset (reportInfo). With those
two things put together, your last line in the loop would look like this:

fileContents = fileContents & reportInfo.Fields(propArray(i))

That might fix the problem, but I don't think so. If you could post a sample of the output you're getting, along with the output
you actually want, that would be very helpful.

Oh, and akphidelt's post wasn't as off-topic as it might appear at first glance. What he was proposing, assuming I understood
correctly, was completely re-doing your problem from scratch using something like the following query instead of VBA code:

'Give a query with columns a, b, and c, which you want to be a single column:

SELECT a AS Combined FROM MyQuery
UNION ALL
SELECT b AS Combined FROM MyQuery
UNION ALL
SELECT c AS Combined FROM MyQuery

(Note that at least in Access, the second & third "AS Combined" are redundant, but I like to put them in there both for clarity and
in case I change the order of the Union clauses.)



Rob

 

[James A. Fortune]

I apologize in advance for the cross posting, but I'm trying to widen the
set of eyes that is able to see this from
microsoft.public.access.formscoding, since I haven't gotten an answer there
yet.

I have a requirement to essentially take the things in a column returned by
a query and reorder them as rows. The rows aren't necessarily in the same
order as the columns, and some require processing. Once all the rows are
assembled, I need to write them all out to file.

To accomplish this, I created a class with properties corresponding to each
row that I need. I run the query and populate the object. I've also
created an array that contains the names of the properties I want to add to
the file, in the order I want them, but I am at a loss as to the syntax to
use to use a string instead of dot syntax to get at these properties.

What I have now is

'set up array of properties to write to file
Dim propArray As Variant, prop As String

propArray = Array("qCount", "qAttempted", "qViewed", "qAnswers",
"checkedButtons", _
"numCrossedOut", "correctCrossedOut", "incorrectCrossedOut",
"markedAs", "crossList", _
"aX", "bx", "cX", "dX", "eX", "isCorrect", "visit1Time",
"otherVisitTime")

For i = 0 To UBound(propArray)
Debug.Print propArray(i)
If i > 0 Then fileContents = fileContents & vbCr
fileContents = fileContents & propArray(i) & ": "
fileContents = fileContents & propArray(i) &
reportInfo(propArray(i))
Next

but this isn't working.

Thanks;

Amy

Amy,

What you have shown appears to be correct. I'll ramble a bit so that
you might see how to frame a better question.

You've got an e-learning app (I was able to verify this by reading a
page that pointed to your blog) and wish to run a summary query to
populate a file.

The syntax for your propArray is correct. So you need to get the
information from the summary query into an array so that it can be
written to the file. Then you need to be able to write your
fileContents string to that file.

Here's one way to do all that (air code done entirely in Notepad):

Public Sub cmdCreateTable_Click()
Dim propArray As Variant
Dim ReportInfo() As String
Dim MyDB As DAO.Database
Dim strSQL As String
Dim qRS As DAO.Recordset
Dim i As Integer
Dim strFileName As String
Dim FileNum As Integer
Dim qCount As Integer
Dim qAttempted As Integer
....

Open strFileName For Output As #FileNum
propArray = Array("qCount", "qAttempted", "qViewed", "qAnswers",
"checkedButtons", "numCrossedOut", "correctCrossedOut",
"incorrectCrossedOut", "markedAs", "crossList", "aX", "bx", "cX", "dX",
"eX", "isCorrect", "visit1Time", "otherVisitTime")
ReDim ReportInfo(UBound(propArray))
Set MyDB = CurrentDb
strSQL = "SELECT Count(*) AS qCount, Sum(IIf([Attempted] = 1, 0) As
qAttempted, ... FROM tblTests WHERE UID = " &
Forms!frmUserNavigation!cbxTestTaker.Column(0) & TestNumber = " &
Forms!frmUserNavigation!cbxTestNumber.Value & ";"
Set qRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
qCount = 0
qAttempted = 0
....
If qRS.RecordCount > 0 Then
qRS.MoveFirst
FileNum = FreeFile()
strFileName = "C:\UID" &
Forms!frmUserNavigation!cbxTestTaker.Column(0) & "Test" &
Forms!frmUserNavigation!cbxTestNumber.Value & ".txt"
For i = 0 To UBound(propArray)
If i > 0 Then
Print #FileNum, propArray(i) & ":" & qRS(PropArray(i))
End If
Next i
Close FileNum
End If
qRS.Close
Set qRS = Nothing
Set MyDB = Nothing
End Sub

If the query returns a record, the code should write a file (called
e.g., C:\UID82Test5.txt) containing something like:

qCount: 100
qAttempted: 70
qViewed: 70
....

I hope this helps,

James A. Fortune
(e-mail address removed)

 

[Amy Blankenship]

Well, other than one minor problem with your code (you've unnecessarily
included propArray(i) twice), I don't see any reason why it wouldn't work.
Just to be safe, I'd specify the "Fields" property in what I'm assuming is
your recordset (reportInfo). With those two things put together, your
last line in the loop would look like this:

fileContents = fileContents & reportInfo.Fields(propArray(i))

Except that now I want to make sure the properties are being printed out in
the correct order, so I want the name of the property to appear on each row
until I have something that works. Plus, reportInfo does not have a Fields
property. Do you know what I mean when I say I wrote a class to handle
this? Essentially it is an associative array with methods. I'd rather not
revamp my code to use a Dictionary, because then I'd lose the extra methods.
Is it possible to write a Class in VB that extends Dictionary? Information
on Classes is very hard to find for VBA.

That might fix the problem, but I don't think so. If you could post a
sample of the output you're getting, along with the output you actually
want, that would be very helpful.

I'm not getting anything.
reportInfo(propArray(i)) 'Runtime error 438 (object does not support this
property or method)
reportInfo.propArray(i) 'Compile error (Expected identifier or bracketed
expressopm
reportInfo[propArray(i)] 'Runtime error 2464 (Microsoft Access cannot find
the field '|' referred to in your expression

That is why I asked for the proper syntax...because the syntaxes I have
tried didn't work.

Oh, and akphidelt's post wasn't as off-topic as it might appear at first
glance. What he was proposing, assuming I understood correctly, was
completely re-doing your problem from scratch using something like the
following query instead of VBA code:

'Give a query with columns a, b, and c, which you want to be a single
column:

SELECT a AS Combined FROM MyQuery
UNION ALL
SELECT b AS Combined FROM MyQuery
UNION ALL
SELECT c AS Combined FROM MyQuery

That might work if I knew how many rows there would be in advance, and if no
processing were required on the return.

(Note that at least in Access, the second & third "AS Combined" are
redundant, but I like to put them in there both for clarity and in case I
change the order of the Union clauses.)

Yes, I am quite aware of how Union queries work.

Thanks;

Amy

 

[Amy Blankenship]

Amy,

What you have shown appears to be correct. I'll ramble a bit so that you
might see how to frame a better question.

You've got an e-learning app (I was able to verify this by reading a page
that pointed to your blog) and wish to run a summary query to populate a
file.

The syntax for your propArray is correct. So you need to get the
information from the summary query into an array so that it can be written
to the file. Then you need to be able to write your fileContents string
to that file.

Here's one way to do all that (air code done entirely in Notepad):

I've already parsed the information into my custom class. I am really
*just* looking for the proper syntax to get it back out. To boil it down to
the simplest fragment, my object has a property called qCount. I need to be
able to take the string "qCount" and combine it with my object somehow to
come back with the proper data. So if I can type something like

? reportInfo("qCount")

into the Immediate window and have it spit back at me the value of
reportInfo.qCount, then I will have this problem licked. I know classes
aren't widely used in this group because there is so durn little
information, so I can figure something else out if no one knows.

Thanks;

Amy

 

[James A. Fortune]

I've already parsed the information into my custom class. I am really
*just* looking for the proper syntax to get it back out. To boil it down to
the simplest fragment, my object has a property called qCount. I need to be
able to take the string "qCount" and combine it with my object somehow to
come back with the proper data. So if I can type something like

? reportInfo("qCount")

into the Immediate window and have it spit back at me the value of
reportInfo.qCount, then I will have this problem licked. I know classes
aren't widely used in this group because there is so durn little
information, so I can figure something else out if no one knows.

Thanks;

Amy

I have an example (somewhat contrived) of class usage here:

http://groups.google.com/group/comp.databases.ms-access/msg/61e18041cac920f8

If reportInfo is the name of your class, maybe use something like:

iqAttempted = 1
Select Case propArray(i)
Case "qAttempted": MyOtherArrayName(i) = reportInfo.qAttempted
....
End Select

?MyOtherArrayName(iqAttempted)

James A. Fortune
(e-mail address removed)

 

[Amy Blankenship]

I have an example (somewhat contrived) of class usage here:

http://groups.google.com/group/comp.databases.ms-access/msg/61e18041cac920f8

If reportInfo is the name of your class, maybe use something like:

iqAttempted = 1
Select Case propArray(i)
Case "qAttempted": MyOtherArrayName(i) = reportInfo.qAttempted
...
End Select

?MyOtherArrayName(iqAttempted)

I think I'd rather switch to a Dictionary object ;-). Right now, I'm trying
to write a getProperty method on the class, but since all the properties
seem subordinate to Me rather than just being variables in the module, I've
so far gotten the same issues.

Thanks;

Amy

 

[James A. Fortune]

I've already parsed the information into my custom class. I am really
*just* looking for the proper syntax to get it back out. To boil it down to
the simplest fragment, my object has a property called qCount. I need to be
able to take the string "qCount" and combine it with my object somehow to
come back with the proper data. So if I can type something like

? reportInfo("qCount")

into the Immediate window and have it spit back at me the value of
reportInfo.qCount, then I will have this problem licked. I know classes
aren't widely used in this group because there is so durn little
information, so I can figure something else out if no one knows.

Thanks;

Amy

Maybe this is what you're after:

http://groups.google.com/group/comp.databases.ms-access/msg/1f78b7e7cd85512b

James A. Fortune
(e-mail address removed)

 

[Amy Blankenship]

Maybe this is what you're after:

http://groups.google.com/group/comp.databases.ms-access/msg/1f78b7e7cd85512b

Yes, that works. I've spent enough time in AS3 that the lack of data typing
bugs me, though. Maybe I can combine it somehow...

 

[Dirk Goldgar]

I apologize in advance for the cross posting, but I'm trying to widen the
set of eyes that is able to see this from
microsoft.public.access.formscoding, since I haven't gotten an answer there
yet.

I have a requirement to essentially take the things in a column returned
by
a query and reorder them as rows. The rows aren't necessarily in the same
order as the columns, and some require processing. Once all the rows are
assembled, I need to write them all out to file.

To accomplish this, I created a class with properties corresponding to
each
row that I need. I run the query and populate the object. I've also
created an array that contains the names of the properties I want to add
to
the file, in the order I want them, but I am at a loss as to the syntax to
use to use a string instead of dot syntax to get at these properties.

What I have now is

'set up array of properties to write to file
Dim propArray As Variant, prop As String

propArray = Array("qCount", "qAttempted", "qViewed", "qAnswers",
"checkedButtons", _
"numCrossedOut", "correctCrossedOut",
"incorrectCrossedOut",
"markedAs", "crossList", _
"aX", "bx", "cX", "dX", "eX", "isCorrect", "visit1Time",
"otherVisitTime")

For i = 0 To UBound(propArray)
Debug.Print propArray(i)
If i > 0 Then fileContents = fileContents & vbCr
fileContents = fileContents & propArray(i) & ": "
fileContents = fileContents & propArray(i) &
reportInfo(propArray(i))
Next

but this isn't working.

I don't think I'd do it this way -- I think I'd use a crosstab query
instead, or else a union/sum of a set of contrived queries -- but to answer
your question: you can do this by either (A) adding a method to your class
that receives a string argument, passes it through some case logic and , and
returns the desired property value, or (B) adding a public Properties
collection to your class, with code to load that collection with the
appropriate indexes and values.

I could be wrong, but I don't believe you can interrogate the properties of
the class by name unless you use one of those methods, or something very
similar.

 

[James A. Fortune]

Yes, that works. I've spent enough time in AS3 that the lack of data typing
bugs me, though. Maybe I can combine it somehow...

I'm glad that works. I saw from your blog that using a class module is
Hobson's choice if Flex is involved.

James A. Fortune
(e-mail address removed)

Hobson's choice - Eng. university slang, supposedly from Thomas Hobson
(c.1544-1631), Cambridge stable manager who let horses and gave
customers a choice of the horse next in line or none at all. Phrase
popularized by Milton, c.1660. -- http://www.etymonline.com

 

[Robert Morley]

Okay, now I understand what you're trying to do. I see you've gotten lots of replies to this, and they've obviously got you on the
right path with creating a Properties collection. If you want to use "reportInfo(SomeString)", as you've been trying to, instead of
"reportInfo.Properties(SomeString)", then you'll want to use default properties. Some programmers discourage their use, but that's
another discussion entirely. There's a little trick you can use to get Access to use default properties...or you can just write
it directly in VB6, where it's a bit easier to do, but I'll assume you want to do this entirely in Access.

See the article here for details: http://www.thescripts.com/forum/thread694992.html

If you want to get fancy, you can also make it work with "For Each" so you can iterate your properties that way, though the normal
method of doing that (and the only way I've played with) involves using a Collection object, which can be a little sluggish
sometimes. I know I've seen really good articles on this method in the past, but I'm damned if I can find them now. Here's a forum
thread that covers the topic fairly well, though: http://www.xtremevbtalk.com/archive/index.php/t-146131.html . Feel free to ask
questions on this, I've done it myself any number of times in the past.

And to answer the question you asked somewhere, I don't believe you can get a type-specific variation of this going unless every
last field uses the same type. If you think about the declaration of the Properties property (or function), it should become fairly
obvious why not: how do you assign a string and a long, for example, to the same function/property without using a Variant? I
believe you might be able to do something like this with overloading in .NET, I'm not 100% sure, but that's definitely not something
available in Access.


Rob

Well, other than one minor problem with your code (you've unnecessarily included propArray(i) twice), I don't see any reason why
it wouldn't work. Just to be safe, I'd specify the "Fields" property in what I'm assuming is your recordset (reportInfo). With
those two things put together, your last line in the loop would look like this:

fileContents = fileContents & reportInfo.Fields(propArray(i))

Except that now I want to make sure the properties are being printed out in the correct order, so I want the name of the property
to appear on each row until I have something that works. Plus, reportInfo does not have a Fields property. Do you know what I
mean when I say I wrote a class to handle this? Essentially it is an associative array with methods. I'd rather not revamp my
code to use a Dictionary, because then I'd lose the extra methods. Is it possible to write a Class in VB that extends Dictionary?
Information on Classes is very hard to find for VBA.

That might fix the problem, but I don't think so. If you could post a sample of the output you're getting, along with the output
you actually want, that would be very helpful.

I'm not getting anything.
reportInfo(propArray(i)) 'Runtime error 438 (object does not support this property or method)
reportInfo.propArray(i) 'Compile error (Expected identifier or bracketed expressopm
reportInfo[propArray(i)] 'Runtime error 2464 (Microsoft Access cannot find the field '|' referred to in your expression

That is why I asked for the proper syntax...because the syntaxes I have tried didn't work.

Oh, and akphidelt's post wasn't as off-topic as it might appear at first glance. What he was proposing, assuming I understood
correctly, was completely re-doing your problem from scratch using something like the following query instead of VBA code:

'Give a query with columns a, b, and c, which you want to be a single column:

SELECT a AS Combined FROM MyQuery
UNION ALL
SELECT b AS Combined FROM MyQuery
UNION ALL
SELECT c AS Combined FROM MyQuery

That might work if I knew how many rows there would be in advance, and if no processing were required on the return.

(Note that at least in Access, the second & third "AS Combined" are redundant, but I like to put them in there both for clarity
and in case I change the order of the Union clauses.)

Yes, I am quite aware of how Union queries work.

Thanks;

Amy

 

[Amy Blankenship]

Okay, now I understand what you're trying to do. I see you've gotten lots
of replies to this, and they've obviously got you on the right path with
creating a Properties collection. If you want to use
"reportInfo(SomeString)", as you've been trying to, instead of
"reportInfo.Properties(SomeString)", then you'll want to use default
properties. Some programmers discourage their use, but that's another
discussion entirely. There's a little trick you can use to get Access
to use default properties...or you can just write it directly in VB6,
where it's a bit easier to do, but I'll assume you want to do this
entirely in Access.

See the article here for details:
http://www.thescripts.com/forum/thread694992.html

I'm thinking you can only have one default property (which would be why it
would be the default. I suspect that won't help in my situation.

If you want to get fancy, you can also make it work with "For Each" so you
can iterate your properties that way, though the normal method of doing
that (and the only way I've played with) involves using a Collection
object, which can be a little sluggish sometimes. I know I've seen really
good articles on this method in the past, but I'm damned if I can find
them now. Here's a forum thread that covers the topic fairly well,
though: http://www.xtremevbtalk.com/archive/index.php/t-146131.html .
Feel free to ask questions on this, I've done it myself any number of
times in the past.

I don't want to get all the properties, and I don't want them in the order
that VB would just give me. I need them to come back in a specified order.

And to answer the question you asked somewhere, I don't believe you can
get a type-specific variation of this going unless every last field uses
the same type. If you think about the declaration of the Properties
property (or function), it should become fairly obvious why not: how do
you assign a string and a long, for example, to the same function/property
without using a Variant? I believe you might be able to do something like
this with overloading in .NET, I'm not 100% sure, but that's definitely
not something available in Access.

See, that's why I used _different_ properties ;-).

Oh, well, we work within the limitations of whatever language is in front of
us...

Thanks;

Amy

 

[Robert Morley]

I'm thinking you can only have one default property (which would be why it would be the default. I suspect that won't help in my

situation.

What you would make the "default property" is the Properties property. So your property declaration would look something like:

Public Property Get Properties(ByVal strPropName As String) As Variant
Attribute Properties.VB_UserMemId = 0 'hidden
Properties = <wherever you need to retrieve the property from>
End Property

Then you could call it EITHER by:
SomeValue = reportInfo.Properties(SomeString)
or
SomeValue = reportInfo(SomeString)

In other words, reportInfo.qCount would return qCount in a type-specific form, and reportInfo("qCount") or
reportInfo.Properties("qCount") would return it as a Variant, allowing you to access it using a string or variable instead of the
specific property.

As for what you actually assign to Properties above, it will depend how you're storing the property information within your class.
If they're all in separate variables, then it might look something like this:

Select Case strPropName
Case "qCount"
Properties = lngqCount 'assuming this was a long variable stored in lngqCount
Case "qAttempted"
Properties = strqAttempted
Case "qViewed"
Properties = blnqViewed
...
End Select

If you wanted, you could even use a syntax more like this for clarity, though it would probably be slightly slower:

Select Case strPropName
Case "qCount"
Properties = Me.qCount
Case "qAttempted"
Properties = Me.qAttempted
Case "qViewed"
Properties = Me.qViewed
...
End Select

I don't want to get all the properties, and I don't want them in the order that VB would just give me. I need them to come back
in a specified order.

Okay, in that case, implementing For Each is definitely NOT a good idea.

See, that's why I used _different_ properties ;-).

And the nice thing about implementing them as different properties, as well as in a Properties collection is that you can access
them individually and have them be type-specific, or if you need the flexibility of accessing by name using a string variable, you
can do that too as long as you don't mind that they're Variants.



Rob

 

[Dirk Goldgar]

I'm thinking you can only have one default property (which would be why it
would be the default. I suspect that won't help in my situation.

Your default property could be your Properties collection property, couldn't
it?

 

[James A. Fortune]

Yes, that works. I've spent enough time in AS3 that the lack of data typing
bugs me, though. Maybe I can combine it somehow...

I haven't tried it, but why not attempt using a user-defined Type that
is defined in a "real" module for the property, something like:

'--in a module-----
Public Type MyData
qCount As Integer
...
'It's O.K. to have one's you're not using yet defined
End Type
'------------------

'--in your class---
Public Property Get theData() As MyData
....
mtheData.qCount = intqCount 'or whatever is used to store the results of
your calculation
....
End Property
'------------------

'--behind your form--
Dim DataHolder As MyData
....
DataHolder = reportInfo.MyData

'& DataHolder.Item(propArray(i)) [instead of reportInfo(propArray(i))]
'--------------------

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

'& DataHolder.Item(propArray(i)) [instead of reportInfo(propArray(i))]

That won't work. The user-defined type can't use Item to refer to an
element. Maybe Dick's idea will work using

DataHolder.Properties(propArray(i))

and setting a properties collection in the class module.

James A. Fortune
(e-mail address removed)

 

[Amy Blankenship]

What you would make the "default property" is the Properties property. So
your property declaration would look something like:

Public Property Get Properties(ByVal strPropName As String) As Variant
Attribute Properties.VB_UserMemId = 0 'hidden
Properties = <wherever you need to retrieve the property from>
End Property

Then you could call it EITHER by:
SomeValue = reportInfo.Properties(SomeString)
or
SomeValue = reportInfo(SomeString)

In other words, reportInfo.qCount would return qCount in a type-specific
form, and reportInfo("qCount") or reportInfo.Properties("qCount") would
return it as a Variant, allowing you to access it using a string or
variable instead of the specific property.

But the issue there, is how does it get from the individual variables into
the properties list? If there's no dynamic way to grab the name of the
property and its value and pop it into that properties collection or
dictionary or whatever, then we're right back where we started with
hand-coding the name of the property we need.

If I had this to do over, I'd just create a bunch of constants that
corresponded to array indexes, then use those instead of
myClassObj.variableName. I'd lose the typing by doing that, but I'd be
living within how VBA likes to do things.

-Amy