construct dynamic field references in VB? (1 Viewer)

[dwhitman]

The heart of my db is a large table ([voters]) that I receive periodically from an outside source. Within the table is a series of fields that represent the voting history of each voter. Over time, new fields are added to the table representing each election as it happens, and older fields are dropped out to keep the number of fields more or less constant.

If a voter came out to the polls to vote in a given election, the corresponding field will have some variable text in it, otherwise it's null.

I have a utility function I use to add up how many times a voter actually came out to vote lately. I pass the fields of interest as a parameter array since the number of them we need to look at varies depending on where we are in the yearly election cycle. This function works just fine:

Function votes(ParamArray elections() As Variant) As Integer
    Dim i As Integer
    votes = 0
    For i = 0 To UBound(elections())
        If Not (IsNull(elections(i))) Then
            votes = votes + 1
        End If
    Next
End Function

The trouble is, as the voter table gets updated with new elections, I need to periodically edit the list of fields passed to refer to the most recent elections, and how many of them to look at.

The names of the fields follow a predictable pattern, and I can easily construct strings with the names of the fields needed by looking at the current date. But I can't figure out how to use these strings to refer to fields in my table.

Can anyone help me out?

 

[DJkarl]

Using DAO you could cycle through the fields of a tabledef object in the current database. Something like this perhaps. This would return a comma seperated list of field names for the table.

Function FieldList(byval tblNAME as string)as string
Dim fld As Field
Dim x As Long
Dim tbl As TableDef
Dim cList As String
cList = ""
        Set db = currentdb
        Set tbl = db.TableDefs(tblname)
            For Each fld In tbl.Fields
                cList = clist & fld.Name & ","
            Next
        FieldList=cList
        Set tbl = Nothing
        Set db = Nothing
end function

 

[dwhitman]

DJKarl:

Thanks, but I don't this goes to what my problem is. The field names are predictable, and I can construct them as strings pretty easily. Where I'm clueless is once I have these strings, I can't figure out a way to actually refer to the fields within VB using the string.

Your code did give me some ideas; here's a stub using hard coded strings that I hoped would work, but I get a run time error (type mismatch on the OpenRecordSet line):

Function Strength() As Integer

Dim rs As Recordset
Dim tbl As TableDef
Dim db As Database

Set db = CurrentDb
Set tbl = db.TableDefs("voters")
Set rs = db.OpenRecordset(tbl)

Strength = 0

If Not IsNull(rs.Fields("2008p")) Then
   Strength = Strength + 1
End If

Set rs = Nothing
Set db = Nothing

End Function

Not sure if that code fragment is totally crazy. I'm obviously pretty new at this.

 

[DJkarl]

DJKarl:

Thanks, but I don't this goes to what my problem is. The field names are predictable, and I can construct them as strings pretty easily. Where I'm clueless is once I have these strings, I can't figure out a way to actually refer to the fields within VB using the string.

Your code did give me some ideas; here's a stub using hard coded strings that I hoped would work, but I get a run time error (type mismatch on the OpenRecordSet line):

Function Strength() As Integer
 
Dim rs As Recordset
Dim tbl As TableDef
Dim db As Database
 
Set db = CurrentDb
Set tbl = db.TableDefs("voters")
'Don't need TableDef if you are using a recordset
Set rs = db.OpenRecordset([COLOR=red]"voters"[/COLOR])
 
Strength = 0
 
If Not IsNull(rs.Fields("2008p")) Then
'This should work or just rs("Fieldname")
   Strength = Strength + 1
End If
 
Set rs = Nothing
Set db = Nothing
 
End Function

Not sure if that code fragment is totally crazy. I'm obviously pretty new at this.

I made some comments to the code above.

 

[dwhitman]

DJkarl:

Thanks for sticking with me. I made the changes you suggest, but I'm still got a type mismatch error on the OpenRecordSet line. Googled around and found a suggestion to disambiguate the recordset (DAO vs. ADO), and managed to make that go away.

Above and beyond that, I'm a little confused as to whether this code would actually do what I want. If I open the table as a recordset within the function, would I be retrieving values from the same record as the query that calls this function?

Function Strength() As Integer

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("voters")

Strength = 0

If Not IsNull(rs("2008p")) Then
   Strength = Strength + 1
End If

