Can this be done without SQL?

  • Thread starter Thread starter 1.156
  • Start date Start date
1

1.156

We've got a pretty useful database working but neither of us knows what
SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has to
type in a genome and the query shows all the records with this genome in
the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in the
genome table. Can this be done without writing SQL code?

Thanks for any help,
 
We've got a pretty useful database working but neither of us knows what
SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has to
type in a genome and the query shows all the records with this genome in
the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in the
genome table. Can this be done without writing SQL code?

Thanks for any help,
Click to expand...

When you use the QueryByExample grid to create a query, Access writes
the actual SQL code that really does the work. To read the code, in
Query Design View, click on the Query View tool button and select SQL.

You have the right idea to use the table to allow the user to
correctly select the genome wanted. Here's how. But first a question.
Does the table that contains the list of genomes have a Unique ID
field? If so .....
 
Fred, thanks for taking the time to help.

The primary components to this simple DB is a master table that has a
field called GENOME. Currently, the unused table has two fields called
GENOME and Researcher (the genome discoverer). This unused table can
easily be changed if it will help get the job done. I'll be glad to
rename the GENOME field to something unique, like "G"?. Will this help?

Thanks, Rob
 
We've got a pretty useful database working but neither of us knows what
SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has to
type in a genome and the query shows all the records with this genome in
the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in the
genome table. Can this be done without writing SQL code?

Thanks for any help,
Click to expand...

I hit the wrong tool button and inadvertently sent an incomplete
message.

1) When you use the QueryByExample ('graphics') grid to create a
query, Access writes the actual SQL code that really does the work. To
read the code, in Query Design View click on the Query View tool
button and select SQL.

2) You have the right idea to use the table to allow the user to
correctly select the genome wanted. Here's how. But first a question.
Does the table that contains the list of genomes have a Unique ID
field? It should. If so .....

You'll need to use a form to do this.

Make a new unbound form.
Add a combo box that will show the GenomeID field as well as the
GenomeText field.
Set the Combo Box Bound Column to 1
Set the Column Count to 2
Set the Column Widths property to 0";1"
Set the Auto Expand property to Yes.
Set the Limit to List property to Yes.
Name this combo box "cboSelector"

Add a command button to the form.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Add the GenomeID field to the query grid.
Code the Query GenomeID field criteria line:
forms!ParamForm!cboSelector
(Instead of your current [Enter Genome] criteria. Do NOT use the
brackets. Access will correctly provide them.)

When you wish to run the query, first open the form.
Find the wanted Genome name in the combo box.
Click the command button.

The query will display just those records selected and the form will
close.
 
Fred, thanks again. That's a lot of stuff there. I'll see if I can
follow your instructions, and I'll let you know what happens.
Regards, Rob
We've got a pretty useful database working but neither of us knows what
SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has to
type in a genome and the query shows all the records with this genome in
the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in the
genome table. Can this be done without writing SQL code?

Thanks for any help,
Click to expand...

I hit the wrong tool button and inadvertently sent an incomplete
message.

1) When you use the QueryByExample ('graphics') grid to create a
query, Access writes the actual SQL code that really does the work. To
read the code, in Query Design View click on the Query View tool
button and select SQL.

2) You have the right idea to use the table to allow the user to
correctly select the genome wanted. Here's how. But first a question.
Does the table that contains the list of genomes have a Unique ID
field? It should. If so .....

You'll need to use a form to do this.

Make a new unbound form.
Add a combo box that will show the GenomeID field as well as the
GenomeText field.
Set the Combo Box Bound Column to 1
Set the Column Count to 2
Set the Column Widths property to 0";1"
Set the Auto Expand property to Yes.
Set the Limit to List property to Yes.
Name this combo box "cboSelector"

Add a command button to the form.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Add the GenomeID field to the query grid.
Code the Query GenomeID field criteria line:
forms!ParamForm!cboSelector
(Instead of your current [Enter Genome] criteria. Do NOT use the
brackets. Access will correctly provide them.)

When you wish to run the query, first open the form.
Find the wanted Genome name in the combo box.
Click the command button.

The query will display just those records selected and the form will
close.
Click to expand...
 
