Recordset is locked for editing (DAO)

**Denburt** · Jul 12 '10, 05:54 PM

Originally posted by Martin Lang

Hi,

I am using following code to select a recordset

Code:

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

Code:

        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

A few questions what event are you using this code in?
I would also like to know if the table "Halvfabrik at" 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** · Jul 12 '10, 06:27 PM

Originally posted by Denburt

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

Hi Denburt,

Thank you for showing interest.

Table "halvfabrik at" is used in the query biult.

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

Martin

**Martin Lang** · Jul 12 '10, 06:42 PM

the whole code

Here is the whole code if needed

Code:

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** · Jul 12 '10, 07:13 PM

Originally posted by Martin Lang

Hi Denburt,

Thank you for showing interest.

Table "halvfabrik at" is used in the query biult.

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

Martin

I am still unsure of how or where you are calling the function "fncOverføreHFt ilrå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** · Jul 12 '10, 07:21 PM

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** · Jul 12 '10, 07:49 PM

Originally posted by 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

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.

503 Service Unavailable

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

**Martin Lang** · Jul 13 '10, 10:13 AM

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. tblIntermediate s(IDArticle, Intermediatenam e, 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 produktdeklaras jon, Forholdstall, Kalkulasjonspri s, Kalkprissistend ret, Kj, Kcal, Protein, Karbohydrat, Fett, Råvaredata)
In English
2. Ingredients (IDIngredient, *VismaID, Ingredientname, Name used for productdeclarat ion, Rationumber, Calculationpric e, Calculationpric elastedited, Energy fat and so on, Ingredientdata)
3.tblHalvfabrik atoppskrift (*IDHalvfabrikat , *IDråvare, Mengde)
In English
3. tblIntermediaer ecipe (*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 qryTotalcostper Intermediate, in addition to the code.

Attached Files

Produktdatabase versjon 1.0.zip (54.6 KB, 170 views)

**NeoPa** · Jul 13 '10, 01:01 PM

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** · Jul 13 '10, 01:03 PM

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** · Jul 13 '10, 01:06 PM

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** · Jul 13 '10, 02:20 PM

Originally posted by 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.

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

I have a query A with following fields
(*IDArticle, *IDIngredient, Amount, Ingredientpurch aseprice, Cost: Amount*Ingredie ntpurchaseprice )

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(Ingredientc ost) is not null...

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

**Martin Lang** · Jul 13 '10, 03:35 PM

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** · Jul 13 '10, 04:05 PM

Originally posted by Martin Lang

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

I have a query A with following fields
(*IDArticle, *IDIngredient, Amount, Ingredientpurch aseprice, Cost: Amount*Ingredie ntpurchaseprice )

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(Ingredientc ost) is not null...

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

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(Ingredientc ost) 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** · Jul 13 '10, 04:37 PM

Originally posted by Denburt

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

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...?

Something like Dsum(Cost; qryCostPerEachI ngredient; IDArticle = varIDArticle) ?

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

Recordset is locked for editing (DAO)

Recordset is locked for editing (DAO)

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment