Recordset is locked for editing (DAO)

[Martin Lang]

Hi,

I am using following code to select a recordset

Set rsI = CurrentDb.OpenRecordset("SELECT [Halvfabrikat].[IDArtikkel], [Halvfabrikat].[Hfnavn], [Halvfabrikat].[IDRåvare], " & _
" qryTotalkostPerEnhetHF.Lønnskost, qryTotalkostPerEnhetHF.Råvarekost FROM Halvfabrikat INNER JOIN qryTotalkostPerEnhetHF" & _
" ON Halvfabrikat.IDArtikkel = qryTotalkostPerEnhetHF.IDArtikkel WHERE Isnull(qryTotalkostPerEnhetHF.Lønnskost) = False AND" & _
" Isnull(qryTotalkostPerEnhetHF.Råvarekost) = False")

Later on in the code, I want to edit data in the recordset

        IDRåvare = rsO!Artikkelnr
        rsI.FindNext rsI!IDArtikkel = " & test & "
        rsI.Edit
        rsI!IDRåvare = IDRåvare
        rsI.Update

When rsI.edit is reached, I get the error code 3027 "Cannot Update. Database or object is read-only"

Is it because I have used queries in the Select string?

The queries are partly "built" on the table halvfabrikat.

Any help is much appreciated :)

Martin

[Denburt]

@Martin Lang
A few questions what event are you using this code in?
I would also like to know if the table "Halvfabrikat" is used in the query built (if you built one) or if that is the table used in the forms Record Source property.

[Martin Lang]

@Denburt
Hi Denburt,

Thank you for showing interest.

Table "halvfabrikat" is used in the query biult.

There is no form involved in the code. It is a kind of "Update" routine.

Martin

[Martin Lang]

Here is the whole code if needed

Public Function fncOverføreHFtilråvare()
 
Dim rsI As DAO.Recordset
Dim rsI2 As DAO.Recordset
Dim rsI3 As DAO.Recordset
Dim rsI4 As DAO.Recordset
Dim rsO As DAO.Recordset
 
' Select the unique IDArtikkel
Set rsI = CurrentDb.OpenRecordset("SELECT [Halvfabrikat].[IDArtikkel], [Halvfabrikat].[Hfnavn], [Halvfabrikat].[IDRåvare], " & _
" qryTotalkostPerEnhetHF.Lønnskost, qryTotalkostPerEnhetHF.Råvarekost FROM Halvfabrikat INNER JOIN qryTotalkostPerEnhetHF" & _
" ON Halvfabrikat.IDArtikkel = qryTotalkostPerEnhetHF.IDArtikkel WHERE Isnull(qryTotalkostPerEnhetHF.Lønnskost) = False AND" & _
" Isnull(qryTotalkostPerEnhetHF.Råvarekost) = False ")
Set rsO = CurrentDb.OpenRecordset("råvare")
Test = rsO.RecordCount
 
' Loop to add one row for each IDArtikkel
While Not rsI.EOF
    Test = rsI!IDArtikkel
    Set rsI2 = CurrentDb.OpenRecordset("Select Ingrediensliste From tblIngredienslisteHF WHERE IDartikkel = " & rsI!IDArtikkel & "")
    Set rsI3 = CurrentDb.OpenRecordset("Select [sum av kj1], [sum av kcal1], [sum av fett1], [sum av protein1], [sum av karbohydrat1] FROM qryNæringsinnholdHalvfabrikataPåArtikkelnivå WHERE IDHalvfabrikat = " & rsI!IDArtikkel & "")
    Set rsI4 = CurrentDb.OpenRecordset("Select totalenhetskost From qryTotalkostPerEnhetHF WHERE IDartikkel = " & rsI!IDArtikkel & "")
    If IsNull(rsI!IDRåvare) = True Then
        rsO.AddNew
        rsO!Råvarenavn = rsI![Hfnavn]
        rsO!Råvaredata = rsI2!Ingrediensliste
        rsO!Kalkulasjonspris = rsI4!Totalenhetskost
        rsO!Kj = rsI3![Sum Av KJ1]
        rsO!kcal = rsI3![sum av kcal1]
        rsO!Protein = rsI3![sum av protein1]
        rsO!karbohydrat = rsI3![sum av karbohydrat1]
        rsO!Fett = rsI3![sum av fett1]
        rsO.Update
        rsO.MoveLast
        IDRåvare = rsO!Artikkelnr
        rsI.FindFirst rsI!IDArtikkel = " & test & "
        rsI.Edit
        rsI!IDRåvare = IDRåvare
        rsI.Update
 
    Else
        Dim strwhere As String
        strwhere = "[råvare].[Artikkelnr] =" & rsI!IDRåvare & ""
        rsO.FindFirst strwhere
        rsO.Edit
            rsO!Råvarenavn = rsI![Hfnavn]
            rsO!Råvaredata = rsI2!Ingrediensliste
            rsO!Kalkulasjonspris = rsI4!Totalenhetskost
            rsO!Kj = rsI3![Sum Av KJ1]
            rsO!kcal = rsI3![sum av kcal1]
            rsO!Protein = rsI3![sum av protein1]
            rsO!karbohydrat = rsI3![sum av karbohydrat1]
            rsO!Fett = rsI3![sum av fett1]
            rsO.Update
    End If
    rsI.MoveNext
 
 