Fred, thanks again. That's a lot of stuff there. I'll see if I can
follow your instructions, and I'll let you know what happens.
Regards, Rob
We've got a pretty useful database working but neither of us knows what
SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has to
type in a genome and the query shows all the records with this genome in
the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in the
genome table. Can this be done without writing SQL code?

Thanks for any help,
Click to expand...

I hit the wrong tool button and inadvertently sent an incomplete
message.

1) When you use the QueryByExample ('graphics') grid to create a
query, Access writes the actual SQL code that really does the work. To
read the code, in Query Design View click on the Query View tool
button and select SQL.

2) You have the right idea to use the table to allow the user to
correctly select the genome wanted. Here's how. But first a question.
Does the table that contains the list of genomes have a Unique ID
field? It should. If so .....

You'll need to use a form to do this.

Make a new unbound form.
Add a combo box that will show the GenomeID field as well as the
GenomeText field.
Set the Combo Box Bound Column to 1
Set the Column Count to 2
Set the Column Widths property to 0";1"
Set the Auto Expand property to Yes.
Set the Limit to List property to Yes.
Name this combo box "cboSelector"

Add a command button to the form.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Add the GenomeID field to the query grid.
Code the Query GenomeID field criteria line:
forms!ParamForm!cboSelector
(Instead of your current [Enter Genome] criteria. Do NOT use the
brackets. Access will correctly provide them.)

When you wish to run the query, first open the form.
Find the wanted Genome name in the combo box.
Click the command button.

The query will display just those records selected and the form will
close.
Click to expand...
Click to expand...

I read your in-between post, about having just a Genome and Researcher
field in that table.

You can get by just fine using just the Genome (as Text) field.
Instead of having the GenomeID and Genome fields in the combo box,
just use the Genome field. In which case, set the Column Count
property to 1, and the Column Width property to 1". Everything else
should be OK.
Good luck.
 
Fred, thanks again for trying to help. We two got together and went over
your instructions and unfortunately, we can't get understand the very
first line. You say that "QueryByExample.....". We have so far found
no evidence of this name. We use a wizzard to make a query and in
Design view, we find no Query View tool button or anything mentioning SQL.

Skipping a lot of the rest for obvious reasons we have played with
forms. We can make some kind of form but so far have not run into
anything about it being "unbound". Any hint of where you find unbound
forms vs. bound or normal forms?

We'll keep reading -- your advice has given a goal.

We're using Access 2000 on a Wintel machine.

Regards, Rob
Fred, thanks again. That's a lot of stuff there. I'll see if I can
follow your instructions, and I'll let you know what happens.
Regards, Rob

fredg wrote:


On Wed, 19 May 2004 17:43:38 -0500, 1.156 wrote:





We've got a pretty useful database working but neither of us knows what
SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has to
type in a genome and the query shows all the records with this genome in
the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in the
genome table. Can this be done without writing SQL code?

Thanks for any help,




I hit the wrong tool button and inadvertently sent an incomplete
message.

1) When you use the QueryByExample ('graphics') grid to create a
query, Access writes the actual SQL code that really does the work. To
read the code, in Query Design View click on the Query View tool
button and select SQL.

2) You have the right idea to use the table to allow the user to
correctly select the genome wanted. Here's how. But first a question.
Does the table that contains the list of genomes have a Unique ID
field? It should. If so .....

You'll need to use a form to do this.

Make a new unbound form.
Add a combo box that will show the GenomeID field as well as the
GenomeText field.
Set the Combo Box Bound Column to 1
Set the Column Count to 2
Set the Column Widths property to 0";1"
Set the Auto Expand property to Yes.
Set the Limit to List property to Yes.
Name this combo box "cboSelector"

Add a command button to the form.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Add the GenomeID field to the query grid.
Code the Query GenomeID field criteria line:
forms!ParamForm!cboSelector
(Instead of your current [Enter Genome] criteria. Do NOT use the
brackets. Access will correctly provide them.)

When you wish to run the query, first open the form.
Find the wanted Genome name in the combo box.
Click the command button.

The query will display just those records selected and the form will
close.
Click to expand...
Click to expand...

I read your in-between post, about having just a Genome and Researcher
field in that table.

