need help with form prompt

[George Applegate]

I have a form that I want a combo box on. The combo box should allow
the user to select employees for a selected location, and only those
employees.

The location comes from a "prompt" in the query that says ['enter
location'].

Now this is the source for the combo box as I've written it, which
doesn't work:

SELECT Employee.EmployeeID, Employee.LastName, Employee.FirstName,
Employee.Department FROM Employee WHERE
(((Employee.Department)=(location entry form by select
location.department));

the WHERE is what I'm having trouble with. Do I need some single
quotes around 'location entry form by select location.department' or
should that not be the form name (location entry form by selection
location) but instead, the name of the query?

When the user opens the form, I am prompting for the location they are
working with. But the form is using a query, so maybe I should be
referencing the query?

Make a long story short, I want to prompt for a specific location
(which is "department"), but then in the employee combo box, I only
want to display employees tied to that location.

I hope this makes sense. I'm struggling with this and would like to
get it working.

Thanks for any suggestions, ideas or recommendations!
ga

George Applegate
(e-mail address removed)

 

[John W. Vinson]

I have a form that I want a combo box on. The combo box should allow
the user to select employees for a selected location, and only those
employees.

The location comes from a "prompt" in the query that says ['enter
location'].

Now this is the source for the combo box as I've written it, which
doesn't work:

SELECT Employee.EmployeeID, Employee.LastName, Employee.FirstName,
Employee.Department FROM Employee WHERE
(((Employee.Department)=(location entry form by select
location.department));

the WHERE is what I'm having trouble with. Do I need some single
quotes around 'location entry form by select location.department' or
should that not be the form name (location entry form by selection
location) but instead, the name of the query?

Whenever you (unwisely, IMO) use a blank or other special character in the
name of something ( a form, a control, a table name, a fieldname) then you
must enclose it in [square brackets] to tell Access that it's one thing,
rather than something named "location" and something else named "entry" and
something else named "form". You also need to specifically reference the
Forms! collection to tell Access you're referring to a Form rather than some
other sort of object.

Try

SELECT Employee.EmployeeID, Employee.LastName, Employee.FirstName,
Employee.Department FROM Employee WHERE
(((Employee.Department)=([Forms]![location entry form by select
location]![department]));

When the user opens the form, I am prompting for the location they are
working with. But the form is using a query, so maybe I should be
referencing the query?

I have no idea what you mean. Are you talking about having the query reference
itself!?

Make a long story short, I want to prompt for a specific location
(which is "department"), but then in the employee combo box, I only
want to display employees tied to that location.

You will need to use the query above, *and* requery the Employee combo box in
the AfterUpdate event of the Department combo box.

 

[George Applegate]

John,

I appreciate your advice and if you wouldn't mind, would like to ask a
couple of follow-up questions.

First off, I created a query and in my query I have a prompt that says
- ['enter location'] for department. Then I created a from using
automatic create form from a query. Maybe I shouldn't have done it
this way.

Then, I changed the employee id to be a "combo box" on the form.
There is also an input box for department (which is location).

So...when I open the form, the first thing I get is my prompt to
['enter location'], I assume because it's running my query.

Okay, if I enter department (or location) "02", I want to then have my
combo box only list employees wth a department assignment of '02'.

When I open the form it does the prompting for ['enter location'] and
when I enter "02", that's exactly what is in the box. But
unfortunately, I am not getting any employees to show up. So, do I
need to code something different in the query itself or in the form.
I coded it as you suggested with the language you suggested and put
that in the "after update" event of the department box.

Do I need anything in the combo box source pertaining to a query or
anything?

I have probably lost you because I'm probably going about this all the
wrong way. I created the query first, and then created the form from
the query. It seems to work, prompts for the location (department),
but like I said, I'm struggling with then how to get my employee list
in the employee id combo box to contain only (or any) employees for
that department only.

Location and department are the same thing - in the table it's defined
as department, but my users think of "location". I am using
DEPARTMENT in the queies.

Thanks again for any suggestions you could make for me
ga

I have a form that I want a combo box on. The combo box should allow
the user to select employees for a selected location, and only those
employees.

The location comes from a "prompt" in the query that says ['enter
location'].

Now this is the source for the combo box as I've written it, which
doesn't work:

SELECT Employee.EmployeeID, Employee.LastName, Employee.FirstName,
Employee.Department FROM Employee WHERE
(((Employee.Department)=(location entry form by select
location.department));

the WHERE is what I'm having trouble with. Do I need some single
quotes around 'location entry form by select location.department' or
should that not be the form name (location entry form by selection
location) but instead, the name of the query?

Whenever you (unwisely, IMO) use a blank or other special character in the
name of something ( a form, a control, a table name, a fieldname) then you
must enclose it in [square brackets] to tell Access that it's one thing,
rather than something named "location" and something else named "entry" and
something else named "form". You also need to specifically reference the
Forms! collection to tell Access you're referring to a Form rather than some
other sort of object.

Try

SELECT Employee.EmployeeID, Employee.LastName, Employee.FirstName,
Employee.Department FROM Employee WHERE
(((Employee.Department)=([Forms]![location entry form by select
location]![department]));

When the user opens the form, I am prompting for the location they are
working with. But the form is using a query, so maybe I should be
referencing the query?

I have no idea what you mean. Are you talking about having the query reference
itself!?

Make a long story short, I want to prompt for a specific location
(which is "department"), but then in the employee combo box, I only
want to display employees tied to that location.

You will need to use the query above, *and* requery the Employee combo box in
the AfterUpdate event of the Department combo box.

George Applegate
(e-mail address removed)