List in the parameter box.

The key in tblItemsForAssignment is NOT an AutoNumber - it is text (because
it can be a combination of letters and numbers). What I don't understand is
what a corresponding/compatible type would be... the Program field is
currently TEXT

"Jeff Boyce" wrote:

Keri

Change the data type of the Program field in tblItemsForAssignment to a
?LongInt (this assumes your primary key in tblPrograms is an Autonumber - if
not, change to a corresponding/compatible type). Now your tblItemsFor...
table displays what it holds.

Create a query that joins tblItemsFor ... and tblPrograms on this field.
The value you are looking for is the key value.

In your form, build a combo box based on the tblProgram ID field, plus the
ProgramName (or whatever you've called it). Make the first column (the ID)
of zero-width in the properties -- this displays the ProgramName first in
the combo box. When you've picked a Program, the combo box's value is the
first column (i.e., the ID).

Now have your query look to the form, to the combo box, for the ID value it
needs.

--
Good luck

Jeff Boyce
Access MVP

"Keri" wrote in message
...
You are good!
Yes, the field in question is defined as a lookup data type. There are two
tables that use the Program Name field. One table (tblPrograms) stores
the
list of the valid Program Names. The other table (tblItemsForAssignment)
stores the projects related to the programs, and has a Program Name field
to
relate a project to a specific Program. To ensure consistency in the
Program
names (for reporting, etc.), I have the Program Name field in
tblItemsForAssignment as a lookup field, pointing to the Program Name
field
in tblPrograms.

I thought of having the query point to the Program Name field tblPrograms
instead of tblItemsForAssignment. But when I try this, and enter
Forms![PROGRAM]![Item Number] in the Criteria field, MS Access changes it
to
[Forms]![PROGRAM]![Item Number] and then displays a dialog box with
"Forms!PROGRAM!Item Number" and a text input box...

Hope that's enough info...

"Jeff Boyce" wrote:

Keri

Any chance the field in question has been defined as a "lookup" data
type in
the table? If so, you've run afoul of an issue often discussed in
tablesdbdesign. That is, lookup data types store one value but display
something else.

If you are NOT working with a lookup field, then the next most likely
culprit is a spelling error. You didn't provide any information about
how
it doesn't work, so it's a bit tough trying to guess what might be
wrong/not
working...

--
More info, please ...

Jeff Boyce
Access MVP

"keri" wrote in message
...
I tried what you show below, but can't get it to work.
I have a query that currently has
[Enter Program Name]
in the Criteria field for a field called Program Name
Instead of having a free form text field, I want to use a list box so
that
users can pick from the list. The name of the form is PROGRAM. on
that
form, I have a combo box that shows the valid values and a command
button
to
run the query...

I tried entering what you said all of these ways:
Forms![PROGRAM]
[Forms]![PROGRAM]
Forms![PROGRAM]![Item Number]

and nothing will work. I get syntax errors, etc. and can't get the
PROGRAM
form to display... Help please!!!

"Jeff Boyce" wrote:

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query
the
parameter. Add a combo box to the form, based on the source of your
list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the
sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box
selection made for this to work)
select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo
box
and
looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
Access MVP

"ronoee" wrote in message
...
Is ther in the query in Access possible to create a parameter that
present
you a list in the parameter box popping up, so you don’t have to
write
the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then
please
help
me.

Text is a type compatible with Text. Both need to be the same length.


--
Good luck

Jeff Boyce
Access MVP
"Keri" wrote in message
...
The key in tblItemsForAssignment is NOT an AutoNumber - it is text
(because
it can be a combination of letters and numbers). What I don't understand
is
what a corresponding/compatible type would be... the Program field is
currently TEXT

"Jeff Boyce" wrote:

Keri

Change the data type of the Program field in tblItemsForAssignment to a
?LongInt (this assumes your primary key in tblPrograms is an
Autonumber - if
not, change to a corresponding/compatible type). Now your
tblItemsFor...
table displays what it holds.

Create a query that joins tblItemsFor ... and tblPrograms on this field.
The value you are looking for is the key value.

In your form, build a combo box based on the tblProgram ID field, plus
the
ProgramName (or whatever you've called it). Make the first column (the
ID)
of zero-width in the properties -- this displays the ProgramName first
in
the combo box. When you've picked a Program, the combo box's value is
the
first column (i.e., the ID).

Now have your query look to the form, to the combo box, for the ID value
it
needs.

--
Good luck

Jeff Boyce
Access MVP

"Keri" wrote in message
...
You are good!
Yes, the field in question is defined as a lookup data type. There are
two
tables that use the Program Name field. One table (tblPrograms)
stores
the
list of the valid Program Names. The other table
(tblItemsForAssignment)
stores the projects related to the programs, and has a Program Name
field
to
relate a project to a specific Program. To ensure consistency in the
Program
names (for reporting, etc.), I have the Program Name field in
tblItemsForAssignment as a lookup field, pointing to the Program Name
field
in tblPrograms.

I thought of having the query point to the Program Name field
tblPrograms
instead of tblItemsForAssignment. But when I try this, and enter
Forms![PROGRAM]![Item Number] in the Criteria field, MS Access changes
it
to
[Forms]![PROGRAM]![Item Number] and then displays a dialog box with
"Forms!PROGRAM!Item Number" and a text input box...

Hope that's enough info...

"Jeff Boyce" wrote:

Keri

Any chance the field in question has been defined as a "lookup" data
type in
the table? If so, you've run afoul of an issue often discussed in
tablesdbdesign. That is, lookup data types store one value but
display
something else.

If you are NOT working with a lookup field, then the next most
likely
culprit is a spelling error. You didn't provide any information
about
how
it doesn't work, so it's a bit tough trying to guess what might be
wrong/not
working...

--
More info, please ...

Jeff Boyce
Access MVP

"keri" wrote in message
...
I tried what you show below, but can't get it to work.
I have a query that currently has
[Enter Program Name]
in the Criteria field for a field called Program Name
Instead of having a free form text field, I want to use a list box
so
that
users can pick from the list. The name of the form is PROGRAM.
on
that
form, I have a combo box that shows the valid values and a command
button
to
run the query...

I tried entering what you said all of these ways:
Forms![PROGRAM]
[Forms]![PROGRAM]
Forms![PROGRAM]![Item Number]

and nothing will work. I get syntax errors, etc. and can't get
the
PROGRAM
form to display... Help please!!!

"Jeff Boyce" wrote:

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the
query
the
parameter. Add a combo box to the form, based on the source of
your
list.
Modify your query to use that form, that combo box as its
parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's
the
sequence
of how it would work:

open the form (note: the form MUST be opened and the combo
box
selection made for this to work)
select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's
combo
box
and
looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
Access MVP

"ronoee" wrote in message
...
Is ther in the query in Access possible to create a parameter
that
present
you a list in the parameter box popping up, so you don’t have
to
write
the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you
then
please
help
me.