You can get by just fine using just the Genome (as Text) field.
Instead of having the GenomeID and Genome fields in the combo box,
just use the Genome field. In which case, set the Column Count
property to 1, and the Column Width property to 1". Everything else
should be OK.
Good luck.
Click to expand...
 
Fred, we now can see the code -- don't know how we didn't run into it
earlier.

The SQL code looks understandable for the original simple query with no
drop down entry box.

So now if we could just get started with an UNBOUND form, we may start
making process. There seems to be nothing mentioning BOUND or UNBOUND
in all the form stuff we've seen so far.

Rob
Fred, thanks again. That's a lot of stuff there. I'll see if I can
follow your instructions, and I'll let you know what happens.
Regards, Rob

fredg wrote:


On Wed, 19 May 2004 17:43:38 -0500, 1.156 wrote:





We've got a pretty useful database working but neither of us knows what
SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has to
type in a genome and the query shows all the records with this genome in
the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in the
genome table. Can this be done without writing SQL code?

Thanks for any help,




I hit the wrong tool button and inadvertently sent an incomplete
message.

1) When you use the QueryByExample ('graphics') grid to create a
query, Access writes the actual SQL code that really does the work. To
read the code, in Query Design View click on the Query View tool
button and select SQL.

2) You have the right idea to use the table to allow the user to
correctly select the genome wanted. Here's how. But first a question.
Does the table that contains the list of genomes have a Unique ID
field? It should. If so .....

You'll need to use a form to do this.

Make a new unbound form.
Add a combo box that will show the GenomeID field as well as the
GenomeText field.
Set the Combo Box Bound Column to 1
Set the Column Count to 2
Set the Column Widths property to 0";1"
Set the Auto Expand property to Yes.
Set the Limit to List property to Yes.
Name this combo box "cboSelector"

Add a command button to the form.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Add the GenomeID field to the query grid.
Code the Query GenomeID field criteria line:
forms!ParamForm!cboSelector
(Instead of your current [Enter Genome] criteria. Do NOT use the
brackets. Access will correctly provide them.)

When you wish to run the query, first open the form.
Find the wanted Genome name in the combo box.
Click the command button.

The query will display just those records selected and the form will
close.
Click to expand...
Click to expand...

I read your in-between post, about having just a Genome and Researcher
field in that table.

You can get by just fine using just the Genome (as Text) field.
Instead of having the GenomeID and Genome fields in the combo box,
just use the Genome field. In which case, set the Column Count
property to 1, and the Column Width property to 1". Everything else
should be OK.
Good luck.
Click to expand...
 
don't mean to step on fred's toes, just stepping in because not everybody
keeps up with threads that've had no activity for a week or more. (i
probably wouldn't either, except i'm usually way behind on cleaning out my
flagged threads. <g>)
to answer your question re forms: a form is "bound" when there is a table,
query or SQL statement in the form's RecordSource property. if the
RecordSource property is blank, then the form is "unbound" - as in "not
bound to a recordset".

hth


1.156 said:
Fred, we now can see the code -- don't know how we didn't run into it
earlier.

The SQL code looks understandable for the original simple query with no
drop down entry box.

So now if we could just get started with an UNBOUND form, we may start
making process. There seems to be nothing mentioning BOUND or UNBOUND
in all the form stuff we've seen so far.

Rob
Fred, thanks again. That's a lot of stuff there. I'll see if I can
follow your instructions, and I'll let you know what happens.
Regards, Rob

fredg wrote:



On Wed, 19 May 2004 17:43:38 -0500, 1.156 wrote:





We've got a pretty useful database working but neither of us knows what
SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has to
type in a genome and the query shows all the records with this genome in
the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in the
genome table. Can this be done without writing SQL code?

Thanks for any help,




I hit the wrong tool button and inadvertently sent an incomplete
message.

1) When you use the QueryByExample ('graphics') grid to create a
query, Access writes the actual SQL code that really does the work. To
read the code, in Query Design View click on the Query View tool
button and select SQL.

2) You have the right idea to use the table to allow the user to
correctly select the genome wanted. Here's how. But first a question.
Does the table that contains the list of genomes have a Unique ID
field? It should. If so .....

You'll need to use a form to do this.

