Assistance with variables

[Andrew]

Hi,

I have the following code that works......

The_Loop = 0
The_Count = Me!lb_Commitment_Status.ItemsSelected.Count
Set ctl = lb_Commitment_Status
For Each varItem In ctl.ItemsSelected
The_Loop = The_Loop + 1
If The_Loop = 1 Then lb_Commitment_Status_What = "("
lb_Commitment_Status_What = lb_Commitment_Status_What &
"(Tbl_Combined.Commitment_Status)='" & ctl.ItemData(varItem) & "'"
If The_Loop < The_Count Then lb_Commitment_Status_What =
lb_Commitment_Status_What & " OR "
Next varItem
lb_Commitment_Status_What = lb_Commitment_Status_What & ")"

I am building code for an SQL and this is the first bit. There are 10
variables ('Commitment Status' in the above example) and I'd like to simplify
it using a for...next loop.

I've tried a few times but can't quite get it to work.

Here is what I have....

For I = 1 To 10
If I = 1 Then The_Field = "Commitment_Status"
If I = 2 Then The_Field = "Company"
If I = 3 Then The_Field = "Report_Period"
If I = 4 Then The_Field = "Week"
If I = 5 Then The_Field = "OTB_FY"
If I = 6 Then The_Field = "Season1"
If I = 7 Then The_Field = "PO_Distribution_Method"
If I = 8 Then The_Field = "PO_Type"
If I = 9 Then The_Field = "PO_Status"
If I = 10 Then The_Field = "Vendor"

The_Loop = 0
The_Count = Me("lb_" & The_Field).ItemsSelected.Count
Set ctl = "lb_" & The_Field
For Each varItem In ctl.ItemsSelected
The_Loop = The_Loop + 1
If The_Loop = 1 Then lb_Commitment_Status_What = "("
lb_Commitment_Status_What = lb_Commitment_Status_What &
"(Tbl_Combined.Commitment_Status)='" & ctl.ItemData(varItem) & "'"
If The_Loop < The_Count Then lb_Commitment_Status_What =
lb_Commitment_Status_What & " OR "
Next varItem
lb_Commitment_Status_What = lb_Commitment_Status_What & ")"

Next I

I get an error (Object Required) on the line: Set ctl = "lb_" &
Commitment_Status

 

[Robert Morley]

I think you wanted

Set ctl = Me("lb_" & The_Field)

Also, a faster way than your If/Then is to use either Select/Case if it's
something complex:

Select Case i
Case 1
The_Field = "Commitment_Status"
'more code here if you needed it for something
Case 2
The_Field = "Company"
...
End Select

Or, for a simple assignment such as you have:

The_Field = Choose(i, "Commitment_Status", "Company", etc.)


Rob

 

[Andrew]

Thanks Rob,

As you suggested "Set ctl = Me("lb_" & The_Field)" works.

Can you please assist with the statement:
If The_Loop = 1 Then lb_Commitment_Status_What = "("

 

[Robert Morley]

I probably can, but I'm not sure what it is you're looking for the statement
to do.


Rob

 

[Andrew]

Hi Rob,

I'm trying to make a dynamic variable (my best description).

I'll change my sample to make it a bit clearer and then use your suggestion
to get the original working again.

For I = 1 To 4
If I = 1 Then The_Field = "Commitment_Status"
If I = 2 Then The_Field = "Company"
If I = 3 Then The_Field = "Report_Period"
If I = 4 Then The_Field = "Week"


lb_?????????????_What = "Test"


Next I


After completing the for….next loop I want the following variables 'created'
(Test is only an example, I can do that part).


lb_Commitment_Status_What = "Test"
lb_Company_What = "Test"
lb_Report_Period_What = "Test"
lb_Week_What = "Test"


Hope this clears it up

 

[Robert Morley]

Ah, okay, then like you did before, you'd have:

Set ctl = Me("lb_" & The_Field & "_What")
ctl.Value = "Test"

The ".Value" part isn't strictly necessary, but it's considered good form
and can occasional help avoid problems in more advanced code.



Rob