Unable to edit records in a form or query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form where the data is generated from a select query. I can
edit data in the tables that the query pulls from but not in my form or the
datasheet view of the query. I have narrowed the problem down to a
relationship problem but the MS help and online help doesnt quite tell me
enough. I believe that i need a one to one relationship, but when i add a
table in relationship window, I get an error about "enforce referintial
integrity" a rules violation. Can anyone help me figure out the next step to
take?
 
You will need to provide more information. Please describe what the database
is for, the contents of the two tables (key fields and anything relevant to
the question, but it is not necessary to list every field), and the
relationships. Have you established primary keys in the table? You're not
trying to establish a relationship between two primary key fields, are you?
Just guessing.
 
Bruce,
Thanks for the reply. I will do my best to describe it for you.

I am tracking resources for multiple projects. My primary table is
"PROJECT" it contains all project information, including the project number
and title. The query that i am running is pulling information form three
discipline tables "CSA", "ARCHITECTURAL" and "HVAC". The common field in all
of these tables is "PROJNO". The PROJNO field (data type =TEXT)in the
PROJECT table is the Primary Key Field. All other tables have unique
identifiers automatically generated as the Primary Key Field. For instance,
in the CSA table the key field is "CSAIND" (data type = autonumber). I hope
that this makes since.
 
A primary key (PK) is a unique identifier, no matter the data type.
Autonumber is one way of creating a unique identifier, that's all. Is there
a relationship between PROJNO in the Projects table and PROJNO in the other
tables? To put it another way, does each record in, say, the CSA table
relate to a specific record in the Projects table, or can each record in the
CSA table relate to many records in the Projects table. Is the CSA table
fairly static, or are records created on the fly? By way of example, an
Employees table is usually fairly static. If you are entering payroll
information you first need to create an employee record, but payroll
information is entered as you go, with a new entry (record) each week. I
understand a bit more about your database, but still do not understand the
relationships between the tables. A relationship is something you have to
deliberately create, so it is not a question of relationships being set up
without your knowledge. What relationships did you create?
 
Bruce,

The Project table has the PROJNO field in it. The relationship in my query
is to tie this field to the PROJNO field in the other tables (CSA,
ARCHITECTURAL, HVAC). The CSA table for instance has PROJNO, CSAbudget,
CSAexpend, CSAETC, CSAIFC...) This data is specific to the discipline CSA.
The only field in the CSA table that relates to the Project table is PROJNO.
CSA table has many records. Not all project will have CSA budgets and so
forth, but many do. In my form, the query should generate the current data
for each project and then I should be allowed to change certain fields, like
if it is active or not, who is the engineer. These fields are in the CSA
table but they are blank until I enter them manually, because they can
change. i hope that this helps.

Thanks for your help on this. I have spent days trying everything that I
can think of with no luck.

Sincerely,
Merlin
 
I don't know what CSA is, but I know what HVAC is, so I will use that as an
example of how a relationship between PROJNO in the projects table and PROJNO
in the HVAC table would work. Each project contains many cost items. For
HVAC the first record may be blowers (type, size, brand, etc.), the second
item may be ducts, the third item diffusers, and so forth. In other words,
each job would contain an itemized listing of HVAC expenses. If PROJNO in
the Projects table is 101, and if a one-to-many relationship is established
between Projects and HVAC, then when you enter a record in Projects you also
have the opportunity to enter related records in HVAC. You can see this in
the table (Access 2000 and above, I think) by the presence of a + sign next
to each record in Projects. Clicking the + sign will show you the fields in
HVAC. In actual use you would do this with a form and subform. You would
make a form based on Projects and another on HVAC. In form design view drag
the icon for the HVAC form onto the Projects form. When you enter a record
in the Projects table (via the form) you can enter related records (as many
as you need) in the HVAC table (via the subform). Each record created in
this way in the HVAC table will relate to one and only one record in the
Projects table.
If on the other hand the HVAC table contains options from which you must
choose, and if those options are available to other Projects as well, that is
another matter. For instance, if the HVAC table contains a listing of
components available to all projects, you do not want a one-to-many
relationship between the Projects table and the HVAC table. Or there could
be a third option, in which you select the item as described in the previous
paragraph, then enter quantity and current pricing, or something like that.
In either the second or third option you would need a separate table (let's
call it tblProjHVAC) to store either the primary key from the HVAC table, or
the actual values if they are subject to change (price information, for
instance).
One point here is that the CSA, ARCHITECTURAL, and HVAC tables (or
tblProjHVAC, tblProjCSA, and tblProjArch) all relate separately to the
Projects table, but do not relate to each other. For your purposes there is
no need to tie them together in a query. You may want to use queries for
other reasons such as sorting or criteria, but that is another matter.
 