Make a new unbound form.
Add a combo box that will show the GenomeID field as well as the
GenomeText field.
Set the Combo Box Bound Column to 1
Set the Column Count to 2
Set the Column Widths property to 0";1"
Set the Auto Expand property to Yes.
Set the Limit to List property to Yes.
Name this combo box "cboSelector"

Add a command button to the form.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Add the GenomeID field to the query grid.
Code the Query GenomeID field criteria line:
forms!ParamForm!cboSelector
(Instead of your current [Enter Genome] criteria. Do NOT use the
brackets. Access will correctly provide them.)

When you wish to run the query, first open the form.
Find the wanted Genome name in the combo box.
Click the command button.

The query will display just those records selected and the form will
close.
Click to expand...

I read your in-between post, about having just a Genome and Researcher
field in that table.

You can get by just fine using just the Genome (as Text) field.
Instead of having the GenomeID and Genome fields in the combo box,
just use the Genome field. In which case, set the Column Count
property to 1, and the Column Width property to 1". Everything else
should be OK.
Good luck.
Click to expand...
Click to expand...
 
Tina, thanks a lot. I believe this will help.
Rob
don't mean to step on fred's toes, just stepping in because not everybody
keeps up with threads that've had no activity for a week or more. (i
probably wouldn't either, except i'm usually way behind on cleaning out my
flagged threads. <g>)
to answer your question re forms: a form is "bound" when there is a table,
query or SQL statement in the form's RecordSource property. if the
RecordSource property is blank, then the form is "unbound" - as in "not
bound to a recordset".

hth



Fred, we now can see the code -- don't know how we didn't run into it
earlier.

The SQL code looks understandable for the original simple query with no
drop down entry box.

So now if we could just get started with an UNBOUND form, we may start
making process. There seems to be nothing mentioning BOUND or UNBOUND
in all the form stuff we've seen so far.

Rob

fredg wrote:


On Wed, 19 May 2004 19:20:59 -0500, 1.156 wrote:





Fred, thanks again. That's a lot of stuff there. I'll see if I can
follow your instructions, and I'll let you know what happens.
Regards, Rob

fredg wrote:





On Wed, 19 May 2004 17:43:38 -0500, 1.156 wrote:







We've got a pretty useful database working but neither of us knows
Click to expand...
what

SQL means. We make queries using the graphics "design view". Please
let me ask if the following can be done:

In the query, the CRITERIA line reads :" Enter genome". The user has
Click to expand...
to

type in a genome and the query shows all the records with this genome
Click to expand...
in

the genome field. There is a table (not yet used) which has all the
genome possibilities. Naturally, some query users type in incorrect
genomes, not in the table. It sure would be nice to have a drop down
menu allowing the query user to select one of the genomes listed in
Click to expand...
the

genome table. Can this be done without writing SQL code?

Thanks for any help,






I hit the wrong tool button and inadvertently sent an incomplete
message.

1) When you use the QueryByExample ('graphics') grid to create a
query, Access writes the actual SQL code that really does the work. To
read the code, in Query Design View click on the Query View tool
button and select SQL.

2) You have the right idea to use the table to allow the user to
correctly select the genome wanted. Here's how. But first a question.
Does the table that contains the list of genomes have a Unique ID
field? It should. If so .....

You'll need to use a form to do this.

Make a new unbound form.
Add a combo box that will show the GenomeID field as well as the
GenomeText field.
Set the Combo Box Bound Column to 1
Set the Column Count to 2
Set the Column Widths property to 0";1"
Set the Auto Expand property to Yes.
Set the Limit to List property to Yes.
Name this combo box "cboSelector"

Add a command button to the form.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Add the GenomeID field to the query grid.
Code the Query GenomeID field criteria line:
forms!ParamForm!cboSelector
(Instead of your current [Enter Genome] criteria. Do NOT use the
brackets. Access will correctly provide them.)

When you wish to run the query, first open the form.
Find the wanted Genome name in the combo box.
Click the command button.

The query will display just those records selected and the form will
close.







I read your in-between post, about having just a Genome and Researcher
field in that table.

You can get by just fine using just the Genome (as Text) field.
Instead of having the GenomeID and Genome fields in the combo box,
just use the Genome field. In which case, set the Column Count
property to 1, and the Column Width property to 1". Everything else
should be OK.
Good luck.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top