refer to variable via string

[ktrasler]

Hi all

Is it possible to refer to a variable from a string.

I can refer to a field by a string but can't get a string to be used aa a variable.

Heres what I have

rs.Fields(vField.name) = "a" & Replace(vField.name, " ","")

I know the first part works (before =) as I have tried just putting any value in the table and it works fine.

obviously the second part doesn't work but hope its clear what I'm after.

my variable name is 'aName' for example and my field would be 'Name'.

The reason I need this is so that I can look through all the field names and pull the correct data from the same named variable (with 'a' prefix).

Thanks

 

[vbaInet]

Look into CallByName() or Eval()

 

[ktrasler]

thanks for the reply.

I have had a look at both but I dont understand either.

Would you be able to provide an example please....?

 

[sonof27]

does something like this work?

strName = "a" & Replace(vField.name, " ","")
rs.Fields(vField.name) = strName

 

[vbaInet]

Did you check out the Help file? Something like:

rs.Fields(vField.name) = "a" & Replace(Eval(vField.name), " ","")

 

[gemma-the-husky]

what is vfield.name?

what is that supposed to return?

 

[vbaInet]

Oops... wrong place for Eval()

rs.Fields(vField.name) = Eval("a" & Replace(vField.name, " ",""))

 

[ktrasler]

sonof27

That doesn't work. That would be fine if I wanted to add the 'string' to the table. e.g put 'aColleagueNo' in the table.

I want to put the value of the variable in the table e.g aColleagueNo = 12345



vbaInet

I have looked at the help files for both but can't get it to work.

rs.Fields(vField.name) = "a" & Replace(Eval(vField.name), " ","")

This returns the error "can't find the name 'Colleague' you entered in the expression.

rs.Fields(vField.name) = Eval("a" & Replace(vField.name, " ",""))

This returns the error "can't find the name 'aColleagueNo' you entered in the expression.

In this case my variable is

aColleagueNo = 12345. The table field is Colleague No. I set it up like this as I thought it would be fairly straight forward and easy to update when adding new fields etc etc.

I was wrong!!!

 

[sonof27]

if vField has a name property,vField.name, that returns the name of the field does it also have a value property, something like vField.value, that returns the value of vField.

 

[vbaInet]

Is this variable within scope? That is, is it set to PUBLIC?

If it's not, is it in the same module as where the code is being run?

 

[ktrasler]

hi Gemma

sorry, didn't see your reply at first.

vField.name is the field name. e.g below

I am populating the table with information from text files.

For Each vField in rs.Fields
     rs.Fields(vField.name) = Eval("a" & Replace(vField.name, " ",""))
Next vField

Like I said, I thought this would be a nifty way to keep my db as dynamic as poss.

 

[ktrasler]

my last post explains what vField.name is... it just refers to the table field.



the variable is set in the same procedure so definately in scope.

 

[ktrasler]

ha... all these post coming so quick is confusing.


Gemma.... or even Dave. lol

 

[vbaInet]

If you don't have many variables then you would have to create a function for every variable, and this function will then fetch the value of the variable. So if you prefix the function name with another "a" you should be fine. Using your problem variable as an example, i.e. aColleagueNo:

The function would be:

Public Function aaColleagueNo()
    aaColleagueNo = aColleagueNo
End Function

Notice the prefixed "a". The code line will now be:

rs.Fields(vField.name) = Eval("aa" & Replace(vField.name, " ","") & "()")

 

[ktrasler]

Bingo...

That works..

Thank you for helping with this. That will come in very useful..

 

[vbaInet]

No problemo!

There might be a more succint way of doing this but I haven't yet given it much thought.