why is it protected

F

Frukten

This is what i'm trying to do. When i push a button, I want all [historia]
checkboxes in my query to be "checked". What happens is that i get a message
that says - " Cannot update, Database or Object is writeprotected." (freely
translated from swedish.)

I can't find any writeprotection. Please help.

Here is my code.

Private Sub Kommandoknapp169_Click()
On Error GoTo Err_Kommandoknapp169_Click

Dim fraga As String
Dim rs As DAO.Recordset
fraga = "SELECT Ink.Inkorder, Ink.[I lager], Ink.Historia, Ink.Histdat,
Ink.Antal AS bestallda, Sum(Korddata.Antal) AS salda,
[ink].[antal]-[korddata].[antal] AS kvar FROM Ink INNER JOIN Korddata ON
Ink.Inkorder = Korddata.inkord GROUP BY Ink.Inkorder, Ink.[I lager],
Ink.Historia, Ink.Histdat, Ink.Antal, [ink].[antal]-[korddata].[antal] HAVING
(((Ink.[I lager])=Yes) AND (([ink].[antal]-[korddata].[antal])<1));"
Set rs = CurrentDb().OpenRecordset(fraga)
Do Until rs.EOF
If Not rs.BOF Then
rs!Historia = True
rs.MoveNext
Else
Exit Sub
End If
Loop

Exit_Kommandoknapp169_Click:
Exit Sub

Err_Kommandoknapp169_Click:
MsgBox Err.Description
Resume Exit_Kommandoknapp169_Click

End Sub
 
D

Dirk Goldgar

Frukten said:
This is what i'm trying to do. When i push a button, I want all
[historia] checkboxes in my query to be "checked". What happens is
that i get a message that says - " Cannot update, Database or Object
is writeprotected." (freely translated from swedish.)

I can't find any writeprotection. Please help.

Here is my code.

Private Sub Kommandoknapp169_Click()
On Error GoTo Err_Kommandoknapp169_Click

Dim fraga As String
Dim rs As DAO.Recordset
fraga = "SELECT Ink.Inkorder, Ink.[I lager], Ink.Historia,
Ink.Histdat, Ink.Antal AS bestallda, Sum(Korddata.Antal) AS salda,
[ink].[antal]-[korddata].[antal] AS kvar FROM Ink INNER JOIN Korddata
ON Ink.Inkorder = Korddata.inkord GROUP BY Ink.Inkorder, Ink.[I
lager], Ink.Historia, Ink.Histdat, Ink.Antal,
[ink].[antal]-[korddata].[antal] HAVING (((Ink.[I lager])=Yes) AND
(([ink].[antal]-[korddata].[antal])<1));" Set rs =
CurrentDb().OpenRecordset(fraga) Do Until rs.EOF
If Not rs.BOF Then
rs!Historia = True
rs.MoveNext
Else
Exit Sub
End If
Loop

Exit_Kommandoknapp169_Click:
Exit Sub

Err_Kommandoknapp169_Click:
MsgBox Err.Description
Resume Exit_Kommandoknapp169_Click

End Sub
Click to expand...

1. Any "totals query" -- one that uses the GROUP BY clause, as yours
does -- will be nonupdatable (read-only), because each record returned
by the query may represent more than record in the source table(s).

2. If the query *were* updatable, you'd need to modify your code to
update it. You need to call the recordset's .Edit method before
modifying each record's fields, and its .Update method afterward to save
your changes:

With rs
Do Until .EOF
.Edit
!Historia = True
.Update
.MoveNext
Loop
.Close
End With

However, as I said, that won't work in this case, because your query is
not updatable.
 
F

Frukten

Any idea what I´m supposed to do? I have to have a summary on one of the
columns, cause if the summary isn´t ="0" i don´t wan't to check the checkbox.

"Dirk Goldgar" skrev:
Frukten said:
This is what i'm trying to do. When i push a button, I want all
[historia] checkboxes in my query to be "checked". What happens is
that i get a message that says - " Cannot update, Database or Object
is writeprotected." (freely translated from swedish.)

I can't find any writeprotection. Please help.

Here is my code.

Private Sub Kommandoknapp169_Click()
On Error GoTo Err_Kommandoknapp169_Click

