jtallsup
asked on
VB6, Access97 SQL syntax
Need to refer to a textbox while querying Access97:
SELECT * FROM Bills WHERE ??????????
I Need to refer to Text1 on Form4.
I tried [Forms]![Form4]![Text1] and
Forms.Form4.Text1, also with .text behind the "Text1" and several variations. I'm sure someone has this on the tip of their tongue. Perhaps
p biggelaar?
SELECT * FROM Bills WHERE ??????????
I Need to refer to Text1 on Form4.
I tried [Forms]![Form4]![Text1] and
Forms.Form4.Text1, also with .text behind the "Text1" and several variations. I'm sure someone has this on the tip of their tongue. Perhaps
p biggelaar?
You should only have to do:
Form4.Text1.Text
to get the textbox value.
Form4.Text1.Text
to get the textbox value.
MTroutwine changed the proposed answer to a comment
use:
SELECT *froM Bills WHERE yourField='" & form4.text1.Text & "'"
(so you'll get select * from Bills where yourField='sometext'
aka, text/string values need to be quoted)
SELECT *froM Bills WHERE yourField='" & form4.text1.Text & "'"
(so you'll get select * from Bills where yourField='sometext'
aka, text/string values need to be quoted)
ASKER
Returns no records, unless my old eyeballs can't see all those quote marks. I'm a beginner, so I could have something else screwed up, though I don't think so. The field I'm referring to (Text1)is not bound to anything, could that make any difference?
have you tried that query in access directly?
does that work?
are you selecting on the right field?
does text4.Text have a value?
does that work?
are you selecting on the right field?
does text4.Text have a value?
ASKER
No, I haven't tried it directly in access, but the field does have a value, and the query works in all other respects other than this criteria. Could you possibly type your answer out in such a way that I know I'm not misreading the quotes? ie, singleq, doubleq, etc? If you go to that much trouble I'll award you the points regardless of whether or not I can get it to work. As I said, I'm a beginner, and I may have something else screwed up. This isn't an absolute requirement for what I'm working on. It would just make it a little cleaner.
Hello,
I must say I'm pleasantly surprised by your confidence in me. Now let's say if I can help.
Syntax like "Select * from Bills where [Field]=[Forms]![Form4]![t ext1]" should work, but only when you use this select statement from within Access while (the Access form) Form4 is loaded.
In case
- Form4 is not loaded
- you're trying this from outside Access
- you're referring to a VB form, not an Access form
it won't work.
Please tell me, where are you using the select statement and what is your code 'surrounding' this statement
I must say I'm pleasantly surprised by your confidence in me. Now let's say if I can help.
Syntax like "Select * from Bills where [Field]=[Forms]![Form4]![t
In case
- Form4 is not loaded
- you're trying this from outside Access
- you're referring to a VB form, not an Access form
it won't work.
Please tell me, where are you using the select statement and what is your code 'surrounding' this statement
ASKER
OK, guess I wasn't too clear. I am indeed outside access. This is a command from within VB Dataenvironment, refering to a VB Form, which is open.
Oh, then you can't use that syntax. The forms collection is something that Access has, but VB is built differently and doesn't have a forms collection.
Okay. You're command should look something like:
Select * from Bills where MyField=AValue
Assuming AValue doesn't exist, then you can go to the parameter tab of the command and validate that the command object now has AValue as a parameter.
Say you want to validate the values whenever you press a button (if it is another event: feel free) then something like this should be in your button_Click:
DataEnvironment1.Commands( "NameOfCom mand").Par ameters("A value").Va lue = Text1.Text
DataEnvironment1.Commands( "NameOfCom mand").Exe cute
Set txtWhereTheDataShouldBeSho wn.DataSou rce = DataEnvironment1
This should do it.
Good luck...
Okay. You're command should look something like:
Select * from Bills where MyField=AValue
Assuming AValue doesn't exist, then you can go to the parameter tab of the command and validate that the command object now has AValue as a parameter.
Say you want to validate the values whenever you press a button (if it is another event: feel free) then something like this should be in your button_Click:
DataEnvironment1.Commands(
DataEnvironment1.Commands(
Set txtWhereTheDataShouldBeSho
This should do it.
Good luck...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you please past your code because with all the things that have been written, it is supposed to work from a long time!
ASKER
Don't quite have it yet, but you definitely have me on the right track. Thanks for the help.
"SELECT * FROM Bills WHERE FieldName = '" & replace(form4.text1.text, "'", "''") & "'"