Full Version: Need Help To Determine Why Query Is Read-only
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
LAM
Apr 24 2012, 03:29 PM
The query I am having trouble with is based on a contracts table.
The table has several fields that are foreign keys on different tables.

Table Fields
ID = AutoNumber (Primary Key)
CID = Contract ID
EMPID_Sales = Initials of Sales person (Foreign Key Employee table)
EMPID_Support = Initials of Support person (Foreign Key Employee table)
C_Company = Company Name (Foreign Key Account table)
C_ContTermID = Term of Contract (Foreign Key ContTerm table)
C_FeeType = Type of Fee (Foreign Key FeeType table)
C_ContType = (Foreign Key ContType table)
C_Category = Category (Foreign Key AcctCategory table)
C_StartDate
C_EndDate
Etc.

Here is the query.
CODE
SELECT Contracts.ID
, Contracts.CID
, Employee.EMPID
, Employee_1.EMPID
, [Account Master].[Account Name]
, Contracts.C_StartDate
, Contracts.C_EndDate
, Contracts.C_EffDate
, ContTerm.ContTerm
, FeeType.FT_FeeType
, Contracts.C_SSPercent
, Contracts.C_Rate
, Contracts.C_Value
, ContType.CT_Type
, Contracts.C_Premium
, Contracts.C_Webinar
, AcctCategory.Category
, Contracts.C_Referral
, Contracts.C_Notes
FROM ((((((Contracts LEFT JOIN Employee ON Contracts.EMPID_Sales = Employee.EMPID)
LEFT JOIN Employee AS Employee_1 ON Contracts.EMPID_Support = Employee_1.EMPID)
LEFT JOIN [Account Master] ON Contracts.C_Company = [Account Master].ID)
LEFT JOIN ContTerm ON Contracts.C_ContTermID = ContTerm.ContTermID)
LEFT JOIN FeeType ON Contracts.C_FeeType = FeeType.FTID)
LEFT JOIN ContType ON Contracts.C_ContType = ContType.CT_Type)
LEFT JOIN AcctCategory ON Contracts.C_Category = AcctCategory.Category;


The query will be the data source for a form used to edit, manage and add new contracts. I suspect it is the joins that is causing it to be read-only. But if I do not have the joins then the controls on the form will show the ID# or primary key field data for those controls instead of the information I want to display. So instead of ABC Company it will show a number like 1902.
How do I design the query so that it is not read-only?

Thanks in advance for your help.

LAM
JimBurke
Apr 24 2012, 04:10 PM
It sounds like all the joins are basically for table lookups, where there's an ID in the 'main' table and some related descriptions in the joined tables. What I've done
in this situation is to have the form bound to the main table, and then for each of those fields that have an ID and a related value in another table, use a combobox
for the value and have the combobox rowsource as a query that selects all the necessary values from the related table. Each of those comboboxes would be bound to the ID field
in the main table. At a minimum for each combobox query you would need (from the related table) the ID field from that table and the description, with the ID field hidden so that the user only sees
the description. Hope this makes sense. Oh, and yes, I believe you're right about it not being updatable because of the left joins.
LAM
Apr 24 2012, 04:12 PM
Thanks Jim,

I will give that a try and let you know how it goes.

Lori
LAM
May 7 2012, 10:39 AM
It took a while for me to get back to creating the form, but all seems to be working.

Thanks again for the help.

Lori
JimBurke
May 9 2012, 03:37 PM
Glad it worked!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.