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

>guessing there is still a probelm or two in your code.
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

--
Message posted via http://www.accessmonster.com

From: FKlusmann via AccessMonster.com on 17 Jan 2010 23:50
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

>> Thank you.
>> -- Fred

--
Message posted via http://www.accessmonster.com

From: Douglas J. Steele on 18 Jan 2010 07:03
"FKlusmann via AccessMonster.com" <u57520(a)uwe> wrote in message
news:a2473fad93f56(a)uwe...
> 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)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



From: FKlusmann via AccessMonster.com on 18 Jan 2010 11:40
Thank you!

Douglas J. Steele wrote:
>
>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

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

From: Marshall Barton on 18 Jan 2010 12:14
FKlusmann via AccessMonster.com wrote:
>>What code did you use when you tried it?
> Other than what I already posted,
>[code]
>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

TextBoxName is used as a String and should be dimed as such.

> For ctr = 1 To linecount
> TextBoxName = ("Line0" & CStr(ctr))

If you ever need more than 9 of these text boxes, the above
line should be:
TextBoxName = "Line" & Format(ctr, "00")

> Line05 = TextBoxName ' something to put in the text box.

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

[snip]
>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 & "'"")

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

--
Marsh
MVP [MS Access]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Curious Reaction to subform SQL statement
Next: IF in a forms ON Load event...