Forms - Subforms - Drop-Down Boxes

[Glencannon4424 via AccessMonster.com]

Hello,

My boss wants me to create a form which has a couple drop-down boxes on the
following columns: Name, Title, Start Date and End Date. I'm not sure if I
can have 4 drop downs in one form to start off with. That said, I'm having
some trouble getting the first drop-down, Name, to work.

Background: My form is running off a query. There are only 30 or so names in
my query but there are multiple instances of each name. The names in this
query will change frequently.

I don't know if this is the best way to do this but I've dropped all my
unique names in a table and created a subform off that table. But when I
actually run the form, only 1 name appears. In other instances (after I've
made some changes to the properties - can't remember which), all 30 names
appear but I can't select a name other than the 1st person on the list (it
says 'the record-set is not updateable').

What am I doing wrong? Any help is greatly appreciated . . .. . .

 

[Brian Bastl]

Comments in-line:

Hello,

My boss wants me to create a form which has a couple drop-down boxes on the
following columns: Name, Title, Start Date and End Date. I'm not sure if I
can have 4 drop downs in one form to start off with. That said, I'm having
some trouble getting the first drop-down, Name, to work.

4 combo boxes on one form presents no problem at all. Having a form,
control, or field named "Name" or any other reserved word will most
certainly cause problems. Change your reference to EmployeeName or
CustomerName or WhateverName.

Background: My form is running off a query. There are only 30 or so names in
my query but there are multiple instances of each name. The names in this
query will change frequently.

This is normal. I assume that the query is based on one table.

I don't know if this is the best way to do this but I've dropped all my
unique names in a table and created a subform off that table. But when I
actually run the form, only 1 name appears. In other instances (after I've
made some changes to the properties - can't remember which), all 30 names
appear but I can't select a name other than the 1st person on the list (it
says 'the record-set is not updateable').

No, this obviously isn't correct. What exactly are you trying to achieve?
What is the record source for the main form, i.e. the query's SQL? What are
the pertinent tables and corresponding fields?

Please post a reply with the necessary info.

Brian

 

[Glencannon4424 via AccessMonster.com]

The source query is based off 2 tables and a query. Here is the SQL code:

SELECT tblPW_OWED.NAME, tblPW_OWED.ID, tblPW_OWED.TITLE, tblPW_OWED.[PW-TITLE]
, tblDAYSOFWEEK.[PAY-WEEK], tblDAYSOFWEEK.YR, qryWTI_PAID_CALC.[WTI-PAY],
tblPW_OWED.[PW-OWED_RULE1], tblPW_OWED.[PW-OWED_RULE2], tblPW_OWED.[PW-
OWED_RULE3]
FROM (tblDAYSOFWEEK INNER JOIN qryWTI_PAID_CALC ON tblDAYSOFWEEK.[PAY-WEEK] =
qryWTI_PAID_CALC.[PAY-WEEK]) INNER JOIN tblPW_OWED ON (qryWTI_PAID_CALC.[PAY-
WEEK] = tblPW_OWED.[PAY-WEEK]) AND (qryWTI_PAID_CALC.ID = tblPW_OWED.ID)
GROUP BY tblPW_OWED.NAME, tblPW_OWED.ID, tblPW_OWED.TITLE, tblPW_OWED.[PW-
TITLE], tblDAYSOFWEEK.[PAY-WEEK], tblDAYSOFWEEK.YR, qryWTI_PAID_CALC.[WTI-PAY]
, tblPW_OWED.[PW-OWED_RULE1], tblPW_OWED.[PW-OWED_RULE2], tblPW_OWED.[PW-
OWED_RULE3];

Ultimately, I want a form with 4 drop-down boxes and 2 auto-populating fields.
The user will select a name from a drop-down. This name will autopopulate
the ID and Title. They will then select a PW-Title, Start Date and End Date
from 3 separate drop-downs. Based on these inputs, I would some data to
appear - in a form or in a report - I haven't really thought about that piece
of the puzzle yet. The data I want to appear in is my source query.

I'll change the "name" field as you suggested. I think I answered all your
questions. Thanks very much for your help.

 

[Brian Bastl]

Hi,

after re-reading your first post and this one, what I'm understanding is
that you are NOT trying to set up the form for data entry, but rather for
viewing the data from your query filtered by the combo boxes. If I'm wrong,
then post back with the table name(s) and fields of the table(s) you wish to
populate, because the Totals query you've posted most certainly will render
your form "read-only".

I have more questions than answers at this point.