Dim fraga As String
Dim rs As DAO.Recordset
fraga = "SELECT Ink.Inkorder, Ink.[I lager], Ink.Historia,
Ink.Histdat, Ink.Antal AS bestallda, Sum(Korddata.Antal) AS salda,
[ink].[antal]-[korddata].[antal] AS kvar FROM Ink INNER JOIN Korddata
ON Ink.Inkorder = Korddata.inkord GROUP BY Ink.Inkorder, Ink.[I
lager], Ink.Historia, Ink.Histdat, Ink.Antal,
[ink].[antal]-[korddata].[antal] HAVING (((Ink.[I lager])=Yes) AND
(([ink].[antal]-[korddata].[antal])<1));" Set rs =
CurrentDb().OpenRecordset(fraga) Do Until rs.EOF
If Not rs.BOF Then
rs!Historia = True
rs.MoveNext
Else
Exit Sub
End If
Loop

Exit_Kommandoknapp169_Click:
Exit Sub

Err_Kommandoknapp169_Click:
MsgBox Err.Description
Resume Exit_Kommandoknapp169_Click

End Sub
Click to expand...

1. Any "totals query" -- one that uses the GROUP BY clause, as yours
does -- will be nonupdatable (read-only), because each record returned
by the query may represent more than record in the source table(s).

2. If the query *were* updatable, you'd need to modify your code to
update it. You need to call the recordset's .Edit method before
modifying each record's fields, and its .Update method afterward to save
your changes:

With rs
Do Until .EOF
.Edit
!Historia = True
.Update
.MoveNext
Loop
.Close
End With

However, as I said, that won't work in this case, because your query is
not updatable.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Frukten said:
Any idea what I´m supposed to do? I have to have a summary on one of
the columns, cause if the summary isn´t ="0" i don´t wan't to check
the checkbox.

"Dirk Goldgar" skrev:
Frukten said:
This is what i'm trying to do. When i push a button, I want all
[historia] checkboxes in my query to be "checked". What happens is
that i get a message that says - " Cannot update, Database or Object
is writeprotected." (freely translated from swedish.)

I can't find any writeprotection. Please help.

Here is my code.

Private Sub Kommandoknapp169_Click()
On Error GoTo Err_Kommandoknapp169_Click

Dim fraga As String
Dim rs As DAO.Recordset
fraga = "SELECT Ink.Inkorder, Ink.[I lager], Ink.Historia,
Ink.Histdat, Ink.Antal AS bestallda, Sum(Korddata.Antal) AS salda,
[ink].[antal]-[korddata].[antal] AS kvar FROM Ink INNER JOIN
Korddata
ON Ink.Inkorder = Korddata.inkord GROUP BY Ink.Inkorder, Ink.[I
lager], Ink.Historia, Ink.Histdat, Ink.Antal,
[ink].[antal]-[korddata].[antal] HAVING (((Ink.[I lager])=Yes) AND
(([ink].[antal]-[korddata].[antal])<1));" Set rs =
CurrentDb().OpenRecordset(fraga) Do Until rs.EOF
If Not rs.BOF Then
rs!Historia = True
rs.MoveNext
Else
Exit Sub
End If
Loop

Exit_Kommandoknapp169_Click:
Exit Sub

Err_Kommandoknapp169_Click:
MsgBox Err.Description
Resume Exit_Kommandoknapp169_Click

End Sub
Click to expand...

1. Any "totals query" -- one that uses the GROUP BY clause, as yours
does -- will be nonupdatable (read-only), because each record
returned
by the query may represent more than record in the source table(s).

2. If the query *were* updatable, you'd need to modify your code to
update it. You need to call the recordset's .Edit method before
modifying each record's fields, and its .Update method afterward to
save your changes:

With rs
Do Until .EOF
.Edit
!Historia = True
.Update
.MoveNext
Loop
.Close
End With

However, as I said, that won't work in this case, because your query
is not updatable.
Click to expand...
Click to expand...

Did you leave out some of the code before? I don't see where you check
the value of the summed field before attempting to update the record.
I'm not sure I understand exactly what you want to do, partly because of
the language difference, of course, and partly because what you've
posted doesn't do what you've said anyway. In principle, though, it
should be possible to do the sort of update I *think* you want using a
single update query, without even opening a recordset. However, you
must use a subquery to identify the records you want to update. Put
your GROUP BY and Sum() function in the subquery and return just the
[Inkorder] field, and let the main query use the subquery as a criterion
on Ink.Inkorder. It *may* look something like this, but please
understand I'm not at all sure this is right.

UPDATE Ink SET Ink.Historia = True
WHERE Ink.Inkorder IN
(SELECT Ink.Inkorder
FROM Ink INNER JOIN Korddata
ON Ink.Inkorder = Korddata.inkord
GROUP BY Ink.Inkorder
HAVING (Ink.[I lager]=True)
AND (([ink].[antal]-[korddata].[antal])<1)
AND Sum(Korddata.Antal) = 0);

With luck, that will at least give you an idea how to accomplish what
you want.
 
Top