Recordset with join is not updateable - how to do VBA right way

Question

I am sorry if repeating some older answers, but I cannot figure it out. I have Access form, connected to db, which is filtered by some fields and some vba procedures to operate it.

There is select which define my recordset:

strsearch = "Select * from T1 left join DatT2M on T1.ID = DatT2M.RefID where ((Mesto like ""*" & strtextM & "*"" and (DatT2M.DatumT2M > " & DatK_od & " and DatT2M.DatumT2M < " & DatK_do & ") and (T1.DatumDK > " & DatDK_od & " and T1.DatumDK < " & DatDK_do & ") ))"

T1 and T2 are linked tables from db,

Dat2M is "auxiliary" query to find last date of update:

   SELECT T2.RefID, Max(T2.Datum) AS DatumT2M
   FROM T2
   GROUP BY T2.RefID
   HAVING (((T2.RefID) Is Not Null));

and after_update of field I save them by this VBA function:

With Me.Recordset
    .Edit
    .Fields("[uzivatel]") = uzivDB
    .Update
End With
DoCmd.RunCommand acCmdSaveRecord.

When I update some field of form, some sound appear and in the left corner of Access window is written "Recordset is not updateable".

Can you tell me, what have I change I my code, to make it updateable and write values to database tables, please?

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2020-06-14T15:25:50

The query is simply not updatable. So, you have to change the update code, or change the code you are using to display and fill the controls on the form.

One way is to move all the controls of the child table to a sub form, and thus they can be edited. You do not mention if the sample update code you have is attempting to update a column in the main table, or the child table of your sql join.

So, your update code becomes this:

dim rst     as dao.RecordSet
dim strSQL  as string
strSQL = "select * from DAT2M where Ref2id = " & me!id
set rst = Currentdb.OpenRecordSet(strSQL)
rst.edit
rst.Fields("[uzivatel]") = uzivDB
rst.Update
rst.close

So, the issue is that the query is no updatable. It also not clear why your posted code does a update in code, and then attempts to do a save recordset with the runcommand + saverecord.

So, base the form on a single table. If you need to update additional columns from the child table, then move them all to a sub form. This would mean that you don't have to write ONE LINE of code. So, the form should be based on one table - not two. If you setup the link master/child settings for the main form, then the child form and records displayed should follow without additional code.

Recordset with join is not updateable - how to do VBA right way

1 Answers