1) Will the same [NAME] always have the same [TITLE]?
2) Will the same [NAME] always have the same [ID]?
3) Will the same [NAME] always have the same [PW-TITLE]?
4) Is the [NAME] field in tblPW_OWED derived from a value in another table,
perhaps a Customers table?
5) Same with [PW-TITLE]

Brian

The source query is based off 2 tables and a query. Here is the SQL code:

SELECT tblPW_OWED.NAME, tblPW_OWED.ID, tblPW_OWED.TITLE, tblPW_OWED.[PW-TITLE]
, tblDAYSOFWEEK.[PAY-WEEK], tblDAYSOFWEEK.YR, qryWTI_PAID_CALC.[WTI-PAY],
tblPW_OWED.[PW-OWED_RULE1], tblPW_OWED.[PW-OWED_RULE2], tblPW_OWED.[PW-
OWED_RULE3]
FROM (tblDAYSOFWEEK INNER JOIN qryWTI_PAID_CALC ON tblDAYSOFWEEK.[PAY-WEEK] =
qryWTI_PAID_CALC.[PAY-WEEK]) INNER JOIN tblPW_OWED ON (qryWTI_PAID_CALC.[PAY-
WEEK] = tblPW_OWED.[PAY-WEEK]) AND (qryWTI_PAID_CALC.ID = tblPW_OWED.ID)
GROUP BY tblPW_OWED.NAME, tblPW_OWED.ID, tblPW_OWED.TITLE, tblPW_OWED.[PW-
TITLE], tblDAYSOFWEEK.[PAY-WEEK], tblDAYSOFWEEK.YR, qryWTI_PAID_CALC.[WTI-PAY]
, tblPW_OWED.[PW-OWED_RULE1], tblPW_OWED.[PW-OWED_RULE2], tblPW_OWED.[PW-
OWED_RULE3];

Ultimately, I want a form with 4 drop-down boxes and 2 auto-populating fields.
The user will select a name from a drop-down. This name will autopopulate
the ID and Title. They will then select a PW-Title, Start Date and End Date
from 3 separate drop-downs. Based on these inputs, I would some data to
appear - in a form or in a report - I haven't really thought about that piece
of the puzzle yet. The data I want to appear in is my source query.

I'll change the "name" field as you suggested. I think I answered all your
questions. Thanks very much for your help.

Comments in-line:


4 combo boxes on one form presents no problem at all. Having a form,
control, or field named "Name" or any other reserved word will most
certainly cause problems. Change your reference to EmployeeName or
CustomerName or WhateverName.


This is normal. I assume that the query is based on one table.


No, this obviously isn't correct. What exactly are you trying to achieve?
What is the record source for the main form, i.e. the query's SQL? What are
the pertinent tables and corresponding fields?

Please post a reply with the necessary info.

Brian

 

[Glencannon4424 via AccessMonster.com]

Hi Brian,

I decided to re-structure my database instead. Thanks for your help!

Hi,

after re-reading your first post and this one, what I'm understanding is
that you are NOT trying to set up the form for data entry, but rather for
viewing the data from your query filtered by the combo boxes. If I'm wrong,
then post back with the table name(s) and fields of the table(s) you wish to
populate, because the Totals query you've posted most certainly will render
your form "read-only".

I have more questions than answers at this point.

1) Will the same [NAME] always have the same [TITLE]?
2) Will the same [NAME] always have the same [ID]?
3) Will the same [NAME] always have the same [PW-TITLE]?
4) Is the [NAME] field in tblPW_OWED derived from a value in another table,
perhaps a Customers table?
5) Same with [PW-TITLE]

Brian

The source query is based off 2 tables and a query. Here is the SQL code:

[quoted text clipped - 54 lines]

 

[Brian Bastl]

A very wise decision indeed!

Brian

Hi Brian,

I decided to re-structure my database instead. Thanks for your help!

Hi,

after re-reading your first post and this one, what I'm understanding is
that you are NOT trying to set up the form for data entry, but rather for
viewing the data from your query filtered by the combo boxes. If I'm wrong,
then post back with the table name(s) and fields of the table(s) you wish to
populate, because the Totals query you've posted most certainly will render
your form "read-only".

I have more questions than answers at this point.

1) Will the same [NAME] always have the same [TITLE]?
2) Will the same [NAME] always have the same [ID]?
3) Will the same [NAME] always have the same [PW-TITLE]?
4) Is the [NAME] field in tblPW_OWED derived from a value in another table,
perhaps a Customers table?
5) Same with [PW-TITLE]

Brian

The source query is based off 2 tables and a query. Here is the SQL code:

[quoted text clipped - 54 lines]

Brian