Non-updatable record set

[Peter Hallett]

A form is bound to Table_A but also needs to utilize fields from Table_B.
The two tables have no fields in common and, as a result, if both tables are
included in the form’s SQL statement, or (much the same thing) if the form is
bound to a query incorporating both tables, then no join can be established
between the tables and, as a result, even though the controls bound to
Table_B are hidden, and thus effectively read-only, data input to controls
bound to Table_A is rejected, with an error message declaring that a
non-updatable ‘snapshot-type record set’ has been established. The lack of
any connection between the two tables also precludes the use of form/sub-form
architecture.

Whist, after some reflection, that is understandable, it leaves a practical
difficulty. The form has to utilize data from Table_B. One solution is to
read the Table_B fields with a series of DLookups. That certainly works, and
avoids the non-updatable record set problem, but it is hardly elegant. With
a significant number of Table_B fields involved, it would be useful to be
able to read them with a single query but the original problem is then likely
to be re-encountered.

Clearly this is more a matter of style than substance but it would be
interesting to know whether less prolix solutions exists.

 

[NG]

Hi Peter,

Have you tried to create an object - class module for the values in table_B,
with a property procedure in the form you can then use the new class as a
form property to get the desired values from table_B on loading the form, or
whenever necessary.

 

[BruceM]

In what way does it need to use fields from Table_B? Since the tables are
unrelated it is difficult to picture what needs to be available from
Table_B. Perhaps you could supply some specifics about what the database
needs to do.

 

[Peter Hallett]

The following hopefully answers your question:–

Table_A contains details of a small fleet of aircraft, featuring such
information as registration numbers, types, ownership, fuel consumptions etc.
Also included are hours flown, hours left to inspection, due date for next
inspection and date of annual inspection etc. (Contrary to possible
expectations, these numbers are generally not closely related.) It is
necessary to identify aircraft that are approaching their hourly or
date-based expiry limits. Table_A is updated each time an aircraft is flown.

The system operator can choose the amount of notice he requires of the need
to withdraw individual aircraft from service as the limits are approached.
The alert margins on hours available, days left to inspection, and days left
to annual inspection are not aircraft-specific but set on a fleet-wide basis
and stored in Table_B.

When an aircraft completes its 50, 100 hour, or annual inspection, the date
and/or hourly limit to the next inspection are reset by default on the form
used to record its return to service. However, such dates and times almost
invariably need to be adjusted. Available hours often have to be reduced to
allow for delivery times and dates amended to those when the aircraft were
signed off by the maintenance facility rather than returned to the operator.
The latter is allowed to make such changes, and choose the margins, but
subject to narrow limits. These constraints are also stored in Table_B.

Each time the form is opened, it compares the current times and dates for
the aircraft selected from Table_A with the limits set in Table_B. Clearly,
the data in the two tables are unrelated. It would be poor design to
duplicate the global values of Table_B in each of the Table_A records, even
though it would overcome the non-updatable record set problem.

 

[Peter Hallett]

Thanks Noëlla,

I must apologize for not responding earlier. Whatever the impression, I was
not ignoring your contribution. Shortly after I replied to Bruce, there was
major trouble getting into the forum, which lasted several days. I am sure
that I have not been the only one affected. It has happened before but not
for so long. To add insult to injury, however, I then suffered a lengthy
power cut, with resulting equipment failure that knocked out my Internet
connection for more than 24 hours. I have only just succeeded in restoring
service. Evidently not my week. I had better get this off before anything
else happens.

If you have seen my reply to Bruce, maybe you would confirm whether you
think your suggested approach would work. If so, I would be grateful for a
bit of guidance. As I recently mentioned to another contributor, I learned
my programming in a ‘classless’ society but, as an old dog, I am still
willing to learn new tricks.

 

[BruceM]

I see I did not respond to your earlier reply to my first response. There
was a lot going on.

I think you need at least one more table. The Aircraft table shoudl be
attributes of an aircraft, which can include type, registration number,
service frequency, and so forth. It could include owner if there is no need
to maintain historical information about ownership. However, last service
date is no more an attribute of an aircraft than something like your last
eye exam is an attribute of you. Regarding the aircraft record, my comments
about last service date, etc. is particularly true if there is a need to
maintain historical information about repairs, service, and so forth.

There must be some connection between TableA and TableB, or the TableB
limits would have no connection to the TableA record. That is, there would
be no connection between, say, the type of aircraft and the service
frequency (or whatever exactly the TableB constraints are). If there is no
connection between the two tables then any record from TableB could
accompany any record from TableA.

I think this problem can be solved, but a little more information is needed
about the connection between the TableA records and the TableB constraints.
It could be that you need a main aircraft record, with related records about
service. This brings a third table for Service, related to TableA, into
play. Each TableA record can have several related Service records. In this
scenario you could populate the most recent service record with the
scheduled time for the next service. You could put together a query that
looks only for records with NextScheduledServiceDate within a month or two
weeks or whatever.

 

[Peter Hallett]

Thanks for the additional information, and request for further details,
Bruce. I am afraid, however, that I can’t add much to what I have already
said. There really is no connection between Table_A and Table_B. If I
illustrate the situation a little further, this may become clearer.

In order to plan for forthcoming outages, the aircraft operator needs to
know in advance as each of his aircraft approaches one of its several
statutory inspections, each requiring its withdrawal from service. He
therefore sets alert margins such that, on starting up the database each day,
he receives a report, or reports, to the effect that, “Aircraft G-ABCD is due
for its annual inspection in N days,†for example.

There are currently 5 different check dates/times, requiring 5 advance
warning, or post inspection adjustment, margins, designated N1 to N5, say.
They are:–
1. Days left to next interim inspection – N1.
2. Flying hours left to next interim inspection – N2
3. Days left to next annual inspection – N3
4. Allowable adjustment on days to next interim inspection, following reset
– N4
5. Allowable adjustment on days to next annual inspection, following reset –
N5

and are stored in Table_B. They apply equally to all the aircraft in the
fleet. In contrast to the dates and hours stored in Table_A, these margins
have no statutory significance, are solely for the benefit of the operator,
and are freely resettable at his discretion.

The majority of aircraft are subject to mandatory inspection every 6 months
or every 50 flying hours, whichever occurs first. In addition, they are
required to undergo an annual inspection, irrespective of the number of
flying hours or days then outstanding. The operator has currently chosen to
be alerted 7 days in advance of the interim inspection date, 10 hours prior
to the exhaustion of available flying hours and 14 days in advance of an
annual inspection. Therefore, N1 = 7, N2 = 10 and N3 = 14.

In order to display a form, or issue a report, notifying the imminent
non-availability of aircraft, that form, or report, must have access to
Table_A, which determines the remaining flying hours or days and is updated
on a daily and/or per-flight basis. It must also interrogate Table_B, for
the quasi-constant reporting triggers against which to compare the current
values of Table_A. The problem remains that, if the form or report is bound
to both tables, then a non-updateable record set results.

From what has so far been said, it does not seem that there is a simple
solution. My current strategy is to read Table_B with a series of DLookups.
It isn’t elegant, and could get worse. It is possible that further
non-synchronized inspections could become necessary for certain aircraft.
For example, those with variable pitch propellers may have to undergo
additional independent inspections associated with the latter. This would
require the use of further DLookups. One solution is to include the
reporting margins in each of the records of Table_A but this would represent
poor table design. Since the margins are global they would be repeated in
each record of Table_A and any changes would also have to be made identically
in the same way. The place for the margins is clearly in a separate,
amendable table with the ‘snapshot-type record set’ being the penalty if it
is included in an SQL statement with Table_A.