populate textbox by name(number)

[FKlusmann]

Hello and thanks again.
I'd like to get several lines of text from a table and display them in forms
textboxes.

My table is TextTest01 with 3 fields (no PK, no index):
sel is text field size: 2
lineNo is number long integer
line is text field size 80
My unbound form fTestText01 has 10 textboxes named Line01, Line02 .. To
Line10.

My code efforts are:
Private Sub Form_Open(Cancel As Integer)
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim varResult As Variant ' will hold the answer Dlookup provides
Dim txtboxname As Variant ' the name of form's textbox to be populated
Dim selct As String ' A group indicator will come from a table
selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display
For ctr = 1 To linecount
' the answer the text the table tbl field match value
varResult = DLookup("[line]", "TextTest01", "[sel] = '" & [selct] & "'")
' this finds the correct text.

' where I want this to be displayed
txtboxname = ("Line0" & CStr(ctr)) ' dim'd as object gives error 91
' this holds the correct form textbox names
' Me.Controls("txtboxname").Text
' txtboxname dim'd as string gave error 2465 Tab Tests -(Keep) can't find
the field "txtboxname ..

Set txtboxname = varResult
' when dim'd as string gives error 424 - object required
' when dim'd as object gives
' when dim'd as variant gives run-time error 13 type mismatch
Next ctr
End Sub
....
I would sure like your help to make this work.

Thanks, -- Fred

 

[Douglas J. Steele]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Douglas J. Steele]

Oops: Hit Enter too soon!

I'm not really sure what you're trying to do.

To refer to a control via a variable, you'd use

Dim txtboxnamd As String

txtboxname = "Line0" & CStr(ctr)

and then use Me.Controls(txtboxname)

You cannot refer to the Text property of a text box unless the text box has
focus. Instead, refer to its Value property.

The following code will work, but would put the same value into all 9
textboxes:

Private Sub Form_Open(Cancel As Integer)
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim varResult As Variant ' will hold the answer Dlookup provides
Dim txtboxname As String ' the name of form's textbox to be populated
Dim selct As String ' A group indicator will come from a table
selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display
For ctr = 1 To linecount
' the answer the text the table tbl field match value
varResult = DLookup("[line]", "TextTest01", "[sel] = '" & [selct] & "'")
txtboxname = "Line0" & CStr(ctr)
Me.Controls(txtboxname).Value = varResult
Next ctr
End Sub

or, more simply

Private Sub Form_Open(Cancel As Integer)
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim selct As String ' A group indicator will come from a table
selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display
For ctr = 1 To linecount
Me.Controls("Line0" & CStr(ctr)) = _
DLookup("[line]", "TextTest01", "[sel] = '" & [selct] & "'")
Next ctr
End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello and thanks again.
I'd like to get several lines of text from a table and display them in
forms
textboxes.

My table is TextTest01 with 3 fields (no PK, no index):
sel is text field size: 2
lineNo is number long integer
line is text field size 80
My unbound form fTestText01 has 10 textboxes named Line01, Line02 .. To
Line10.

My code efforts are:
Private Sub Form_Open(Cancel As Integer)
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim varResult As Variant ' will hold the answer Dlookup provides
Dim txtboxname As Variant ' the name of form's textbox to be populated
Dim selct As String ' A group indicator will come from a table
selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display
For ctr = 1 To linecount
' the answer the text the table tbl field match value
varResult = DLookup("[line]", "TextTest01", "[sel] = '" & [selct] & "'")
' this finds the correct text.

' where I want this to be displayed
txtboxname = ("Line0" & CStr(ctr)) ' dim'd as object gives error 91
' this holds the correct form textbox names
' Me.Controls("txtboxname").Text
' txtboxname dim'd as string gave error 2465 Tab Tests -(Keep) can't
find
the field "txtboxname ..

Set txtboxname = varResult
' when dim'd as string gives error 424 - object required
' when dim'd as object gives
' when dim'd as variant gives run-time error 13 type mismatch
Next ctr
End Sub
...
I would sure like your help to make this work.

Thanks, -- Fred

 

[FKlusmann via AccessMonster.com]

Hi and thanks:
You state:
“I'm not really sure what you're trying to do.â€
I hope to give information in the (unbound) form of text lines from a table
when some conditions are met.

For a lack of any better idea I will unhide some textboxes (named Line01,
Line02,…Line0n) and display them on the form. In this basic example, when
‘Welcome’ is selected, I would display the text in each table row field
(named line) from the table (TextTest01) rows with selection (Field named sel
matching “Wâ€) and (Field named lineNo being numbers from 1 to n) .

