save record isn't available now

H

Harold Good

Hello,

I have a table that collects data from about 150 spreadsheets each day, and
then I refer to this table in a Report, without any problems.

I now want to use the same data from this table, but in a Form but I get the
message "save record isn't available now". From what I've read on previous
messages from others, the problem is that it can't write to the table, thus
it can't save.

So assuming that is the problem, is there some way around this?

If it involves VBA code, where would I put the code, would it be in one of
the Form's Events?

Thanks very much,
Harold
 
C

Carl Rapson

If you set the form's AllowAdditions property to No, it should eliminate
that message. If the underlying table isn't writable, you may need to set
AllowEdits and AllowDeletions to No also.

Carl Rapson
 
H

Harold Good

Hi Carl,

I have done all three of these and it makes no difference.
Any other thoughts?

Thanks,
Harold
 
J

John W. Vinson

I now want to use the same data from this table, but in a Form but I get the
message "save record isn't available now". From what I've read on previous
messages from others, the problem is that it can't write to the table, thus
it can't save.
Click to expand...

What's the actual Recordsource property of this form? I'm guessing that it's a
non-updateable query.

John W. Vinson [MVP]
 
C

Carl Rapson

As John Vinson asked, is the form based on a query or on the table directly?
If it's a query, can you open the query directly and modify records? If not,
then the query itself is non-updatable, and so the form will be also. If the
form is based on the table directly, I'm not sure why it wouldn't be
updatable. Are your tables split into a backend file? If so, are you sure
you have write access to the backend file?

Carl Rapson
 
H

Harold Good

Hi,
Good points you bring up. It is based on a query. No, from this query I
cannot directly modify the records. But the database is split into a
backend, and I do have write access to the backend, because if I click on
the table link in the front end, it opens the table and I can modify it.
Also, on the front end if I make a very basic query based on this table,
from this simple query i can modify the table.

So there's lots of good diagnosis, but I'm not sure where to go with it.
Does it mean that the form's query isn't quite designed correctly? Assuming
the form's query can't be improved, is there a way around this?

Thanks again,
Harold
 
J

John W. Vinson

So there's lots of good diagnosis, but I'm not sure where to go with it.
Does it mean that the form's query isn't quite designed correctly?
Click to expand...

That's exactly what it means. If it's a single-table query (or possibly two or
three tables) it should be possible to change it so that it can be updated. If
you wish, you can open the query in SQL view and post the SQL text here -
someone may be able to identify why it's not updateable.

If it's a multitable query it probably won't be possible, but you probably
don't WANT to be trying to update such a query anyway; proper form design,
with subforms, combo boxes and other tools should make it unnecessary to use
such a complex query.

John W. Vinson [MVP]
 
H

Harold Good

Thanks for your help. It is a multitable query, and some tables can have
multiple occurences of an ID that seems to be the culprit.

So since there doesn't seem to be any easy way to fix that aspect, is there
a way around it?

The form would never write to that particular table, just display useful
information.

Is there a way to prevent those few fields (text boxes) from writing to that
table, so that the form will work?

Thanks,
Harold
 
J

John W. Vinson

Thanks for your help. It is a multitable query, and some tables can have
multiple occurences of an ID that seems to be the culprit.

So since there doesn't seem to be any easy way to fix that aspect, is there
a way around it?

The form would never write to that particular table, just display useful
information.

Is there a way to prevent those few fields (text boxes) from writing to that
table, so that the form will work?
Click to expand...

I have no trace of an idea what fields are "writing to that table". You can
see the form - I can't!

If the form isn't being used to update any table, I don't know why you would
get the message. What actions cause the error message to appear?

John W. Vinson [MVP]
 
H

Harold Good

Since it is based on a multitable query, the form does write to some other
tables, but the four text boxes on the form that cause the problem come from
the table "ExcelProgressData_SNAPSHOT" that each day gathers information
from 100+ spreadsheets. But those 4 boxes are locked on the form so that
they can't be edited from the form - just display info. So it would seem
that since they are locked, they can't write to that table anyway since the
data in those 4 doesn't change. But that's a newbie's way of seeing it!

Here is the complicated code.