Bruce,

I am copying the SQL for the Query and a table so that you may understand my
delima better. The form will show the all the information from the query. I
will want to edit the following cells: CSACOMMENTS, CSAIFC, CSAPROJCD,CSAENG
and CSADES. All other fields are generated from accounting and are manhours
budgeted and spent and estimated to complete. These should not be edited.
My Query:
SELECT DISTINCTROW CSA.CSAPROJCD, CSA.PROJNO, CSA.CSAENG, CSA.CSADES,
CSA.CSACOMMENTS, CSA.CSABUD, CSA.CSAETC, ARCHITECTURE.ARCHBUD,
ARCHITECTURE.ARCHETC, PROJECT.PROJTITLE, PROJECT.PROJMGR,
PROJCLIENT.CLIENTNAME, PROJCLIENT.PROJCLIENTMGR, CSA.CSAIFC, HVAC.HVACBUD,
HVAC.HVACETC
FROM (((PROJECT INNER JOIN CSA ON PROJECT.PROJNO = CSA.PROJNO) INNER JOIN
PROJCLIENT ON PROJECT.PROJNO = PROJCLIENT.PROJNO) INNER JOIN ARCHITECTURE ON
PROJECT.PROJNO = ARCHITECTURE.PROJNO) INNER JOIN HVAC ON PROJECT.PROJNO =
HVAC.PROJNO
WHERE (((CSA.CSAPROJCD)<>"X" Or (CSA.CSAPROJCD) Is Null) AND
((CSA.CSAETC)>0)) OR (((CSA.CSAPROJCD)<>"X" Or (CSA.CSAPROJCD) Is Null) AND
((ARCHITECTURE.ARCHETC)>0)) OR (((CSA.CSAPROJCD)<>"X") AND ((CSA.CSAIFC) Is
Not Null)) OR (((CSA.CSAPROJCD)<>"X") AND ((CSA.CSAENG) Is Not Null)) OR
(((CSA.CSAPROJCD)<>"X" Or (CSA.CSAPROJCD) Is Null) AND ((HVAC.HVACETC)>0))
ORDER BY CSA.CSAPROJCD, CSA.PROJNO;

CSA Table:
CSAIND.....Autonumber.....long Integer
PROJO......Text.................Indexed(duplicates ok)
CSAPROJCD..text..............Indexed (Duplicates ok)
CSAENG.......text..............
CSADES.......text
CSACHKR.....text
CSAISSUE....date/time
CSADWGTYP..text
CSACOMMENTS...text
CSAIFC.........date/time
CSABUD.......number
CSASTD.......number
CSAETC.......number
CSAINDF......number
CSAPER.......number

I hope that this will shed some light on what is going on.
 
I'm afraid you are going to have to start a new thread. I am not skilled at
evaluating SQL statements, which is why I asked about the structure and
purpose of the database. I can see where CSA, HVAC, and ARCHITECTURE could
each relate to the Projects table, but I don't understand why they need to be
combined into a query, nor how they relate to each other.
I still am inclined to guess that you need PROJNO as the foreign key in each
of those three tables, with a one-to-many relationship between PROJNO in the
Projects table and PROJNO in each of the other three tables. If you need to
sum the costs for CSA, HVAC, and ARCHITECTURAL for each project there are
probably better ways than a query of doing that.
There are plenty of people here who are familiar with SQL scripts, and
perhaps all of what I have asked is answered there. I outlined several
possible scenarios in my previous post. It appears that none are relevant,
but I don't know with what to replace them. Sorry I couldn't be more help.
 
Back
Top