If I get this to work there will one place to change / add / correct text
(and langueage) for the several forms in this project.

I tried your suggested:

Private Sub Form_Open(Cancel As Integer)
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim varResult As Variant ' will hold the answer Dlookup provides
Dim txtboxname As String ' the name of form's textbox to be populated
Dim selct As String ' A group indicator will come from a table
selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display
For ctr = 1 To linecount
' the answer the text the table tbl field match value
varResult = DLookup("[line]", "TextTest01", "[sel] = '" & [selct] & "'")
txtboxname = "Line0" & CStr(ctr)
Me.Controls(txtboxname).Value = varResult
Next ctr
End Sub
[End Quote]

But get “Run-time error ‘2448’: You can’t assign a value to this object†at
the line
Me.Controls(txtboxname).Value = varResult

I also tired:

Private Sub Form_Open(Cancel As Integer)
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim selct As String ' A group indicator will come from a table
selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display
For ctr = 1 To linecount
Me.Controls("Line0" & CStr(ctr)) = _
DLookup("[line]", "TextTest01", "[sel] = '" & [selct] & "'")
Next ctr
End Sub
[End Quote]

and also get:
But get “Run-time error ‘2448’: You can’t assign a value to this object†at
the line
Me.Controls(txtboxname).Value = varResult

I am open to any ideas and suggestions.

Again, Thank you.

-- Fred

 

[Douglas J. Steele]

You sure Line01, Line02 etc are text boxes and not some other type of
control and that they're unbound?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi and thanks:
You state:
“I'm not really sure what you're trying to do.â€
I hope to give information in the (unbound) form of text lines from a
table
when some conditions are met.

For a lack of any better idea I will unhide some textboxes (named Line01,
Line02,…Line0n) and display them on the form. In this basic example, when
‘Welcome’ is selected, I would display the text in each table row field
(named line) from the table (TextTest01) rows with selection (Field named
sel
matching “Wâ€) and (Field named lineNo being numbers from 1 to n) .

If I get this to work there will one place to change / add / correct text
(and langueage) for the several forms in this project.

I tried your suggested:

Private Sub Form_Open(Cancel As Integer)
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim varResult As Variant ' will hold the answer Dlookup provides
Dim txtboxname As String ' the name of form's textbox to be populated
Dim selct As String ' A group indicator will come from a table
selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display
For ctr = 1 To linecount
' the answer the text the table tbl field match value
varResult = DLookup("[line]", "TextTest01", "[sel] = '" & [selct] & "'")
txtboxname = "Line0" & CStr(ctr)
Me.Controls(txtboxname).Value = varResult
Next ctr
End Sub
[End Quote]

But get “Run-time error ‘2448’: You can’t assign a value to this objectâ€
at
the line
Me.Controls(txtboxname).Value = varResult

I also tired:

Private Sub Form_Open(Cancel As Integer)
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim selct As String ' A group indicator will come from a table
selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display
For ctr = 1 To linecount
Me.Controls("Line0" & CStr(ctr)) = _
DLookup("[line]", "TextTest01", "[sel] = '" & [selct] & "'")
Next ctr
End Sub
[End Quote]

and also get:
But get “Run-time error ‘2448’: You can’t assign a value to this objectâ€
at
the line
Me.Controls(txtboxname).Value = varResult

I am open to any ideas and suggestions.

Again, Thank you.

-- Fred

 

[FKlusmann via AccessMonster.com]

You sure Line01, Line02 etc are text boxes and not some other type of
control and that they're unbound?

Yes sir, nothing on this test form is bound.