SELECT TBL_FieldCoordinators.FCName, TBL_Languages.EthCode,
TBL_LanguageStages.StageProjectID, TSC_PROJECTS.ProjectName,
TSC_PROJECTS.ProjectID, TSC_PROJECTS.Active_Project,
TBL_Languages.TSCReportName, TBL_LanguageStageGoals.LangStageGoalID,
TBL_LanguageStageGoals.LangStageGoalDesc, TBL_LanguageStages.StageStatus,
tblStageStatusID.StageStatusName, TBL_Languages.Country,
TBL_LanguageStages.StageRevisedEstEndYr, TBL_LanguageStages.StageBeginDate,
TBL_LanguageStages.StageRevisedEstEndYr, TSC_PROJECTS.LocationWeb,
TBL_Languages.CountryEdited, TBL_LanguageStageTypes.StageTypeShortName,
ExcelProgressData_SNAPSHOT.Qtr, ExcelProgressData_SNAPSHOT.Yr,
ExcelProgressData_SNAPSHOT.Planned, ExcelProgressData_SNAPSHOT.Actual,
TBL_Languages.TSCPopTotal, TBL_Languages.SensitivityLevel,
TBL_LanguageStageTypes.StageTypeName, TBL_Languages.EthName,
TBL_PROJECT_FINANCIALS.FiscalYear, TBL_PROJECT_FINANCIALS.AnnualBudget,
TBL_PROJECT_FINANCIALS.YTDExpense
FROM (((TBL_LanguageStageTypes INNER JOIN ((TBL_LanguageStageGoals INNER
JOIN (TBL_LanguageStages INNER JOIN tblStageStatusID ON
TBL_LanguageStages.StageStatus = tblStageStatusID.StageStatus) ON
TBL_LanguageStageGoals.LangStageGoalID = TBL_LanguageStages.StageGoalID)
LEFT JOIN ExcelProgressData_SNAPSHOT ON TBL_LanguageStages.EthID =
ExcelProgressData_SNAPSHOT.EthID) ON TBL_LanguageStageTypes.StageTypeID =
TBL_LanguageStages.StageTypeID) INNER JOIN ((TBL_FieldCoordinators INNER
JOIN TSC_PROJECTS ON TBL_FieldCoordinators.FCID = TSC_PROJECTS.FCID) INNER
JOIN tblLanguagesTSC ON TSC_PROJECTS.ProjectID = tblLanguagesTSC.ProjectID)
ON TBL_LanguageStages.StageProjectID = TSC_PROJECTS.ProjectID) INNER JOIN
TBL_Languages ON (TBL_Languages.EthID = tblLanguagesTSC.EthID) AND
(TBL_LanguageStages.EthID = TBL_Languages.EthID)) LEFT JOIN
TBL_PROJECT_FINANCIALS ON TSC_PROJECTS.ProjectID =
TBL_PROJECT_FINANCIALS.ProjectID
WHERE (((TSC_PROJECTS.Active_Project)=True) AND
((TBL_LanguageStages.StageStatus)=1) AND
((TBL_PROJECT_FINANCIALS.FiscalYear)=2007));

But if I make a basic query, as follows (linked to TBL-Languages), this
query WILL write to the "ExcelProgressData_SNAPSHOT" table:
SELECT ExcelProgressData_SNAPSHOT.EthID, ExcelProgressData_SNAPSHOT.Qtr,
ExcelProgressData_SNAPSHOT.Yr, ExcelProgressData_SNAPSHOT.Planned
FROM ExcelProgressData_SNAPSHOT INNER JOIN TBL_Languages ON
ExcelProgressData_SNAPSHOT.EthID = TBL_Languages.EthID;

But if I make another basic query, as follows (linked to
TBL-LanguageStages), this query will NOT write to the
"ExcelProgressData_SNAPSHOT" table, because TBL Language Stages needs to
have multiple occurrences of EthID:
SELECT ExcelProgressData_SNAPSHOT.EthID, ExcelProgressData_SNAPSHOT.Qtr,
ExcelProgressData_SNAPSHOT.Yr, ExcelProgressData_SNAPSHOT.Planned
FROM ExcelProgressData_SNAPSHOT INNER JOIN TBL_LanguageStages ON
ExcelProgressData_SNAPSHOT.EthID = TBL_LanguageStages.EthID;

Likely not worth plowing thru all this. Sorry to bother you with it.

Thanks,
Harold
===============================================
 
Top