how to concatenate a number to a variable name?

[Pierre]

Let's put it simply:
I have two sets of variables:

A1, A2, A3, A4 and B1, B2, B3 and B4.

Basically, what I'm trying to do is something that *could* look like this:

For x = 1 to 4
Ax = Bx
next x

So the content of A1 will be copied to B1, A2 to B2, etc with one single
loop instead of going through all the variables separately (I actually have
about 50 of those...)
In short, I'm looking for a way to append or concatenate a variable name
with a number.

I've been searching lots of forums and didn't find an answer. Is this
possible? I kind of remenber it was feasable in Basic on my old ColorComputer
III...

Pierre.

 

[David H]

I do not believe that it can be done. I had a similar question about dynamic
code quite awhile back and the consensus was no.

I would question though what exactly you're trying to accomplish. If the
snippet is actually logic, you could use an array.

 

[Jack Leach]

agreed... this does not seem to be possible.

Similar to this would be using the Eval() function, but alas, it does not
work on variables.

Eval("function" & variable & "()") would return the value of
functionA()
if the variable = "A"

but, no way to do this with a variable itself.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Pierre]

Thanks to all of you.

The goal of this is that I have a record with mutiple fields (A1, A2, A3,
etc). They're configuration parameters for automation controls. When this
data needs to be updated I wanted to create a form where the "current" data
is displayed in lock fields (cannot be changed - to prevent oops from
happening), and with fields where the "new" data is entered (B1, B2, B3),
thus allowing to view the "new" data before "writing" it in the record. Once
the user confirms all the fields correct, the "new" values would be written
over the "current" values. (A1 = B1, etc).
By doing so, I don't end-up with a huge db with all the previous values as
they are of no real interest.

Obviously, I will have to rethink the way I wanted this to work.

 

[Douglas J. Steele]

No offense, but having fields named A1, A2, A3 etc. is generally indicative
of a poor table design. It looks as though you've got a repeating group,
which should always be avoided.

That being said, it sounds as though you're trying to deal with field names,
not variable names. If that's the case, you can use code like:

Dim lngLoop As Long

For lngLoop = 1 to 5
rs1.Fields("A" & lngLoop) = rs2.Fields("B" & lngLoop)
Next lngLoop

 

[David H]

That's entirely possibly by one of two means.

1) The most commonly used method is to place code into the Form's
BeforeUpdate event which asks the user to confirm the change, as well
introducing validation. If the user says NO or CANCEL or if the validation
fails, CANCEL = TRUE aborts the update.

2) An alternative, although less commonly implemented, would be to use bound
controls that are locked or disabled to display the actual values and then
unbound controls to capture the new values. Once the user enters the new
values into the unbound controls are taken and then placed into an UPDATE SQL
statement which saves the values. (Or the code copies the values from the
unbound controls to the bound controls and executes Me.Dirty = False which
saves the values.)

 

[David H]

I might add that using an explicit SQL Statement to UPDATE the values has the
added benefit in that you can copy the values to a history table thus
capturing the Date/Time the settings were changed and other information like
who changed them and why.

 

[Pierre]

No offence taken, Douglas. Actually the name of the database fields are...
BA0, BA1, BA2... Like I said, those are configuration parameters for
automation controls and the engineers have named those parameters this way...
There's also BWTC, BWTD, BWD0, KCM, LMBDA... ;-) If you can figure out why
they came out with those easy-to-remember names, please tell me...

I was planning to use something like "A1" in the form to make it easier to
copy from one set of fields to the other.
It's funny that I could have done this trick with the Basic from a
ColorComputer 3 with something as simple as:

for x = 1 to 4
A$(x) = B$(x)
next x

Regards,
Pierre.

 

[Pierre]

Hi David.
Unfortunately, I'm not hot enough in SQL and quite rusty on Basic to do
something like that. I think it could be "easier" to switch it to Excell
where I can use cells to do the trick. I wanted to do it in Access because I
already have a large Product Receipe DB and I wanted to link everything
together.

Regards,
Pierre.

 

[David H]

In a technical or scientific situation, field names like that are appropriate
since they represent a real-world aspect of an actual object.

 

[David H]

It is much, much easier than you think. Both techniques are one the simpler
side of things when it comes to Access development.

 

[Douglas J. Steele]

I'm not sure I agree.

Of course, it depends on what's being modeled, but it might be better to
have a related table with ParameterName and ParameterValue, rather than
having multiple fields in the original table.

 

[David H]

That I can understand and might give it consideration if I were designing the
DB myself. I would hesitate to go with a child table though for each
parameter unless I had something in place to capture changes to it for any
particular master record. Using the child obviously allows the database to be
used for multiple products with a variety of configuration possibilities, but
a single parameter can make all the difference in the world.

Not that I'm going to bring up the time where Tech Support was setting up a
new profile for a Disney owned & operated hotel still under construction and
inadvertently caused the night audit to run against the files, which charged
guest's credit cards, generated multiple invoices and a wide variety of
assorted GL transactions across multiple nights until I just happened to run
across the problem.

 

[Pierre]

Thanks David.
By discussing to someone who has a lot more experience than I do with
Access, I came to the conclusion that going with something similar to your
suggestion "1" below would be the best way.
So I think this is solved. Thanks all for your help!

Pierre.