Dynamic labelname Adressing

[Guest]

I have 10 labels, naming lb0/lb1/..../lb9

I want to control the placement/caption with one Sub procedure, not 10!
for i = 0 to 9

My problem:

dim txt$
txt = ""lb" & i
me.txt.caption obviously won't work!!

What do I have to use to obtain this??

 

[Allen Browne]

You can use a string variable to refer to a control like this:
Me(txt$).Caption = "Say what?"

 

[Tim Ferguson]

dim txt$
txt = ""lb" & i
me.txt.caption obviously won't work!!

for i = 1 to 0
me.controls("lb" & i).caption = "Changed"
next i


or the more reliable version which uses

me.controls(format(i, """lb""0").caption


Hope that helps


Tim F

 

[Guest]

I get error with this sub...Seems to be somewhere in my DLookup code. I do
not get much further...

Sub PUMPlabeling(ByVal i As Integer)
Dim Hnumber$
Hnumber = "lbHnumber" & i

Dim valSelected$
valSelected = Me.Controls("cboH" & i).Value
Dim valnumber$,
valnumber = DLookup("[Hnumber]", "H_system", "[Hnumber] =" & valSelected)
FillinLABEL Hnumber, valnumber
End Sub

FilinLABEL is just a Sub filling in values in labels.

 

[Tim Ferguson]

I get error with this sub...Seems to be somewhere in my DLookup code.
I do not get much further...

Some comments below, but really you need to supply the actual error
message and the line it occurs on...

Sub PUMPlabeling(ByVal i As Integer)

Dim Hnumber$

Very old-style declaration; not incorrect but it's clearer and
more likely to avoid problems by using Dim hNumber as String

Hnumber = "lbHnumber" & i

Safer to use explicit Format expression: with low values this is
obviously okay, but with large numbers you may find yourself faced with
an expression like "lbHnumber1.34E4" or whatever.

Dim valSelected$

See above. Dim strValSelected As String

valSelected = Me.Controls("cboH" & i).Value

Remember that any Textbox.Value can be a Null -- therefore it's safer to
catch this either with a Nz() function or using a Variant.

Dim valnumber$,

There is a comma at the end of this: this should not even pass the editor
without flagging the error. Again, if it is picking up a DLookup() it
should be capable of accepting a Null, so use a Variant instead.

valnumber = DLookup("[Hnumber]", "H_system", _
"[Hnumber] =" & valSelected)

I don't see the point of passing a hNumber value and asking for the same
thing back. There are only two possible returns from this lookup: either
Null (there is no such Hnumber) or Hnumber itself (which you already
know). If it's Null, then the command will fail because valnumber is
declared as a string. I suggest you change this to something like

dim fNotThere as Boolean
fNotThere = (0 = DLookup("Hnumber", "h_system", _
"Hnumber=" & valSelected)

You don't seem to be doing anything with this value anyway. The call to
FillInLabel uses the same value as valSelected.

Another potential problem: I note that you are using a string variable
for valSelected, although I guess that Hnumber is a numeric. It is is in
fact a text field, you need to quote the thing properly:

"Hnumber=""" & valSelected & """"

but if it's a number then the expression is fine as it is.

FillinLABEL Hnumber, valnumber

End Sub



FilinLABEL is just a Sub filling in values in labels.

This is not spelled the same as the call above: which one is the typo?<g>

Hope that helps. Please post again with the actual error information if
you need further advice.

Best wishes


Tim F

 

[Guest]

Hello Tim,

I did not quote my textfield corretcly! Once again you helped me with my
problems, I thank you for that.
I will try to use your comments in my code, using variant instead of string
will probably be more safe in use.

Thank you,

Zurn

I get error with this sub...Seems to be somewhere in my DLookup code.
I do not get much further...

Some comments below, but really you need to supply the actual error
message and the line it occurs on...

Sub PUMPlabeling(ByVal i As Integer)

Dim Hnumber$

Very old-style declaration; not incorrect but it's clearer and
more likely to avoid problems by using Dim hNumber as String

Hnumber = "lbHnumber" & i

Safer to use explicit Format expression: with low values this is
obviously okay, but with large numbers you may find yourself faced with
an expression like "lbHnumber1.34E4" or whatever.

Dim valSelected$

See above. Dim strValSelected As String

valSelected = Me.Controls("cboH" & i).Value

Remember that any Textbox.Value can be a Null -- therefore it's safer to
catch this either with a Nz() function or using a Variant.

Dim valnumber$,

There is a comma at the end of this: this should not even pass the editor
without flagging the error. Again, if it is picking up a DLookup() it
should be capable of accepting a Null, so use a Variant instead.

valnumber = DLookup("[Hnumber]", "H_system", _
"[Hnumber] =" & valSelected)

I don't see the point of passing a hNumber value and asking for the same
thing back. There are only two possible returns from this lookup: either
Null (there is no such Hnumber) or Hnumber itself (which you already
know). If it's Null, then the command will fail because valnumber is
declared as a string. I suggest you change this to something like

dim fNotThere as Boolean
fNotThere = (0 = DLookup("Hnumber", "h_system", _
"Hnumber=" & valSelected)

You don't seem to be doing anything with this value anyway. The call to
FillInLabel uses the same value as valSelected.

Another potential problem: I note that you are using a string variable
for valSelected, although I guess that Hnumber is a numeric. It is is in
fact a text field, you need to quote the thing properly:

"Hnumber=""" & valSelected & """"

but if it's a number then the expression is fine as it is.

FillinLABEL Hnumber, valnumber

End Sub



FilinLABEL is just a Sub filling in values in labels.

This is not spelled the same as the call above: which one is the typo?<g>

Hope that helps. Please post again with the actual error information if
you need further advice.

Best wishes


Tim F

 

[Tim Ferguson]

I will try to use your comments in my code, using variant instead of
string will probably be more safe in use.

Remember that if you use a Variant you still have to check it for IsNull
() before handing it off to any other string function...



By the way, as you probably guessed, this was a rush of the blood to the
head. The function should be a DCount() not a DLookup(). That is the
point: DCount always returns a number (zero if the record is not found,
one if it was found, and more than one if you have duplicate records,
which should never happen).

I just noticed a missing bracket at the end too.

All the best


Tim F