Retrieving Textbox Values

[Glint]

Hi,

I have a myriad of textboxes on my form, and from time to time, I need to
retrieve some of their values in comma-separated format. The textboxes are
named in the format of a letter and two digits, for easy identification, like
A22 or B54 etc.
Before a textbox is updated, I want a display of values in the adjoining
boxes displayed to assist a user in making his decision on a new entry.

This is the code that I tried on their got-focus event:

'If TypeOf Screen.ActiveControl Is TextBox Then
If IsNull(Screen.ActiveControl) Then
Dim X As Integer, P As Integer, Y As Integer, Z As Integer, S As
String, Tx As Control, ImpoList As String
S = Screen.ActiveControl.Name
X = Mid(S, 2, 1)
Y = Right(S, 1)
Z = Screen.ActiveControl.Tag
For Each Tx In Screen.ActiveForm
If TypeOf Tx Is TextBox Then
If Tx.Name = "A" & Y & P Then
For P = 1 To 10
DoCmd.GoToControl Tx.Name
If Not IsNull(Tx) Then
ImpoList = ImpoList & "," & Tx.Text
End If
Next
End If
End If
Next
MsgBox ImpoList
End If
'End If

However, ImpoList is null after running the code, and I can see several
textboxes are not null. The code seems to work up to the line before
ImpoList=Impolist & "," & Tx
Can you please help me out?

 

[Douglas J. Steele]

For Each Tx In Screen.ActiveForm

isn't valid: I believe you mean

For Each Tx In Screen.ActiveForm.Controls

As well, you haven't assigned a value to P before your statement

If Tx.Name = "A" & Y & P Then

However, I don't see any reason to loop through the Controls collection at
all. If you want what's in the given row, it should be sufficient to use

If IsNull(Screen.ActiveControl) Then
Dim X As Integer, P As Integer, Y As Integer, S As String,
ImpoList As String
Dim Prefix As String
S = Screen.ActiveControl.Name
Prefix = Left(S, 1)
X = Mid(S, 2, 1)
Y = Right(S, 1)
For P = 1 To 9
With Screen.ActiveForm
If Not IsNull(.Controls(Prefix & X & P)) Then
ImpoList = ImpoList & "," & .Controls(Prefix & Y & P)
End If
End With
Next P
MsgBox ImpoList
End If

or for a given column

If IsNull(Screen.ActiveControl) Then
Dim X As Integer, P As Integer, Y As Integer, S As String,
ImpoList As String
Dim Prefix As String
S = Screen.ActiveControl.Name
Prefix = Left(S, 1)
X = Mid(S, 2, 1)
Y = Right(S, 1)
For P = 1 To 9
With Screen.ActiveForm
If Not IsNull(.Controls(Prefix & P & Y)) Then
ImpoList = ImpoList & "," & .Controls(Prefix & P & Y)
End If
End With
Next P
MsgBox ImpoList
End If

Note that I've changed P to only got from 1 to 9. I don't understand how you
could use 10 in there, given your text box names!

 

[Glint]

Thanks for your help, Douglas.
Replacing "For Each Tx In Screen.ActiveForm" with "For Each Tx In
Screen.ActiveForm.Controls" did the magic. I appreciate it.

NB
This is not the right place to mention this but i wonder if anyone else
finds it hard to log in to the forum. I found I could not log in from my
laptop last night; the "Sign Out" sign was permanently on, no matter what I
did. I could not log out of it, nor would it log me in.

 

[Douglas J. Steele]

Can't help you there: I use a newsgroup reader rather than the web interface
as I don't like Microsoft's web interface.