Query that refers to a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a query with criteria that asks the user for a status. The
status will never change and when entering the data initially, the user will
choose from a drop down list for status. How can I create the query dialog
box such that they will again see the drop down list and will not have to
enter any "creative" text? I currently just have this in the query criteria
[Enter Status Change] but my fear is that they will not remember the list or
will mispell etc...thanks!
 
Hi,
You can create a form with a ComboBox that lists all the status so the user
can select the right status, and in the query you can refer to that combo in
the form

Select * From TableName Where FieldName = Forms![Formname]![ComboName]

In the criteria, instead of [Enter Status Change] write
Forms![Formname]![ComboName]

Also, if you want all the records to be return if no status was selected,
you can add another criteria to the query

Select * From TableName Where FieldName = Forms![Formname]![ComboName] Or
Forms![Formname]![ComboName] Is Null
 
Hi,

Thanks for responding. I am still having problems.
This is what I created in the subform...a combo box with the row source as
SELECT [Lookup Status].[Status Type] FROM [Lookup Status]; The Lookup Status
is my table that lists the status...so the combo box is called StatusChange.
I entered in the query criteria the following
Forms![Comments Subform]![StatusChange]
but the parameter value dialog box does not point to a list but simply puts
the criteria language in the parameter value dialog box at the top. What am I
doing wrong?


Ofer Cohen said:
Hi,
You can create a form with a ComboBox that lists all the status so the user
can select the right status, and in the query you can refer to that combo in
the form

Select * From TableName Where FieldName = Forms![Formname]![ComboName]

In the criteria, instead of [Enter Status Change] write
Forms![Formname]![ComboName]

Also, if you want all the records to be return if no status was selected,
you can add another criteria to the query

Select * From TableName Where FieldName = Forms![Formname]![ComboName] Or
Forms![Formname]![ComboName] Is Null

--
Good Luck
BS"D


Cheese said:
I have created a query with criteria that asks the user for a status. The
status will never change and when entering the data initially, the user will
choose from a drop down list for status. How can I create the query dialog
box such that they will again see the drop down list and will not have to
enter any "creative" text? I currently just have this in the query criteria
[Enter Status Change] but my fear is that they will not remember the list or
will mispell etc...thanks!
Click to expand...
Click to expand...
 
In the sub form the path is different

Forms![MainFormName]![SubformControlName].Form![StatusChange]

--
Good Luck
BS"D


Cheese said:
Hi,

Thanks for responding. I am still having problems.
This is what I created in the subform...a combo box with the row source as
SELECT [Lookup Status].[Status Type] FROM [Lookup Status]; The Lookup Status
is my table that lists the status...so the combo box is called StatusChange.
I entered in the query criteria the following
Forms![Comments Subform]![StatusChange]
but the parameter value dialog box does not point to a list but simply puts
the criteria language in the parameter value dialog box at the top. What am I
doing wrong?


Ofer Cohen said:
Hi,
You can create a form with a ComboBox that lists all the status so the user
can select the right status, and in the query you can refer to that combo in
the form

Select * From TableName Where FieldName = Forms![Formname]![ComboName]

In the criteria, instead of [Enter Status Change] write
Forms![Formname]![ComboName]

Also, if you want all the records to be return if no status was selected,
you can add another criteria to the query

Select * From TableName Where FieldName = Forms![Formname]![ComboName] Or
Forms![Formname]![ComboName] Is Null

--
Good Luck
BS"D


Cheese said:
I have created a query with criteria that asks the user for a status. The
status will never change and when entering the data initially, the user will
choose from a drop down list for status. How can I create the query dialog
box such that they will again see the drop down list and will not have to
enter any "creative" text? I currently just have this in the query criteria
[Enter Status Change] but my fear is that they will not remember the list or
will mispell etc...thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top