Can't enter data in a form based on a query

[Guest]

I've created a form based on a query, and cannot enter data into the
controls-- was setting up a combo box with staff names when I discovered
this. I believe I have a relationship issue, because when I eliminate fields
in the query that are from other tables, I am able to enter data in the form.
Here's the setup.

Controls on form from Main Table (these are the ones in question)
TicketNum (autonumber using DMax+1) Primary Key -- data type: number
ProgramArea (combo box) -- number -- bound to ProgramArea field (number) in
main table
Record source tblProgramArea
Fields: ProgramID -- number
Program -- text
Category (cascading combo -- filters on selection in ProgramArea) -- number
-- bound to Category field in main table -- number
Record source tblCategoryDetails
Fields: CategoryID and ProgramID -- number --
Category -- text
Description (cascading combo -- filters on selections in ProgramArea &
Category)--number -- bound to Description field in main table -- number
Record source tblDescriptionsDetails
Fields: DescriptionID, CategoryID, ProgramID -- number
Description -- text

I created the query to obtain the text values for selections in the combo
boxes. So, I have all the fields from the main table, plus the text fields
from each table that is a combo box record source. I created a relationship
between the IDs in the three tables (ProgramID, CategoryID, DescriptionID)
and the fields Program, Category and Decription in the main table.

Now, when I design a form based on this query, I'm unable to enter any data.
Can anyone advise me?

 

[Ken Snell [MVP]]

A query must be updatable for you to use it as the recordsource of a form
that will be used for data entry.

There are many possible reasons why a query can be nonupdatable. See these
MS Knowledge Base articles for more info:

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


For your situation, likely the cause is because of the way you're joining
the tables, but you'll need to give us more info about the table structures
and the query that you're trying to use (such as the SQL statement of that
query).

 

[Guest]

Thank you, Ken, for the resources. I've looked at them.

It looks as if my problem may be that the join field (DescriptionsID) is a
one-to-many relationship. I thought I had described the table structure as
to fields and primary keys. But here's some additional info.

The second and third tables for the combo boxes (CategoryDetails) and
(DescriptionsDetails) do not have primary keys. I tried making
ProgramID&CategoryID a primary key for the CategoryDetails table and the same
for all "ID" fields in the DescriptionDetails table, that that didn't result
in a change.

Here's the SQL of the query (I eliminated some fields to make it a bit
easier to read.) Perhaps this will give more clues to the problem. (I don't
get an error message. The computer just beeps when I try to make a selection
on the form or to type in a control.)

SELECT tbl_Service_Requests.TicketNum, tbl_Service_Requests.ReceivedDate,
tbl_Service_Requests.Initiator, tbl_Service_Requests.State,
tbl_Descriptions_Detail.Description, tbl_Service_Requests.Description
FROM tbl_Descriptions_Detail INNER JOIN tbl_Service_Requests ON
tbl_Descriptions_Detail.DescriptionID=tbl_Service_Requests.Description
WHERE (((tbl_Service_Requests.Status)="Open"));

 

[Guest]

Oops! At second look at the Descriptions table, the DescriptionID is not
unique! Its the combination of ProgramID&CategoryID&DescriptionID that is
unique. Here's an example of data from that table, along with a "translation"
of the IDs when all the text values are assembled.
ProgramID CategoryID DescriptionID Description
1 1 1 Current Usage Inquiry
3 1 1 Current Usage Inquiry
4 1 2 Policy Issue

These values would be translated to:
Program Category Description
CSENet Business Guidance Current Usage Inquiry
DFAS Busubess Guidance Current Usage Inquiry
ICR Business Guidance Policy Issue

Hope this helps to clarify.

 

[Ken Snell [MVP]]

Not sure if I totally understand, but you must include in your query all
three fields that together comprise the primary key. That is how the query
will be able to identify a unique record. Try that change to your query.

I also note in the SQL statement that you posted this clause:

ON
tbl_Descriptions_Detail.DescriptionID=tbl_Service_Requests.Description

Should the tbl_Service_Requests.Description be
tbl_Service_Requests.DescriptionID ?

 

[Guest]

Ken: That did the trick! I did not have all three "primary keys" in the
table, and although the correct value for "Description" showed in the query,
somehow, data entry was prohibited. I also noted your question about the
query. I had called the field in the main table "Description", rather than
"DescriptionID," which was more accurate. I changed the name to DescriptionID
so the field names matched. This may have been another source of the problem.

At any rate, your advice surely got me out a a hole, and I'm very grateful.
Thanks again.

 

[Ken Snell [MVP]]

Glad it's fixed. Note that there is only one primary key in a table --
however, the primary key can be composed (as it is in your case) of more
than one field, when it's called a composite primary key. So what you needed
to do was include all the fields that comprise the primary key.

RE: the change of Description to DescriptionID, that isn't really necessary
and shouldn't have been any source of problem. I called it to your attention
only to be sure that you were joining the correct field and was not a
mistake. Sometimes, it's easy to "join" to the wrong field!

Good luck.

 

[Guest]

Thank you so much, Ken. Am now working on another challenge that I may have
to post. You folks who volunteer your time and expertise to those of us
newbies are really wonderful.

 

[Ken Snell [MVP]]

Thank you so much, Ken. Am now working on another challenge that I may have
to post. You folks who volunteer your time and expertise to those of us
newbies are really wonderful.

< blushing on behalf of all of us ! >

Thank you.