copied form to new database doesn't work

[Bob H]

I have a database Access 2007 in with a few controls and text boxes for
editing which worked ok in one database. I copied the same form over to
a new database with extra forms etc, and now the copied form no longer
allows edits and neither do some of the cmd buttons.

I have checked in properties that editing is allowed and it is not
locked. Basically I have made sure that there is nothing in the forms
properties which would not allow editing.

So now I am at a loss why this should happen.

Thanks

 

[Jeff Boyce]

Bob

Can you confirm that the form has an underlying data source?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Bob H]

Bob

Can you confirm that the form has an underlying data source?

Regards

Jeff Boyce
Microsoft Access MVP

The record source for the form is a qry, but I have also noticed that
nothing is allowed, ie, no right click menu to rename anything. It is
like the whole database is is locked for some reason.

The message on the status bar says, recordset not updateable, when I try
to add data to the form.

Thanks

 

[Jeff Boyce]

Bob

Try checking Access HELP, if you haven't done so already. Look for
"updateable recordsets".

There are some circumstances under which a query (i.e., a recordset) is not
updateable.

Consider posting the SQL statement for that query...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Bob H]

Bob

Try checking Access HELP, if you haven't done so already. Look for
"updateable recordsets".

There are some circumstances under which a query (i.e., a recordset) is not
updateable.

Consider posting the SQL statement for that query...

Regards

Jeff Boyce
Microsoft Access MVP

I'm not having much luck yet trying to find a suitable solution for
'recordset not updateble', but here is th SQL for the query which the
form is getting the data from:

SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblLocation.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded,
tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"Due
Soon",Nz([NextTestDate],"No Test Date"))) AS OutOfDate
FROM tblLocation INNER JOIN (tblToolType INNER JOIN tblTools ON
tblToolType.ToolTypeID = tblTools.ToolTypeID) ON tblLocation.LocationID
= tblTools.LocationID
WHERE (((tblTools.ToolTypeID)=[Forms].[Tools].[cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));


Thanks

 

[Douglas J. Steele]

I'm not having much luck yet trying to find a suitable solution for
'recordset not updateble'

Check what Allen Browne has at http://www.allenbrowne.com/ser-61.html

 

[Bob H]

Bob

Try checking Access HELP, if you haven't done so already. Look for
"updateable recordsets".

There are some circumstances under which a query (i.e., a recordset)
is not
updateable.

Consider posting the SQL statement for that query...

Regards

Jeff Boyce
Microsoft Access MVP

I'm not having much luck yet trying to find a suitable solution for
'recordset not updateble', but here is th SQL for the query which the
form is getting the data from:

SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblLocation.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded,
tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"Due
Soon",Nz([NextTestDate],"No Test Date"))) AS OutOfDate
FROM tblLocation INNER JOIN (tblToolType INNER JOIN tblTools ON
tblToolType.ToolTypeID = tblTools.ToolTypeID) ON tblLocation.LocationID
= tblTools.LocationID
WHERE (((tblTools.ToolTypeID)=[Forms].[Tools].[cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));


Thanks

This qry SQL is from a duplicate on another database at work, which
works fine, but on this database, it does not show any records on the form!

SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblTools.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded,
tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"Due
Soon",Nz([NextTestDate],"No Test Date"))) AS OutOfDate
FROM tblToolType INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID
WHERE (((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));

Thanks

 

[John W. Vinson]

This qry SQL is from a duplicate on another database at work, which
works fine, but on this database, it does not show any records on the form!

SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblTools.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded,
tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"Due
Soon",Nz([NextTestDate],"No Test Date"))) AS OutOfDate
FROM tblToolType INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID
WHERE (((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));

That very strongly suggests that one or other of the tables has no data, or
that there is no matching value of ToolTypeID. Since you're not using
tblToolType in the query, either to retrieve a field or as a criterion, try
just removing this table from the query altogether!

Or are the LastTestDate and NextTestDate fields in tblToolTypes?

 

[Bob H]

This qry SQL is from a duplicate on another database at work, which
works fine, but on this database, it does not show any records on the form!

SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblTools.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded,
tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"Due
Soon",Nz([NextTestDate],"No Test Date"))) AS OutOfDate
FROM tblToolType INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID
WHERE (((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));

That very strongly suggests that one or other of the tables has no data, or
that there is no matching value of ToolTypeID. Since you're not using
tblToolType in the query, either to retrieve a field or as a criterion, try
just removing this table from the query altogether!

Or are the LastTestDate and NextTestDate fields in tblToolTypes?

Vvery strange indeed!
The query which is read only, is getting data from tblTools, which
consists of almost 5000 records, and frmTools reads and shows this data.
I created a new query based on the same tblTools, which has all the same
records in, but the frmTools does not show the records from the new query.
Furthermore, the frmtTools does not show any records from tblTools!

The LastTestDate and NextTestDate fields are in the tblTools.

Thnaks

 

[Bob H]

This qry SQL is from a duplicate on another database at work, which
works fine, but on this database, it does not show any records on the
form!

SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblTools.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded,
tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"Due
Soon",Nz([NextTestDate],"No Test Date"))) AS OutOfDate
FROM tblToolType INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID
WHERE (((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));

That very strongly suggests that one or other of the tables has no
data, or
that there is no matching value of ToolTypeID. Since you're not using
tblToolType in the query, either to retrieve a field or as a
criterion, try
just removing this table from the query altogether!

Or are the LastTestDate and NextTestDate fields in tblToolTypes?

Vvery strange indeed!
The query which is read only, is getting data from tblTools, which
consists of almost 5000 records, and frmTools reads and shows this data.
I created a new query based on the same tblTools, which has all the same
records in, but the frmTools does not show the records from the new query.
Furthermore, the frmtTools does not show any records from tblTools!

The LastTestDate and NextTestDate fields are in the tblTools.

Thanks

I have solved the non updateable query by removing a related table, but
now no records are shown in the form!

 

[Bob H]

This qry SQL is from a duplicate on another database at work, which
works fine, but on this database, it does not show any records on the
form!

SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblTools.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded,
tblTools.DateEdited,
IIf([NextTestDate]<Date(),"OverDue",IIf(([NextTestDate]-Date()<7),"Due
Soon",Nz([NextTestDate],"No Test Date"))) AS OutOfDate
FROM tblToolType INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID
WHERE (((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));

That very strongly suggests that one or other of the tables has no
data, or
that there is no matching value of ToolTypeID. Since you're not using
tblToolType in the query, either to retrieve a field or as a
criterion, try
just removing this table from the query altogether!

Or are the LastTestDate and NextTestDate fields in tblToolTypes?

Vvery strange indeed!
The query which is read only, is getting data from tblTools, which
consists of almost 5000 records, and frmTools reads and shows this data.
I created a new query based on the same tblTools, which has all the same
records in, but the frmTools does not show the records from the new
query.
Furthermore, the frmtTools does not show any records from tblTools!

The LastTestDate and NextTestDate fields are in the tblTools.

Thanks

I have solved the non updateable query by removing a related table, but
now no records are shown in the form!

Update: Solved.

After removing a related table from the query, which made the query
editable, I then searched for reasons why no data was shown in the form
from the query. I found the answer here:

Open up the form in design view.

Open up the form's properties and go to the Data tab.

Make sure that Data property is set to No and all the "Allow" properties
are set to Yes.
The data property was set to Yes.

Now records are shown and the form is editable.

 

[John W. Vinson]

Make sure that Data property is set to No and all the "Allow" properties
are set to Yes.
The data property was set to Yes.

That's the "Data Entry" property, for the lurkers. Data Entry often causes
such confusion - you would think it means that you can enter data in the form,
but it really means that the form is ONLY for entering new data; existing
records are not shown.

 

[David W. Fenton]

That's the "Data Entry" property, for the lurkers. Data Entry
often causes such confusion - you would think it means that you
can enter data in the form, but it really means that the form is
ONLY for entering new data; existing records are not shown.

This is quite clearly explained in the Help file, so I can't quite
fathom why anyone would be puzzled about what it does.

 

[John W. Vinson]

This is quite clearly explained in the Help file, so I can't quite
fathom why anyone would be puzzled about what it does.

How many users do you know who READ the Help file...? <g>

 

[Douglas J. Steele]

How many users do you know who READ the Help file...? <g>

Approximately an inverse number to those who set properties without knowing
whether or not they should? <g>

 

[John W. Vinson]

Approximately an inverse number to those who set properties without knowing
whether or not they should? <g>

As many such people would say (or at least think)... Sounds Reasonable!

 

[David W. Fenton]

How many users do you know who READ the Help file...? <g>

If you are going to post in a public forum asking the question,
don't you want to avoid the embarassment of having it pointed out to
you that you're wasting people's time on something that you could
have answered without posting?

It's one thing to read the help file and find it confusing (happens
to me every day!), and then to post asking for clarification.

It's entirely another thing to ask a question that has an answer
ready at hand in the environment in which the question arose.