Prev: Curious Reaction to subform SQL statement
Next: IF in a forms ON Load event...
From: FKlusmann via AccessMonster.com on 18 Jan 2010 14:23
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

From: Marshall Barton on 18 Jan 2010 16:09
FKlusmann via AccessMonster.com wrote:

>' 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).

--
Marsh
MVP [MS Access]
From: FKlusmann via AccessMonster.com on 18 Jan 2010 16:27
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

First  |  Prev  | 
Pages: 1 2 3 4
Prev: Curious Reaction to subform SQL statement
Next: IF in a forms ON Load event...