Access VBA - Dynamically assign properties to variable with of custom type

[bandwagon]

Hi Everyone,

A transient, virtual hobo, with only enough skill to be highly dangerous. You all know the type, as evidenced here:


The code I'm attaching, far below, does not work (shocker). It attempts to dynamically assign values to the properties? of my variable (of type ProjectRegions) via a loop that feeds it the property name and the property value. Thus,

• 1st time through:
  projectRegion.projectName = "Dismantle hampster wheel that is my job".
• 2nd time through:
  projectRegion.projectNumber = "42".
• 3rd time through:
  projectRegion.projectDescription = "A futile endeavor".
• nth time through:
  projectRegion.[regionName] = regionValue

I have two questions:
1. Is it possible to accomplish this dynamically, kind of like 'variable variables' in PHP?
2. I am currently using a Select Case, but it consumes hundreds of lines. Should I stick with that?

#1 would be funner and shorter. Thanks for any assistance.

Public Type ProjectRegions 
 projectName As Integer
 projectNumber As String
 projectDescription As String
End Type
Dim projectRegion As ProjectRegions
 
Sub getProjects()
  'Assume currentExcelRegion and currentExcelRegionValue change each time through the loop
  For count = 1 To 1000
         projectRegion = saveProjectRegions(currentExcelRegion, currentExcelRegionValue)
  Next
  MsgBox "My project number is: " & projectRegion.projectNumber
  MsgBox "My project description is: " & projectRegion.projectDescription
End Sub
 
Public Function saveProjectRegions(regionName, regionValue) As ProjectRegions
 projectRegion.[regionName] = regionValue 'This doesn't work, not that I expected it to
 saveProjectRegions = projectRegion
End Function

 

[spikepl]

You have provided details of something, but of what? In plain English - what do you want to do with which data?

 

[bandwagon]

You have provided details of something, but of what? In plain English - what do you want to do with which data?

In my very best plain English, I have created a custom type with several properties. I want to assign values to those properties via a loop.

 

[jdraw]

And when the loop is finished--what?
Do you want to save values for subsequent use?


For info on Type statement
http://msdn.microsoft.com/en-us/library/aa266315(v=vs.60).aspx

 

[bandwagon]

And when the loop is finished--what?
Do you want to save values for subsequent use?

Yes, I want to save them for subsequent use. I've edited the code above so it shows the global variable projectRegion.

I could then update the database with something like...

UPDATE tblProjects SET tblProjects.projectName = projectRegion.projectName WHERE ...

 

[jdraw]

Great. So you have some tables? How about telling us about the database, tables and relationships. Can you tell us about what this database is for? Will it support your business?

How would you describe what you're trying to do to someone in the line up at McDonalds who does not know you, or database or Access?

 

[DJkarl]

Hi Everyone,

A transient, virtual hobo, with only enough skill to be highly dangerous. You all know the type, as evidenced here:


The code I'm attaching, far below, does not work (shocker). It attempts to dynamically assign values to the properties? of my variable (of type ProjectRegions) via a loop that feeds it the property name and the property value. Thus,

• 1st time through:
  projectRegion.projectName = "Dismantle hampster wheel that is my job".
• 2nd time through:
  projectRegion.projectNumber = "42".
• 3rd time through:
  projectRegion.projectDescription = "A futile endeavor".
• nth time through:
  projectRegion.[regionName] = regionValue

I have two questions:
1. Is it possible to accomplish this dynamically, kind of like 'variable variables' in PHP?
2. I am currently using a Select Case, but it consumes hundreds of lines. Should I stick with that?

#1 would be funner and shorter. Thanks for any assistance.

Public Type ProjectRegions 
 projectName As Integer
 projectNumber As String
 projectDescription As String
End Type
Dim projectRegion As ProjectRegions
 
Sub getProjects()
  'Assume currentExcelRegion and currentExcelRegionValue change each time through the loop
  For count = 1 To 1000
         projectRegion = saveProjectRegions(currentExcelRegion, currentExcelRegionValue)
  Next
  MsgBox "My project number is: " & projectRegion.projectNumber
  MsgBox "My project description is: " & projectRegion.projectDescription
End Sub
 
Public Function saveProjectRegions(regionName, regionValue) As ProjectRegions
 [COLOR=Red]projectRegion.[regionName] = regionValue 'This doesn't work, not that I expected it to[/COLOR]
 saveProjectRegions = projectRegion
End Function

Well of course that doesn't work, your type defines 3 possible values ( projectName , projectNumber , projectDescription ) you tried to access [regionName] which is none of those. In short, you cannot dynamically access the type the way you are trying to do. You can still use a loop, but each definition ( projectName , projectNumber , projectDescription ) would need to be typed out and added in the loop.

 

[bandwagon]

Great. So you have some tables? How about telling us about the database, tables and relationships. Can you tell us about what this database is for? Will it support your business?

How would you describe what you're trying to do to someone in the line up at McDonalds who does not know you, or database or Access?

Getting into my project details is too steep a challenge for me at the moment. And way beyond the scope of my question, which I'm sorry I cannot ask in a clear way. I appreciate your help but I think I'm just going to go to McDonald's and get some fries.

 

[bandwagon]

Well of course that doesn't work, your type defines 3 possible values ( projectName , projectNumber , projectDescription ) you tried to access [regionName] which is none of those. In short, you cannot dynamically access the type the way you are trying to do. You can still use a loop, but each definition ( projectName , projectNumber , projectDescription ) would need to be typed out and added in the loop.

Thank you for your answer. Yes, that is what I'm doing now:

    Select Case regionName
        Case "projectName"
            projectRegion.ProjectName = regionValue
        Case "projectNumber"
            projectRegion.ProjectNumber = regionValue
        Case "projectDescription"
            projectRegion.ProjectDescription = regionValue
        '.....plus many more of these
    End Select

...There are probably 50 or so and probably more to come. But it goes on so long. It works the way I have it, but it would have been much easier with a single line that 'dynamically accesses the type.' Thanks again.

 

[jdraw]

Getting into my project details is too steep a challenge for me at the moment. And way beyond the scope of my question, which I'm sorry I cannot ask in a clear way. I appreciate your help but I think I'm just going to go to McDonald's and get some fries.

As the saying goes, pay me now or pay me double later. Planning is best done at the beginning of a project. However, it is your project, and it should be a learning experience.

Good Luck with the project.