Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB6, Access97 SQL syntax

Posted on 2000-02-16
13
Medium Priority
?
229 Views
Last Modified: 2010-05-02
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?
0
Comment
Question by:jtallsup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2528875
Your query should like this:
"SELECT * FROM Bills WHERE FieldName = '" & replace(form4.text1.text, "'", "''") & "'"
0
 
LVL 3

Expert Comment

by:MTroutwine
ID: 2528878
You should only have to do:

Form4.Text1.Text

to get the textbox value.
0
 
LVL 3

Expert Comment

by:MTroutwine
ID: 2528879
MTroutwine changed the proposed answer to a comment
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Expert Comment

by:niven
ID: 2528943
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)
0
 

Author Comment

by:jtallsup
ID: 2529069
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?
0
 

Expert Comment

by:niven
ID: 2529089
have you tried that query in access directly?
does that work?
are you selecting on the right field?
does text4.Text have a value?
0
 

Author Comment

by:jtallsup
ID: 2529121
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.
0
 
LVL 2

Expert Comment

by:p_biggelaar
ID: 2529201
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]![text1]" 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
0
 

Author Comment

by:jtallsup
ID: 2529298
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.
0
 
LVL 2

Expert Comment

by:p_biggelaar
ID: 2529379
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("NameOfCommand").Parameters("Avalue").Value = Text1.Text
    DataEnvironment1.Commands("NameOfCommand").Execute
   
    Set txtWhereTheDataShouldBeShown.DataSource = DataEnvironment1

This should do it.

Good luck...
0
 
LVL 2

Accepted Solution

by:
p_biggelaar earned 150 total points
ID: 2529383
I assumed that text1.text is on the same form as where you want to change the shown data. Otherwise you can use syntax like form1.text1.text to make sure your referring to the right textboxes on the right form.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2529399
Can you please past your code because with all the things that have been written, it is supposed to work from a long time!
0
 

Author Comment

by:jtallsup
ID: 2529496
Don't quite have it yet, but you definitely have me on the right track. Thanks for the help.
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question