Refer to fields based on a calculated number

[A.Singh]

I have 50 fields (columns) with same properties. Each is named Slot1, Slot2, ...
I need to SetValue in various fields. A calculated number determines which of the 50 fields needs to be filled in (with a value determined by an expression).



Thanks in advance!

[burrina]

You need to explain your issue better for us to help. What is the calculated number? What is the expression?

[A.Singh]

As I said, there are 50 fields named Slot1, Slot2, ...

There is a variable [TempVars]![I]. This contains the calculated Slot number. If it contains 3, then I need to update Slot3 using SetValue.

I tried concatenating: "Slot" & [TempVars]![I] as the 'Item' in SetValue action, but it did not work. Also: "Slot" & Str$([TempVars]![I]).

I hope this helps.

[June7]

This appears to be a non-normalized data structure.

I don't know if macro can handle this. VBA would be:

Me.Controls("Slot" & variable) = somevalue

I've never used TempVars but I think VBA can refer to them.

[ssanfu]

As I said, there are 50 fields named Slot1, Slot2

This implies you have 1 record with 50 fields in a table. BUT you don't state whether these fields are bound to controls on a form.

If you want to update the fields directly (in the table), you will need to use VBA.

If the fields are bound to controls, I hope you took the time to rename the controls (ie txtSlot1, txtSlot2, ...). Less confusion on whether you are referring to a field or control.

[A.Singh]

sorry for the confusion.

There is a form that has 50 fields per record, each bound to a column in a table. They are named "Slot1", "Slot2", ... in both objects. Will naming them as txtSlot1 etc enable me to concatenate the "Slot" and a variable content in a Macro?

I am not familiar with VBA and therefore am using the Macro builder!

[ssanfu]

It doesn't matter if they are named "Slot1" or "txtSlot1". It just makes it easier to know if you are trying to update a control or field.

Attached is a demo of the VBA using a variable to a bound control. This is just a demo..... I don't use tempvars so I just used a control to get the slot number.

[A.Singh]

Thank you, Steve and June 7.

Steve, the sample you sent does what i was looking for. However, since I do not know VB, i would like to seek guidance on:

1. how to create a routine that can be called from a macro. Any declarations needed? How/where is it to be saved?
2. will it be able to use the [TempVars]![I] variable that is to be concatenated? the TempVar has been set in the macro, and is calculated there. if so, what is the syntax for reference in VB? If not, could the value be passed to the VB routine as a parameter? (In which case it would have to be written as a function).
The VB routine needs to update Slot1 etc in a Form. what syntax to do that? In the macro, it is referred to as [Forms]![Calendar View/Change]![Slot1].

Thank you.

A.S.

[June7]

I don't use macros but I think they can only call functions, not subs. Place the function procedure in a general (public) module.

TempVars can be referenced in VBA. http://office.microsoft.com/en-us/ac...010120216.aspx

[A.Singh]

i converted my macros to VBA. it still does not concatenate.

I tried the following:

Forms![Calendar View/Change]!["Slot" & SlotN] = X

or

Me.Controls("Slot" & SlotN) = X

SlotN is an integer;

Pl advise what can be done. this is the only thing now holding up completion.

[June7]

And what is X? Is that a variable?

The syntax works for me.

If you want to provide db for analysis, follow instructions at bottom of my post.

[A.Singh]

Thanks. Yes, X is a variable.

I will try to tweak it to see if i can make it work. if not, will send db for analysis

[A.Singh]

Steve et al:

Thanks for your help. Sorry for the delay in responding -- i was not able to work on my project for a while. I did struggle a bit (since I am a novice), but was able to make it work!

Again, thanks

A. Singh