Access VBA reserved word "name"

Question

I am using Access VBA to access movie information using the API. It is working fine but I have a problem with attributes that have as name the word "name" which is a reserved word.

Let's say I want to access the production companies. I am using this code, and it is working fine if I want to get the id, but it doesn't work if I am trying to get the names because the word "name" is a reserved word in line 4.

Does anyone have an idea how to fix it?

The working code:

Set Keys1 = VBA.CallByName(jsonDecode1, "production_companies", VbGet)
movie_companies = ""
For Each Key In Keys1
    movie_companies = movie_companies & **key.id** & ","
Next

The not working code:

Set Keys1 = VBA.CallByName(jsonDecode1, "production_companies", VbGet)
movie_companies = ""
For Each Key In Keys1
    movie_companies = movie_companies & **Key.Name** & ","
Next

The json file:

production_companies: [ { name: "France 2 Cinéma", id: 83 }, { name: "SBS Productions", id: 8997 }, { name: "Les Films Français", id: 16782 } ],

Thank you for your reply, but i need to give you more details of what i am doing. yes i am using a collection but i didn't create it i am just importing data do the collection from a website using API, also i want to let you know that all the other properties works fine except the "name" may be because the editor capitalize the first letter of the word "Name" automatically but it doesn't capitalize the other words, how can i avoid the editor to capitalize the word "name" maybe that's the issue. Thank you again

That's the whole code:

Set sc = CreateObject("ScriptControl"): sc.language = "JScript"  
Set oXMLHTTP1 = CreateObject("MSXML2.XMLHTTP")
oXMLHTTP1.Open "GET",  URL, False   'Open socket to get the website
oXMLHTTP1.setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
oXMLHTTP1.send 'send request
Do While oXMLHTTP1.ReadyState <> 4
    DoEvents
Loop
oResp = oXMLHTTP1.responseText 'Returns the results as a byte array
Set jsonDecode1 = sc.Eval("(" + oResp + ")")
Set Keys1 = VBA.CallByName(jsonDecode1, "production_countries", VbGet)
For Each Key In Keys1
       movie_companies = movie_companies & Key.id & ","
       movie_companies = movie_companies & Key.Name & ","
Next

Now i am sure that the problem is the fact that the first letter is uppercase, because i have another property named "key" and because the editor change it to "Key" with uppercase K, it is not working too :) is there a way to disabled changing the first letter to uppercase in the code editor? this will definitely fix the problem

Answer 1

I fixed it :), it is weird but i fixed it. what you need to do is declare a variable "name" with the lower case "n" and after that i remove it and now the code editor don't capitalize the word "name" anymore. If i declare a variable with the first letter capital it will save it and even if i remove the declaration the next time you use it it will automatically capitalize it and you cannot remove until you declare it again with all lower :)

Thank you all

Answer 2

From what I can see, this has ZERO to do with reserved words.

The use of name is a valid property of a GAZILLION built in objects such as forms, reports, column names pulled from a record set etc.

The “conflict” of reserved words is ONLY in regards to SQL or using VBA variables you define (and name is in fact NOT a reserved word anyway).

You are using “someobject.Name” and thus this has ZERO to do with reserved words since a property or method of an object called “name” NEVER was a source of errors or issues in Access VBA. In fact built in methods can use reserved words.

Ignoring that you left out the data type of this function? (cannot stress how you want to code with option explicit and ALSO cannot stress how you should give a data type to that function).

However I shall make a BIG guess and assume it is a VBA collection object.

Collections in VBA don’t have a name property. So this works:

Dim c        As New Collection

c.Add "Apple", "testkey1"
c.Add "Grape", "testkey2"

Dim f        As Variant

For Each f In c
  Debug.Print f
Next

However f.name will fail since “.name” is NOT a property or method of a collection. So this issue has nothing to do with reserved words but the simple fact that “.name” never been part of or a valid syntax for a collection object.

And if you really do need each element of the collection to have values, then your function can return a collection of “structures” but that structure will have to be a class object. So this code shows this in Action:

So the class module can be:

Class module clsStruct:

Option Compare Database
Option Explicit

Public Name As String
Public KeyName  As String
Public Value As String

Now in our code:

Dim c        As New Collection

Dim MyStruct  As New clsStruct

MyStruct.KeyName = "hello"
MyStruct.Name = "myname"
MyStruct.Value = "my value"

c.Add MyStruct

Dim f As Variant

For Each f In c
  Debug.Print f.Name, f.Value, f.KeyName
Next

Output:
myname        my value      hello

So you are MOST free to use “.name” as a valid property of a object you create, but the collection object by default has no such name property and thus will fail at runtime.