Combo Box

From an earlier question answered by Fred.

His instruction Select Distinct Tablename.[FieldName] From TableName Order
By TableName.[FieldName]

would be written as Select Distinct Main.[Priority] From Main Order By
Main.[Priority] if I read this correctly. In the table Main, the Lookup is
entered as Text and on the form I want a combo box.

First, is the syntax written properly based on Fred's structure above?
Second, does the table Main Lookup need to be a combo box as well?
Third, is this SQL statement listed in the form row source property , or
does the table Main Lookup get the SQL statement, or does it go in both spots?

A lot of questions, but I am confused on this because no data gets returned
when I enter this in the form's row source property. My combo box is blank
when I put in a new entry and I have seven records with three that duplicate.

I just want to cut down on queries and thought there might be a way to get
that done without the row source in the form based on a query. That was my
original question Fred answered, but it is no longer listed so I could not
follow up his response.

On Tue, 2 Feb 2010 13:56:03 -0800, Kyhigh
wrote:

From an earlier question answered by Fred.

His instruction Select Distinct Tablename.[FieldName] From TableName Order
By TableName.[FieldName]

would be written as Select Distinct Main.[Priority] From Main Order By
Main.[Priority] if I read this correctly. In the table Main, the Lookup is
entered as Text and on the form I want a combo box.

First, is the syntax written properly based on Fred's structure above?
Second, does the table Main Lookup need to be a combo box as well?
Third, is this SQL statement listed in the form row source property , or
does the table Main Lookup get the SQL statement, or does it go in both spots?

A lot of questions, but I am confused on this because no data gets returned
when I enter this in the form's row source property. My combo box is blank
when I put in a new entry and I have seven records with three that duplicate.

I just want to cut down on queries and thought there might be a way to get
that done without the row source in the form based on a query. That was my
original question Fred answered, but it is no longer listed so I could not
follow up his response.

Is Priority a grrr Lookup Field? That is, is it displayed as a combo box
when you open table Main?

Most of us are vehemently opposed to Lookup Fields in tables: see
http://www.mvps.org/access/lookupfields.htm for a critique.

And DON'T be afraid of queries. They are the lifeblood of any Access app; and
there is no good reason to minimize the number of queries just to minimize the
number of queries! That said, you can use a SQL statement as the direct
RowSource of a combo box, without having a (visible) stored query (Access will
store the query for you anyway, it just won't be visible on the Queries tab).

--

John W. Vinson [MVP]

Kyhigh -

Your syntax is correct. Where you put the SQL depends on where you want to
use it. If you make this SQL the recordsource for the combo box, then the
combo box will get a list of all the Priority values from the Main table. If
you use it as the form's recordsource, the the form will only display the
Priority values from the Main table.

Normally, a form would contain several fields of data based on a table or
query. You can restrict which records are shown on a form by using a filter
or changing the recordsource of the form. The recordsource of the form may
be based on the selected value of an unbound combo box, which is what I
suspect you want. If that is the case, and you want to restrict which
records you show on the form to be those with the Priority from the combo
box, then put the SQL you have in the recordsource of the combo box. Then
you will look at the recordsource of the form, and include whatever fields
from whatever table/query you want, but add in the criteria to restrict the
records to those with the priority selected in the combo box.

--
Daryl S


"Kyhigh" wrote:

From an earlier question answered by Fred.

His instruction Select Distinct Tablename.[FieldName] From TableName Order
By TableName.[FieldName]

would be written as Select Distinct Main.[Priority] From Main Order By
Main.[Priority] if I read this correctly. In the table Main, the Lookup is
entered as Text and on the form I want a combo box.

First, is the syntax written properly based on Fred's structure above?
Second, does the table Main Lookup need to be a combo box as well?
Third, is this SQL statement listed in the form row source property , or
does the table Main Lookup get the SQL statement, or does it go in both spots?

A lot of questions, but I am confused on this because no data gets returned
when I enter this in the form's row source property. My combo box is blank
when I put in a new entry and I have seven records with three that duplicate.

I just want to cut down on queries and thought there might be a way to get
that done without the row source in the form based on a query. That was my
original question Fred answered, but it is no longer listed so I could not
follow up his response.