Set rs = Nothing
Set db = Nothing

End Function

And in fact, if I run this, it always seems to return 1, even if there's a null in [2008p] on a given record.

 

[DJkarl]

Thanks for sticking with me. I made the changes you suggest, but I'm still getting a type mismatch error on the OpenRecordSet line.

Try declaring the recordset as DAO.Recordset

Dim rs As DAO.Recordset

Above and beyond that, I'm a little confused as to whether this code would actually do what I want. If I open the table as a recordset within the function, would I be retrieving values from the same record as the query that calls this function?

This is the first time you've mentioned this function is being called by a query. The first post mentions you needed to know how to refer to a field name through code.

If a query is being used then you would need to pass something to identify the record (voterID, primaryKey, etc...) then look up that record in a recordset (via findfirst or seek method), count what you need to count, then pass back a result. This would not be the best method as the recordset object would be created and destroyed for every record you query.

Function Strength() As Integer
 
Dim rs As Recordset
Dim db As Database
 
Set db = CurrentDb
Set rs = db.OpenRecordset("voters")
 
Strength = 0
 
If Not IsNull(rs("2008p")) Then
   Strength = Strength + 1
End If
 
Set rs = Nothing
Set db = Nothing
 
End Function

 

[dwhitman]

Thanks again!

I found the trick of declaring as a DAO.recordset elsewhere just before your reply hit, but you cut to the heart of the problem in the later part of your post.

As written, I obviously need to tell the function which record to look at, and I agree with your assessment - sounds very inefficient to open the recordset, seek to the current record, pull values, then close the recordset for each record in the calling query.

I started in thinking this would be quite easy, but I just didn't know the syntax to make the references back to fields in the calling query - sort of like the "Me" object you can use on forms. Maybe vb just doesn't easily do what I was hoping.

 

[LPurvis]

What is the requirement of the related record - and what the function should return?

 

[dwhitman]

There's a calculated field in a query that wants an integer count of the number of times a voter has actually come out to the polls, based on whether some other fields in that same record have some text in them, or are null.

I have a working function that returns this count when I pass the fields to be checked as parameters. The trouble is that twice a year, the fields to be checked change - so I need to edit the parameters passed.

The names of the new fields to be examined are predictable, so I was hoping I could build some intelligence into the function to let it figure out what fields to look at.

With DJKarl's help, I constructed a crude stub that sort of does this, but to get data from the proper record, there'd be a huge amount of overhead; basically running a new query for each record in the calling query.

The piece I hadn't thought through was that when I pass a field as a parameter, Access is passing a pointer not just to the field, but to the field in a particular record. And I'm loosing that extra information when I try to construct the field name on the fly in the called function.

 

[DJkarl]

There's a calculated field in a query that wants an integer count of the number of times a voter has actually come out to the polls, based on whether some other fields in that same record have some text in them, or are null.

I have a working function that returns this count when I pass the fields to be checked as parameters. The trouble is that twice a year, the fields to be checked change - so I need to edit the parameters passed.

The names of the new fields to be examined are predictable, so I was hoping I could build some intelligence into the function to let it figure out what fields to look at.

With DJKarl's help, I constructed a crude stub that sort of does this, but to get data from the proper record, there'd be a huge amount of overhead; basically running a new query for each record in the calling query.

The piece I hadn't thought through was that when I pass a field as a parameter, Access is passing a pointer not just to the field, but to the field in a particular record. And I'm loosing that extra information when I try to construct the field name on the fly in the called function.

If you already have a calculated field in a query, why not just use VBA to write the new SQL for the query?

Assuming only the field names are changing and you have the ability to predict or read the field names, you could just write a new SQL statement and replace the SQL from your existing query.

 

[dwhitman]

If you already have a calculated field in a query, why not just use VBA to write the new SQL for the query?

Assuming only the field names are changing and you have the ability to predict or read the field names, you could just write a new SQL statement and replace the SQL from your existing query.

Ah, that's a GREAT suggestion. Let me noodle around with that for a while.

It also occurs to me that the main place I call this function is in an update query that populates a new field based on the calculation. And while there are some other places I'd use an "intelligent" version of the function, for this primary use I could just eliminate that query altogether,and instead walk through the table record by record and stuff the proper results into the field all from within VB.

Thank you for your thoughtful responses and patience with this newbie.