Also, I cannot figure the type mismatch in (new problem):
varResult = DLookup("[line]", "TextTest01", "[sel] = '" & selct & "'" And "
[lineNo] = " & ctr)

Thank you.
-- Fred

 

[Marshall Barton]

FKlusmann via AccessMonster.com wrote:
[snip]

I tried your suggested:

Private Sub Form_Open(Cancel As Integer) [snip]
Me.Controls(txtboxname).Value = varResult [snip]
But get “Run-time error ‘2448’: You can’t assign a value to this object” at
the line
Me.Controls(txtboxname).Value = varResult

The Open event is probably too early to set a control's
value. Try the Load event instead.

 

[FKlusmann via AccessMonster.com]

[snip]

The Open event is probably too early to set a control's
value. Try the Load event instead.

I tried, but that does not work either.

I've about convinced myself that there is no way to program change in text
box names.

Thanks for trying!
-- Fred

 

[Marshall Barton]

[snip]

The Open event is probably too early to set a control's
value. Try the Load event instead.

I tried, but that does not work either.

I've about convinced myself that there is no way to program change in text
box names.

Thanks for trying!

Well, I tried, but your "that does not work either" doesn't
say much for your effort.

What happened? Did you get the same error? A different
error?

What code did you use when you tried it?

I have done this kind of thing many times and I suspect that
Doug has too, so, the devil being in the details, I'm
guessing there is still a probelm or two in your code.

 

[Steve Sanford]

You have double quotes in the wrong place. The line should be:

varResult = DLookup("[line]", "TextTest01", "[sel] = '" & selct & "' And
[lineNo] = " & ctr)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

You sure Line01, Line02 etc are text boxes and not some other type of
control and that they're unbound?

Yes sir, nothing on this test form is bound.

Also, I cannot figure the type mismatch in (new problem):
varResult = DLookup("[line]", "TextTest01", "[sel] = '" & selct & "'" And "
[lineNo] = " & ctr)

Thank you.
-- Fred

 

[FKlusmann via AccessMonster.com]

Thanks Marshall:

Well, I tried, but your "that does not work either" doesn't
say much for your effort.

It says that I am frustrated and will probably do it manually.

What code did you use when you tried it?

Other than what I already posted,

Private Sub Form_Load()
Dim linecount As Integer     ' Line conter (set to 9 for this test)
Dim ctr As Long              ' Counter to be incremented
Dim selct As String          ' A group indicator will come from a table
Dim varResult As Variant     ' will hold the answer Dlookup provides
linecount = 9                ' maximum number of lines to display
Dim TextBoxName As Variant
For ctr = 1 To linecount
TextBoxName = ("Line0" & CStr(ctr))
Line05 = TextBoxName    ' something to put in the text box.
Next ctr
End

' txtboxname = ("Line0" & CStr(ctr))   ' dim'd as object gives error 91
' ' when dim'd as string gives error 424 - object required
' ' when dim'd as variant gives run-time error 13 type mismatch
' Me.Controls("Line0" & CStr(ctr)) =

and probably others which I just trashed.
[QUOTE]
guessing there is still a probelm or two in your code.[/QUOTE]
I'd bet a cup of coffee that you are correct!

While I have your attention;
varResult = DLookup("[LineOfText]", "[tTextTest01]", "[lineNo] = " & Me.[Lno]
& "") ' worked, criteria is a Number

and
varResult = DLookup("[LineOfText]", "[tTextTest01]", "[sel] = '" & Me.Selct &
"'")  ' worked, criteria is a String

but I have not found how to fix the syntax of
varResult = DLookup("[LineOfText]", "[tTextTest01]", ""[lineNo] = " & Me.[Lno]
& "  And "[sel] = '" & Me.Selct & "'"")

I appreciate your suggestions.
-- Fred

 

[FKlusmann via AccessMonster.com]

Steve Sanford wrote You have double quotes in the wrong place. The line
should be:

varResult = DLookup("[line]", "TextTest01", "[sel] = '" & selct & "' And [lineNo] = " & ctr)

Steve, I copied and pasted this and still got Syntax Error.
I changed the field name from "line" to LineOfText in the tanle just to
verify that I did not have a reserved word.

My last failure is:
varResult = DLookup("[LineOfText]", "TextTest01", "[sel] = '" & Selct & "'
And [lineNo] = " & Lno)"

Note that this query does work:
SELECT tTextTest01.LineOfText
FROM tTextTest01
WHERE (((tTextTest01.sel)=[Forms]![fTextTest01]![Selct]) AND ((tTextTest01.
lineNo)=[Forms]![fTextTest01]![Lno]));

but I have not been able to put the result into my unbound form's unbound
text boxes.

Again, I thank you.
-- Fred

 

[Douglas J. Steele]

Steve Sanford wrote You have double quotes in the wrong place. The line
should be:

varResult = DLookup("[line]", "TextTest01", "[sel] = '" & selct & "' And
[lineNo] = " & ctr)

Steve, I copied and pasted this and still got Syntax Error.
I changed the field name from "line" to LineOfText in the tanle just to
verify that I did not have a reserved word.

My last failure is:
varResult = DLookup("[LineOfText]", "TextTest01", "[sel] = '" & Selct & "'
And [lineNo] = " & Lno)"

Remove the double quote from the end.

varResult = DLookup("[LineOfText]", "TextTest01", "[sel] = '" & Selct & "'
And [lineNo] = " & Lno)

 

[FKlusmann via AccessMonster.com]

Thank you!

Remove the double quote from the end.

varResult = DLookup("[LineOfText]", "TextTest01", "[sel] = '" & Selct & "'
And [lineNo] = " & Lno)

I've got to get my glasses cleaned......

If you can, please point me to an explanation for the use of quotes,
apostrophes, ampersands, plus signs, etc., as they change for string and
numeric varibles.

Thanks again!
-- Fred

 

[Marshall Barton]

What code did you use when you tried it?

Other than what I already posted,

Private Sub Form_Load()
Dim linecount As Integer     ' Line conter (set to 9 for this test)
Dim ctr As Long              ' Counter to be incremented
Dim selct As String          ' A group indicator will come from a table
Dim varResult As Variant     ' will hold the answer Dlookup provides
linecount = 9                ' maximum number of lines to display
Dim TextBoxName As Variant[/QUOTE]

TextBoxName is used as a String and should be dimed as such.
[QUOTE]
For ctr = 1 To linecount
TextBoxName = ("Line0" & CStr(ctr))[/QUOTE]

If you ever need more than 9 of these text boxes, the above
line should be:
TextBoxName = "Line" & Format(ctr, "00")
[QUOTE]
Line05 = TextBoxName    ' something to put in the text box.[/QUOTE]

You should now be able to use the corrected DLookup:
Me.Controls(TextBoxName) + DLookup(...

[snip][QUOTE]
While I have your attention;
varResult = DLookup("[LineOfText]", "[tTextTest01]", "[lineNo] = " & Me.[Lno]
& "") ' worked, criteria is a Number

and
varResult = DLookup("[LineOfText]", "[tTextTest01]", "[sel] = '" & Me.Selct &
"'")  ' worked, criteria is a String

but I have not found how to fix the syntax of
varResult = DLookup("[LineOfText]", "[tTextTest01]", ""[lineNo] = " & Me.[Lno]
& "  And "[sel] = '" & Me.Selct & "'"")[/QUOTE]

Those quotes are still way out of whack.  Use the one that
Doug fixed up.

 

[FKlusmann via AccessMonster.com]

Thank you all! This finally works"

Marshall Barton, Please look at the lines pointed with <<<--- below as they
have comments for you.
You pointed me in the right direction!

I moved the code to a Command Button here.

Private Sub CmdPopulate_Click()
On Error GoTo Err_CmdPopulate_Click
Dim linecount As Integer ' Line conter (set to 9 for this test)
Dim ctr As Integer ' Counter to be incremented
Dim varResult As Variant ' will hold the answer Dlookup provides
Dim txtboxname As Variant ' the name of form's textbox to be populated
Dim Selct As String ' A group indicator will come from a table
Selct = "W" ' W indicates Welcome
linecount = 9 ' maximum number of lines to display for this test
For ctr = 1 To linecount
Lno = ctr ' Lno is a textbox used in DLookup as ctr did not
work for me.
varResult = DLookup("[LineOfText]", "tTextTest01", "[sel] = '" & Selct & "'
And [lineNo] = " & Lno) 'works! - Thanks Douglas J. Steele!!!
TextBoxName = "Line" & Format(ctr, "00")
' Me.Controls (TextBoxName) + DLookup("[LineOfText]", "tTextTest01", "[sel]
= '" & Selct & "' And [lineNo] = " & Lno)
' Me.Controls(TextBoxName) + DLookup(...
' Marshall, VB forced the space between ..rols and (Text.. in the second
line above. <<<<<<----
' Then reports "Invalid use of property"
<<<<<<----
' Me.Controls(TextBoxName).Value = varResult ' This does work!
Me.Controls(TextBoxName).Value = DLookup("[LineOfText]", "tTextTest01", "
[sel] = '" & Selct & "' And [lineNo] = " & Lno)
' This is working now!!
Next ctr
Exit_CmdPopulate_Click:
Exit Sub
Err_CmdPopulate_Click:
MsgBox Err.Description
Resume Exit_CmdPopulate_Click
End Sub

Thanks again!
-- Fred

 

[Marshall Barton]

' Me.Controls(TextBoxName) + DLookup(...

has a + instead of = so it can't work.

But you got a useful result, so a simple typo is not
important.

Somewhere down the road, you may want to explore using a
recordset instead of a bunch of DLookup calls. It might
even be easier (less and simpler code) and faster (if that
matters).

 

[FKlusmann via AccessMonster.com]

Thanks again Marshall.

But you got a useful result, ... which is important.

Somewhere down the road, you may want to explore using a
recordset instead of a bunch of DLookup calls. It might
even be easier (less and simpler code) and faster (if that
matters).

Thanks.
I got volunteered into this project. My last database programing was dBase-II,
maybe 20 years back.
I did not expect learning curve to be so close to 90 degrees, nor for the
help files and recommended books to be so limited.

My best days started with ,004,008,.... and a yellow [Load] button.

Thanks again