Wend
Set rsI = Nothing
Set rsI2 = Nothing
Set rsI3 = Nothing
Set rsI4 = Nothing
Set rsO = Nothing
 
End Function

[Denburt]

@Martin Lang
I am still unsure of how or where you are calling the function "fncOverføreHFtilråvare" from. If you are calling the function from inside of a query then you are definitely going to have problems.

I am not sure exactly what you are trying to do but I will try to help.

1.You will need a form with a button or something, or maybe a macro to call this function and that is where we need to start. Once you have that we can work from there.

2.Just for verification purposes create a new query don't use the wizard, cancel it if it shows up and if the "Show Table" dialog shows up close that also. Then you can use the menu view to go to SQL view.

Insert the SQL statement for rsI then view it as a datasheet and see if you can edit/add new records manually.


Let us know how that goes.

[Martin Lang]

Thank you very much Denburt.

1. Hehe, of course. Didn't think of it in that way. I have a button that calls the macro.

2. Done. Couldn't edit anything.

Wow, you're good at problemshooting.

:)

Martin

[Denburt]

@Martin Lang
Glad I could help, if you have any trouble setting up the query so it is editable you can post your table structures (pointing out primary keys etc.) and I will be glad to see what I can do. So many time there are people that will not set a simple primary key or something similar and that can prevent a query from updating.

If you need help or would like more information on structuring your tables their primary keys and such then you can take a look at this article.
http://bytes.com/topic/access/insigh...ble-structures

[Martin Lang]

Thank you,

I have had a small course in normalisation and table structures. Of course, I might have gone wrong on that part.

Here are the tables used (underlined is PK, *Field is foreign key)

1. tblhalvfabrikat (IDArtikkel, HFnavn, antall enheter per oppskrift, *IDAvdeling, Sist endret, IDråvare)
In English
1. tblIntermediates(IDArticle, Intermediatename, number of products per recipe, *IDDepartment, Last edited, IDIngredient)
(the last column, IDIngredient, is used for a macro that copies that specific intermediate to the ingredient table)

2. Råvare (IDRåvare, *VismaID, Råvarenavn, Navn i produktdeklarasjon, Forholdstall, Kalkulasjonspris, Kalkprissistendret, Kj, Kcal, Protein, Karbohydrat, Fett, Råvaredata)
In English
2. Ingredients (IDIngredient, *VismaID, Ingredientname, Name used for productdeclaration, Rationumber, Calculationprice, Calculationpricelastedited, Energy fat and so on, Ingredientdata)
3.tblHalvfabrikatoppskrift (*IDHalvfabrikat, *IDråvare, Mengde)
In English
3. tblIntermediaerecipe (*IDIntermediate, *IDIngredient, Amount)

I have attached a part of the database where you also may have a look on the tables, the four relevant queries that are used in the final qryTotalcostperIntermediate, in addition to the code.

[NeoPa]

Reasons for a Query to be Non-Updatable.

I suggest you have a look through these as an early step. There is little point getting the code to work perfectly if you cannot find an updatable query to do what you need.

I'm sure DenBurt will guide you wisely anyway. He's very experienced.

[Martin Lang]

Thanks Neopa,

Will do. Hopefull I will be able to solve it myself with this article. Will let you know if I do :)

Martin

[NeoPa]

You may well Martin. I have to say though, that knowing your SQL and coding that SQL into your string using VBA are two entirely different things. If you manage to get from A to B without need for further help then that'd be well done indeed.

[Martin Lang]

@NeoPa
Well, I have found out what causes the locked query.

I have a query A with following fields
(*IDArticle, *IDIngredient, Amount, Ingredientpurchaseprice, Cost: Amount*Ingredientpurchaseprice)

Query B is used to summarize the field Cost. It is query B that causes problem.

If I remove "group by article" from query B, I am able to edit data in it. But on the other hand, I will need it later on....

The reason why is that the Select string in the DAO code is... Select IDArticle where Sum(Ingredientcost) is not null...

Maybe there is a way to work around the problem... or a solution in the SQL string?

[Martin Lang]

Hmm, I have been thinking.... a lot... And to me the simplest solution seems to be to write the stuff I need from the query into a table, and use that table in my SQL string.... Unless you feel there is an easier way around this....

[Denburt]

@Martin Lang
I am still a bit confused but I will do my best...

If I remove "group by article" from query B, I am able to edit data in it. But on the other hand, I will need it later on....

The reason why is that the Select string in the DAO code is... Select IDArticle where Sum(Ingredientcost) is not null...

Maybe there is a way to work around the problem... or a solution in the SQL string?

