Error: You can't go to the specific record.

[Guest]

Upon returning from vacation, I find that the database had crashed. I am
unable to add any new record. I get the error: "You Can't go to the specific
record." I am assuming that someone attempted to add a record and did not
complete same, before going onto the next. Question, how do I fix this?
Since I was gone for 2 weeks, the backup with the good database has been
overwritten several times with the crashed database, so I am unable to
restore the data base that way. I have looked thru the tables, and do not
find any incomplete records. I need any help that can be given. Thanks!

 

[John W. Vinson]

Upon returning from vacation, I find that the database had crashed. I am
unable to add any new record. I get the error: "You Can't go to the specific
record." I am assuming that someone attempted to add a record and did not
complete same, before going onto the next. Question, how do I fix this?
Since I was gone for 2 weeks, the backup with the good database has been
overwritten several times with the crashed database, so I am unable to
restore the data base that way. I have looked thru the tables, and do not
find any incomplete records. I need any help that can be given. Thanks!

Well, the first thing to do is to make sure you have as good a backup as
possible - back up the current database if need be.

Then try Tools... Database Utilities... Compact and Repair. Does that change
anything?

Secondly, are you getting this message only in a Form, or do you get the same
error when you try to insert a record directly into a table? If the error is
on the form, my guess is that there's nothing wrong with your data, but rather
with the design of the form itself. What's the Form's Recordsource?

John W. Vinson [MVP]

 

[Guest]

I can add records to the underlying tables. I can't to the form. Also, the
underlying query which the form is based upon will not allow additions. I
have found that by eliminating one of the tables from the query, the query is
then updateable. In other queries where this same table is used, those
queries also are not updateable unless I remove that particular table. But
the same table is updateable when not linked to any other table. Hope I am
making sense!

Let me give you a little insight on the data base. It records well and
septic permits for a health dept. The main table which all is based upon is
"address". This is the table that is throwing the monkey wrench into
everything.

Any help you can give is appreciated! thanks

 

[John W. Vinson]

I can add records to the underlying tables. I can't to the form. Also, the
underlying query which the form is based upon will not allow additions.

That's why the form is not updateable. The form is just a window, a tool to
edit data in the query. If the query can't be updated, then the form can't be
either.

I have found that by eliminating one of the tables from the query, the query is
then updateable. In other queries where this same table is used, those
queries also are not updateable unless I remove that particular table. But
the same table is updateable when not linked to any other table. Hope I am
making sense!

Please post the SQL view of the query - open the query and select View... SQL
from the menu, and copy and paste the SQL to a message here. Also indicate the
Primary Key of each table and any relationships.

Note that it is *rarely* necessary to build a Form on a multitable query. You
can use a Form for the "one" side of the relationship and a Subform for the
"many", and you can use combo boxes or other tools for "lookup" tables.

John W. Vinson [MVP]

 

[Guest]

Here is the sql:
SELECT AddressLocation.AddressLocationID, AddressLocation.TownshipID,
AddressLocation.SubdivisionID, AddressLocation.[Parcel#], Septic.ClientID,
Septic.ContractorID, Septic.SepticIssueDate, Septic.OccupancyType,
Septic.Occupancy, Septic.SystemType, Septic.FieldType, Septic.[Field Size],
Septic.MaxTrenchDept, Septic.DesignFlow, Septic.LoadingRate, Septic.TankSize,
Septic.GarbageDisposal, Septic.SoilType, Septic.SoilCategory,
Septic.Variance, Septic.Comments, Septic.CurtainDrain, Septic.Layout,
Septic.SepticID, Septic.AddressLocationID, Septic.PermitPurpose,
Septic.SepticPermitNo, Septic.[Distance to Well], Septic.RepairDetails,
Legal.[Township#], Legal.[Range#], Legal.[Section#], Legal.[Lot#],
Legal.Qtr3, Legal.LegalID, Inspections.InspectionDate,
Inspections.InspectorID, Inspections.InspectionID, Inspections.Comments,
Septic.Status, Septic.ExpirationDate
FROM ((AddressLocation RIGHT JOIN Septic ON
AddressLocation.AddressLocationID = Septic.AddressLocationID) LEFT JOIN Legal
ON AddressLocation.LegalID = Legal.LegalID) LEFT JOIN Inspections ON
Septic.SepticID = Inspections.SepticID
ORDER BY Septic.SepticPermitNo DESC;


Primary key of the AddressLocation Table is: AddressLocationID
Primary key of the Septic Table is SepticID
Primary key of the Legal TAble is LegalID
Primary key of the Inspection Table is InspectionID

AddressLocation table has a relationship to Legal as 1 to many
AddressLocation table has a relationship to septic as 1 to many
Septic table has a relationship to Inspection as 1 to many

Anxiously awaiting any insight and help you can give as it is GREATLY
appreciated!

 

[John W. Vinson]

AddressLocation table has a relationship to Legal as 1 to many
AddressLocation table has a relationship to septic as 1 to many
Septic table has a relationship to Inspection as 1 to many

It sounds like there is no relationship between Legal and Septic; if there are
multiple records in both these tables for an AddressLocation you'll get all
possible combinations *and* the query will not be updateable.

I'd really, really suggest solving this relationship problem on the Form
rather than struggling with a Query. Base a Form directly on the
AddressLocation table. Put a subform on this form based on Legal (it could be
a continuous subform) to show the records in Legal.

Put a second subform (in *single form view*) on the form based on Septic. Then
put a sub-subform on the Septic subform for the inspections - this should
probably be a continuous form sorted by inspection date.

Trying to create One Grand Master Query is simply not going to work in this
instance!

John W. Vinson [MVP]

 

[Guest]

Thank you for your help, I have done as you suggest and everything is
working. Have a great weeken!