Glen, I'm going to have to take a closer look at what you wrote.
But, just off the top, you indicate that you are no longer using the Vehicle
table, but using selection boxes for the vehicle. That is surely one of the
first places to look.
Wherever you have a query with a one-to-many relationship, look to see if
you are trying to update (or if Access may be 'fooled' into thinking so) a
field on the many side, but the key from the table on the many side is not
included in the query. That, and UNION queries, are common "oops" situations
for queries becoming unupdateable.
I'll look at this again in the next few days, and if I spot anything else,
I'll let you know. I'm, unfortunately, heavily scheduled for the rest of
today and tomorrow.
Larry Linson
Microsoft Office Access MVP
Glen, You will need to clarify... perhaps, with the application in front
of
you, what you wrote seems detailed and obvious, but you need to review
what
you wrote, focusing on the fact that what you wrote is all we have...
First, what version of Access? MDB, MDE, ADP, ADE, ACCDB, ACCDE? Split Jet
or ACE tables backend, ODBC link to server tables, direct connection to MS
SQL Server from ADP/ADE?
This summary only tells us that you have some kind of problem with
something, somewhere. You had something that worked, you made some sort of
changes, and now you get "sporadic operation" (what does that mean?).
This primary user form, when first opened is "without access to the
backend
tables" -- to what table/tables _does_ it have access? Where does the
"recordset" come from that is not "updateable"? When and how does your
application provide access to the backend tables?
This could mean a lot of different things... presumably you use a Query as
the Record Source of the Form. How you "go into it", what modification you
make, why you remove that modification, and why you "resave" the Query is
not clear.
Indeed! I don't even follow what you are describing, so could not make an
educated guess at what is going wrong. Without a good deal more specific
information, I doubt anyone can. It is difficult enough to debug remotely
with all the information; without it, remote debugging is impossible.
When you initially open the database, it will use the content of the Query
as saved in that Query object. Unless you go into design mode during
execution, you may change the instance of the Query being used but the
saved
Query will not be changed. When you close, then open the Database, the
original query is what is used. If you suspect that the _data_ in the
table
is being changed, and the records behind the form are not the most recent,
the Requery method will work, but it is superfluous if the database is
newly
opened.
In some versions of Access (Access 2.0, Access 97, and Access 2003), Help
is
reasonably clear in describing what can prevent a query from being
updateable. If not, visit Office Online (
http://office.microsoft.com) and
search for updateable, or, either Google or Bing with keywords Access or
Microsoft Access and updateable and query. There's a list of excellent
websites and other references, most created by current or former MVPs,
others by knowledgeable people, and some even by Microsoft,
athttp://sp.ntpcug.org/accesssig/default.asp.
"Updateable" really all boils down to Access being able to determine the
unique record to be updated. If there are mulitple tables joined to create
the Query, it may be as simple as including the key field of the table
being
updated to make it updateable.
With some more, clear, and detailed information it is possible that
someone
can be of assistance.
Larry Linson
Microsoft Office Access MVP
Larry,
Thanks for the response to this. I was trying not to write a book for
this problem so I didn't overwhelm someone trying to help. I'll try
to be more specific.
I am using Access 2007 and the front end has a .accdb extension. The
backend has a .mdb file extension.
All of the tables in the front end (5 total) are linked to the tables
in the backend which has ~30 tables.
The front end I am working with has 2 primary tables and 3 tables
which are used only as lookup tables.
The 3 lookup tables include:
1) Engineer Table with engineers names, contact info, engineering
group, etc. and has a primary ID for each engineer on the list used as
a key
2) Engineering Manager table with the names, contact info, etc. which
also has an ID established as the primary key
3) Vehicle table which identifies the vehicle information - this table
also has an ID established as the primary key but I am no longer using
this table and have created selection boxes for the associated
vehicles along with resolution dates for each one. These fields are
all part of tblStatusUpdate described below. (I know it isn't pretty
but it was the easiest way to capture this resolution date info as
multiple vehicles may be affected by an issue.)
The other 2 tables include the following info:
1) Test Incident Report Repository (TIR) Table: tblTIRRepository
brings in associated records based on the Test Report number and is a
1 to 1 to the StatusTable. For every record in tblTIRRepository there
is an associated status record. tblTIRRepository gives a description
of the event or issue found during testing of a vehicle.
2) Status Update Table: tblStatusUpdate is the table that is actually
updated by the engineers over time. As the issue is troubleshot,
corrected, and regression testing is performed, the engineers use the
primary form to update the status information for the incident.
A little more about tblStatusUpdate:
This table uses the primary keys from the other tables to keep stuff
straight. There is a field for each of the 3 lookup tables which is
used as a drop down on the form. The engineers and managers are
selected by last name and tblStatusUpdate holds the primary key (ID)
based on selection in the form. The vehicle is selected by vehicle
type and tblStatusUpdate holds the primary key (ID) based on vehicle
selection. This basically ties the issue discovered during testing to
the specific vehicle. tblStatusUpdate also has a field which holds a
description of the associated TIR from tblTIRRepository. There is
also a field in tblStatusUpdate which creates the relationship between
it and tblTIR as a 1 to 1.
About the form - frmStatusUpdate:
I do use qryStatusUpdate for the recordset for this form (SQL shown
below). This form holds almost all of the information found in
tblStatusUpdate. From the form, there is no way to update the lookup
tables or tblTIRRepository. Two fields are brought in from
tblTIRRepository for the engineers to reference. One of the fields is
the TIR description which is a large amount of text. This is shown on
the side of the form so the engineer can reference the original issue
as written at the test site. The other field is a string field for
the TIR identifier given to the issue at the test site. This
identifier is used to develop the relationship with tblStatusUpdate.
Neither of the fields are modified on tblTIRRepository from the form.
The form includes as its primary source of data, tblStatusUpdate.
There is some code running in the background of the form which forces
users to select an Update Command button to update the information in
the form. This button will take the latest status information and the
date of entry and append a statushistory field maintained in
tblStatusUpdate. The current status field is cleared, todays date is
entered as the update date, and the current status field is selected
as the focus. This button no longer works until the query is reset.
The code for this operation is:
UnlockRecords
Me.strStatusHistory.Value = Me.dtmStatusDate.Value & " - " &
Me.strStatusUpdate.Value & " ********* " &
Me.strStatusHistory.Value
Me.dtmStatusDate.Value = Date
Me.strStatusUpdate.Value = ""
About the operation of the front end:
When the user opens the engineering front end it is always mapped to
the TIR Backend. I have never needed to re-link the front end and the
backend at start up. In fact, the front end still pulls all of the
records into the form (~300 records at this point). All of the
information on the form is accurate but none of the information is
updateable anymore. This just happened before the Christmas Holiday
and I can't figure out why it has stopped working. When the front end
is opened, frmStatusUpdate is loaded and shows the records in the
database from the backend (there are only links in the front end - no
tables). When the user tries to make any change, nothing happens.
For example - the drop down for selecting the engineer will work (i.e.
it will show the list of engineers sorted by last name) but you cannot
change the selected engineer. If I go into the design view of the
form and take a look at the dataset query (which is a saved query
specific to this form) everything looks good. If I make a change to
the query (i.e. deselect a field in the query and then reselect the
same field) and resave/overwrite the query, the entire form works
properly allowing you to make all of the modifications without fail
until the front end is closed and reopened. When this action is
performed, all data changes are captured and appropriately saved in
the backend.
The SQL statement for the query is:
SELECT Modified_tblStatusUpdate.idxStatusUpdate,
Modified_tblStatusUpdate.dtmStatusDate,
Modified_tblStatusUpdate.strStatusUpdate,
Modified_tblStatusUpdate.str04TIR,
Modified_tblStatusUpdate.str03TestProj,
Modified_tblStatusUpdate.idxAssigneeKey,
Modified_tblStatusUpdate.idxTaskNumber,
Modified_tblStatusUpdate.strStatusHistory,
Modified_tblStatusUpdate.strCurrentStatus,
Modified_tblStatusUpdate.lkpResponsiblePDT,
Modified_tblStatusUpdate.lkpResponsibleEngr,
Modified_tblStatusUpdate.lkpAffectedVehicles,
tblTIRRepository.str90IncDescr, tblTIRRepository.str32Class,
Modified_tblStatusUpdate.str90DescInc,
Modified_tblStatusUpdate.dtmSuspenseDate,
Modified_tblStatusUpdate.lkpTestSite,
Modified_tblStatusUpdate.strPriorityCode,
Modified_tblStatusUpdate.dtmClosedDate,
Modified_tblStatusUpdate.SPH1_Affected,
Modified_tblStatusUpdate.SPH2_Affected,
Modified_tblStatusUpdate.SPH3_Affected,
Modified_tblStatusUpdate.SPH4_Affected,
Modified_tblStatusUpdate.SPH5_Affected,
Modified_tblStatusUpdate.CAT1_Affected,
Modified_tblStatusUpdate.CAT2_Affected,
Modified_tblStatusUpdate.SPH1_Sus_Date,
Modified_tblStatusUpdate.SPH2_Sus_Date,
Modified_tblStatusUpdate.SPH3_Sus_Date,
Modified_tblStatusUpdate.SPH4_Sus_Date,
Modified_tblStatusUpdate.SPH5_Sus_Date,
Modified_tblStatusUpdate.CAT1_Sus_Date,
Modified_tblStatusUpdate.CAT2_Sus_Date,
Modified_tblStatusUpdate.SPH1_Com_Date,
Modified_tblStatusUpdate.SPH2_Com_Date,
Modified_tblStatusUpdate.SPH3_Com_Date,
Modified_tblStatusUpdate.SPH4_Com_Date,
Modified_tblStatusUpdate.SPH5_Com_Date,
Modified_tblStatusUpdate.CAT1_Com_Date,
Modified_tblStatusUpdate.CAT2_Com_Date
FROM tblTIRRepository INNER JOIN Modified_tblStatusUpdate ON
tblTIRRepository.str04TIR = Modified_tblStatusUpdate.str04TIR;
