If loop to choose which value to populate in textbox

Question

I have a textbox Job in Form2, and I have multiple textboxes in Form1.

Lets say in form1 I have Textbox1 which is RollFromInventory, Textbox2 is RollFromMachine1, Texbox3 is RollFromMachine2 and so on, assume there are 4 other machines, so four other Textboxes.

When I want to populate the textbox Job in Form2, I want to write an If loop which should look for the textbox which has value populated in it, in form1 (there will be only one textbox which will have a value among all the available textboxes in form1), i.e. either RollFromInventory will have a value or RollFromMachine1 will have a value or RollFromMachine2..

I am unsure of the looping logic, so I cant really figure out how to go about it.

Currently the code I have written is mainly for populating concatenated values (I am not providing that code, because it will make the objective seem complicated).

Answer 1

The simplest approach might be to populate the Control Source of your Job textbox in Form2 with a Switch statement similar to the following:

=Switch(
    [Forms]![Form1]![RollFromInventory] is not null, [Forms]![Form1]![RollFromInventory],
    [Forms]![Form1]![RollFromMachine1]  is not null, [Forms]![Form1]![RollFromMachine1],
    [Forms]![Form1]![RollFromMachine2]  is not null, [Forms]![Form1]![RollFromMachine2],
    [Forms]![Form1]![RollFromMachine3]  is not null, [Forms]![Form1]![RollFromMachine3]
)

Though, this isn't particularly pretty and reeks of bad interface design - it sounds like a set of radio buttons and a single textbox would be more appropriate for this scenario.

If you want to implement this in VBA, you could use a set of nested If statements or Nz expressions, e.g.:

Forms![PrinterWaxLabel].JOB = Nz(Nz(InvPW, FHPW), WxPW)