Extract individual rows from text box

[Andy]

I have a form with a text box on it. (Access 2003)

Into this text box I am pasting a list of words from a spreadsheet and
they are displaying in the text box as a list - one word per row.

On the click of a command button I am running some VBA code and one of
the things I want to do is to be able to loop through each word (row)
in the text box (to build an SQL statement to query another table).

I haven't been able to work out how to read each row separately. The
TextBox.Values property returns all rows - not one at a time.

Appreciate any help.

Andy

 

[Allen Browne]

In VBA code, you could use Split() to break them into an array at the
CarriageReturn/LineFeed, and then loop through them like this:

Dim varWords as Variant
Dim i As Integer
varWords = Split(Me.[MyTextBox], vbCrLf)
For i = LBound(varWords) To UBound(varWords)
Debug.Print varWords(i)
Next

Assumes the text box contains at least one word.

 

[Andy]

Thanks Allen - quick service!

Haven't had a chance to test but that looks like what I'm after.

Cheers...Andrew
Bendigo

In VBA code, you could use Split() to break them into an array at the
CarriageReturn/LineFeed, and then loop through them like this:

Dim varWords as Variant
Dim i As Integer
varWords = Split(Me.[MyTextBox], vbCrLf)
For i = LBound(varWords) To UBound(varWords)
Debug.Print varWords(i)
Next

Assumes the text box contains at least one word.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a text box on it. (Access 2003)

Into this text box I am pasting a list of words from a spreadsheet and
they are displaying in the text box as a list - one word per row.

On the click of a command button I am running some VBA code and one of
the things I want to do is to be able to loop through each word (row)
in the text box (to build an SQL statement to query another table).

I haven't been able to work out how to read each row separately. The
TextBox.Values property returns all rows - not one at a time.

Appreciate any help.

Andy

 

[Andy]

Worked like a charm! thanks again.

Thanks Allen - quick service!

Haven't had a chance to test but that looks like what I'm after.

Cheers...Andrew
Bendigo

In VBA code, you could use Split() to break them into an array at the
CarriageReturn/LineFeed, and then loop through them like this:

Dim varWords as Variant
Dim i As Integer
varWords = Split(Me.[MyTextBox], vbCrLf)
For i = LBound(varWords) To UBound(varWords)
Debug.Print varWords(i)
Next

Assumes the text box contains at least one word.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a text box on it. (Access 2003)

Into this text box I am pasting a list of words from a spreadsheet and
they are displaying in the text box as a list - one word per row.

On the click of a command button I am running some VBA code and one of
the things I want to do is to be able to loop through each word (row)
in the text box (to build an SQL statement to query another table).

I haven't been able to work out how to read each row separately. The
TextBox.Values property returns all rows - not one at a time.

Appreciate any help.

Andy