Once you run query B then you can take the ID (IDArticle?) store it in a variable and rerun query B using the ID in a where statement to select that record without "group by article" and make your adjustments...?

[Martin Lang]

@Denburt
Something like Dsum(Cost; qryCostPerEachIngredient; IDArticle = varIDArticle) ?

Well, that works, but it gives me multiple records. Then again, I could just use the "Select Distinct in the SQL string....?

[Denburt]

@Martin Lang
I am sorry you have me completely lost now. I read post #12 and you were referring to query A and query B and mentioning items not even in the database that you posted or in the code you have previously posted. The database you posted has one query that sums your figures up but it isn't used in the code either so excuse me if I am a little confused as to where we or you are currently and what your goal is at this point. Maybe you can rephrase your question and I will see if I can help.

[NeoPa]

Martin,

I plan to look at this again this evening. Could you do me a favour and explain your current situation and question as clearly as you can and how it relates to the database attached. I will be able to look at that when I get home, but I haven't had a chance to yet as I'm at work.

[Martin Lang]

Sorry that I haven't expressed this clearly enough :) I really appreciate your help and kind offers :) No reason to excuse yourself as it is me who complicates things with my badly defined problems :)

I used the terms query A and query B to simplify my Norwegian structure in English :) And your advice has really helped me to understand why the original error message appeared in the code I posted in #4.

If you have a look in post #4, on row 10, I have a Select string. The select string consist of several tables/queries. "Query B" was making the error. The reason why was that I had ticked "totals" in design view(if you understand what I mean, not sure if it is the correct translation to the English version of Access), but a new row pops up in design view) . "Totals" locks all posts for editing. That was why I couldn't run my original code (post #4), where I wanted to edit the DAO recordsource which query B locked.

I think I will be able to solve it by myself now. I will come back to you if not. Anyway I will let you know how it goes.

Thanks to both of you :) I really appreciate it!

Martin

[Denburt]

If you have a look in post #4, on row 10, I have a Select string. The select string consist of several tables/queries. "Query B" was making the error. The reason why was that I had ticked "totals" in design view(if you understand what I mean, not sure if it is the correct translation to the English version of Access), but a new row pops up in design view) . "Totals" locks all posts for editing. That was why I couldn't run my original code (post #4), where I wanted to edit the DAO recordsource which query B locked.

Ah I see it, you just need to open a recordset for "Select IDRåvare from Halvfabrikat where IDHalvfabrikat = " & rsI!IDArtikkel & "" then you can update IDRåvare instead of trying to update the RSI recordset since you can't update RSI for reasons specified earlier.

Good luck hope all goes well let us know if there is anything else we can do.

[Martin Lang]

@Denburt
Thank you Denburt,

This is my first "real" database. A lot of lessons learned. My plan is to rebuild it at a later point when I am sufficiently motivated. Now, it is more about solutions that works and less the most optimal solutions.

So I went for a dummytablesolution where data is temporary stored. Will look deeper into this issue at a later point. Till then, hopefully these things have matured for me :)

Thank you for your friendly offer. I am so glad that people like you, Nico and NeoPa exists!

Have a nice evening, or maybe it is day where you are :)

Martin

[Denburt]

@Martin Lang
I am just glad I could help.

Everyone has to start somewhere, and I know I have had to stop and say wait I have an issue.... That's one reason I decided to help on this board the members were all so willing to help me whenever I have had or have an issue or two come up.

You have a good evening as well and again I am glad you have a working solution.

[NeoPa]

Martin Lang: Sorry that I haven't expressed this clearly enough :) I really appreciate your help and kind offers :) No reason to excuse yourself as it is me who complicates things with my badly defined problems :)

That's probably a little harsh on yourself Martin. Defining problems clearly is a struggle for most people who speak English natively. Yours are always well explained and it's clear you have gone to some trouble to do that as well as you can. I was simply alluding to the fact that the later situation wasn't so clear related to the attached database. Easily done, and no longer a problem anyway.

Martin Lang: If you have a look in post #4, on row 10, I have a Select string. The select string consist of several tables/queries. "Query B" was making the error. The reason why was that I had ticked "totals" in design view(if you understand what I mean, not sure if it is the correct translation to the English version of Access), but a new row pops up in design view) . "Totals" locks all posts for editing. That was why I couldn't run my original code (post #4), where I wanted to edit the DAO recordsource which query B locked.

That makes perfect sense Martin. In SQL that translates to a GROUP BY clause (which is quite common). Now, when you see that phrase mentioned you'll know it's about using Totals in a query :)

By the way, I'm very pleased you have managed to get a solution, and please post more questions if you have them.

[Denburt]

@NeoPa
Very well put NeoPa! If only I could put things so eloquently. :)

Oh and Martin I wanted you to know I thought you explained things quite well by the way. Sometimes it can be difficult to get a point across no matter who we are or who we are talking to.

[Martin Lang]

Well, that is good to hear. Thank you. I will definitely come back whenever I am struggling with a new problem. Take care both of you :)