Subquery

[The Doctor]

Question: On a form I have the following SQL:

Select table.row FRom table;

And I would like

Select table.row FRom table WHERE( Bring up string from user input);

..

How do I do that?

 

[Access Developer]

Do you mean that the SQL you show is the Record Source property of the Form?

If so, you could use a Filter, replace the Row Source with the other (I
think you don't even need to Requery, but I could be mis-remembering), or
execute a DoCmd.OpenForm with the WhereCondition that the user entered...
you might, however, want to limit what the user can enter to prevent them
from causing you to try to execute invalid conditions.

 

[The Doctor]

Do you mean that the SQL you show is the Record Source property of the Form?

If so, you could use a Filter, replace the Row Source with the other (I
think you don't even need to Requery, but I could be mis-remembering), or
execute a DoCmd.OpenForm with the WhereCondition that the user entered...
you might, however, want to limit what the user can enter to prevent them
from causing you to try to execute invalid conditions.

I mean to say substring.

 

[Access Developer]

Help us to help you...

In what context do you have the SQL "on the form"? That is, SQL is just a
string of text until you use it some way, and you don't just have strings of
text, SQL or otherwise, "on a form".

What do you want to do with it? Execute it as a standalone SELECT query,
use it as RecordSource for this form, or another form, or a report?

I think the FAQ for the USENET newsgroup comp.databases.ms-access says that
you get the best answers by being detailed, precise, and concise with your
questions. Otherwise, we just have to guess at what you want, and I've done
all the guessing I am prepared to do in re: this question. But, if you'll
explain what you have, and what you want, I will try to answer.

 

[John W. Vinson]

Question: On a form I have the following SQL:

Select table.row FRom table;

And I would like

Select table.row FRom table WHERE( Bring up string from user input);

.

How do I do that?

I think you want a Parameter query. This can be done in two ways:

1. Using a prompt:

SELECT <field, field, field> FROM table WHERE [Fieldname] = [Please enter the
criterion:];

The text in square brackets will be shown on screen as a prompt.

2. Better, getting the criterion from an unbound control on a Form:

SELECT <field, field, field> FROM table WHERE [fieldname]=
[Forms]![NameOfYourForm]![NameOfYourControl];

A clearer question may well elicit a more detailed answer.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[The Doctor]

Question: On a form I have the following SQL:

Select table.row FRom table;

And I would like

Select table.row FRom table WHERE( Bring up string from user input);

.

How do I do that?

I think you want a Parameter query. This can be done in two ways:

1. Using a prompt:

SELECT <field, field, field> FROM table WHERE [Fieldname] = [Please enter the
criterion:];

The text in square brackets will be shown on screen as a prompt.

2. Better, getting the criterion from an unbound control on a Form:

SELECT <field, field, field> FROM table WHERE [fieldname]=
[Forms]![NameOfYourForm]![NameOfYourControl];

A clearer question may well elicit a more detailed answer.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

Wel the criteria is the user input as the user enter's it.

 

[John W. Vinson]

Wel the criteria is the user input as the user enter's it.

I'm sorry, I have no idea what you mean here. Is this a question?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com