microsoft access 2016 - form - recordset not updateable

Question

Hi,

I'm hoping someone can help me figure out why I can no longer update my table, using my form (I can update the table itself but can't use my form). I inherited this database and it was working fine until it crashed and created a backup. The data in both tables seem fine, so now I really would just like to be able to use my form again and keep working. I tried to "compact and repair database" using the backup it created and I still can't update any fields and in the bottom left it always says "This Recordset is not updateable". I read other posts on this forum and it may be a problem with the way the form is querying the tables? The one form pulls data from two tables. I'm hoping someone can tell me if something is wrong with the query? I'm not sure why there is an asterix (the table doesn't have an asterix in its name) but don't know how to fix it. When I try to delete it from the query, it seems to work but then when I open the form, it asks me to enter a "Parameter Value" in a box that says "CSAS Publications Metadata" and then has a text box for me to enter a parameter value. 

Design View:

Field: CSAS PUBLICATIONS METADATA.*

Table: CSAS PUBLICATIONS METADATA

Field: PeerReviewMtg1.Value

Table: CSAS PUBLICATIONS METADATA

Query Builder SQL view:

SELECT [CSAS PUBLICATIONS METADATA].*, [CSAS PUBLICATIONS METADATA].PeerReviewMtg1.Value

FROM [CSAS PUBLICATIONS METADATA];

Thanks,

Laura

Answer 1

OK, Looking at it more closely, it appears you are only using one table. Where do you get the idea you are using two tables. Also you are using a multi-value field. I'm not sure why that field is listed separately.

What I would try is to use the pull down for the Recordsource property and just select the CSAS PUBLICATIONS METADATA table. Not use a SQL query. (Note: make sure you have a backup copy of the original Access file before making changes).

See if that makes the form editable.

Answer 2

Thanks Scott! I think it is the recordsource. I found it in design view, in the "data" tab of the property sheet, by clicking on the "more info "..." of "record source" and then choosing the SQL view.

Do you have advice as to where to go from here? I don't know how to rewrite my SQL statement. It's the same SQL statement in the original Access file, as well as the file that was created when it crashed (I've been using the original and have just kept the other as a backup!).

Thanks again!

Laura

Answer 3

Hi Laura, I'm an independent adviser and will try to help.

Please post the SQL for the query that is the Recordsource of the form. The message you are getting indicates something is causing that query to not be updateable. Normally, it is not a good idea to use multi-table queries as Recordsources, but rather use mainform/subform combinations.

If

SELECT [CSAS PUBLICATIONS METADATA].*, [CSAS PUBLICATIONS METADATA].PeerReviewMtg1.Value

FROM [CSAS PUBLICATIONS METADATA];

is the Recorsource, the asterisk is indicating to use all fields from that table. But the SQL statement has no join between the two tables which would make it uneditable. It seems possible the crash and your using the backup file may have damages the SQL statement.