This is a multi-part message in MIME format.



------=_NextPart_000_000D_01C182BC.FC6F9C40

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Hello all,



Below is the code i'm using to tally stock levels from an order form, 

can anyone explain why i'm getting the run time error 3027 on the 

'rst.Edit' command. I'm being told the database or object is read-only.



Dim db As Database

Dim rst As Recordset

Dim str As String

Dim OrderNumber As Variant



OrderNumber =3D Me.OrderID



MsgBox "OrderNumber =3D " & OrderNumber 'Order number marker for 

reference







Set db =3D CurrentDb()

Set rst =3D db.OpenRecordset("SELECT tblOrderDetails.UnitID, 

tblOrderDetails.Quantity, AV_UNITS.NoInStock FROM AV_UNITS RIGHT JOIN 

tblOrderDetails ON AV_UNITS.UnitID =3D tblOrderDetails.UnitID GROUP BY 

tblOrderDetails.UnitID, tblOrderDetails.Quantity, AV_UNITS.NoInStock, 

tblOrderDetails.OrderID HAVING (((tblOrderDetails.OrderID) =3D " & 

OrderNumber & ")) ORDER BY tblOrderDetails.UnitID", dbOpenDynaset)



rst.MoveLast

rst.MoveFirst





Do While Not (rst.EOF)

    If (rst![Quantity] <=3D rst![NoInStock]) Then

        rst.Edit

        rst![NoInStock] =3D rst![NoInStock] - rst![Quantity]

        rst.update

        rst.MoveNext

    Else

        MsgBox "There is not enough stock of Unit ID" & rst("UnitID") & 

", to make this purchase."

    End If

Loop





The database properties are only marked as 'Archive' with the read only 

option unchecked.



I'm running Access2000 on NT4 SP6.



It's late and i'm stumped.



Can anyone help, please.



Thanks

Declan Burns

Is it possible that either or both tables may be "read-only"? The help file

says this sometimes happens.



Walt

I've checked in the 'Tools, Security, User and Group Permissions' menu and

both tables involved in the query are updateable. Do you know of any other

way to check the table attributes? There is nothing listed in the

'properties' of each table, when right clicking on the table.



Thanks

Declan



----- Original Message -----

From: "Walt Morgan" <wmorgan@s...>

To: "Access" <access@p...>

Sent: Wednesday, December 12, 2001 12:48 PM

Subject: [access] Re: Run Time Error 3027 generated with this code, why?





> Is it possible that either or both tables may be "read-only"? The help

file

> says this sometimes happens.

>

> Walt

>

>

>

Declan,



Can you run a modified query only on one table at set it to dbOpenTable,

rather than dbOpenDynaset. If it works, then the problem has been somewhat

isolated to the query itself. Just another thought.



Walt





I've checked in the 'Tools, Security, User and Group Permissions' menu and

both tables involved in the query are updateable. Do you know of any other

way to check the table attributes? There is nothing listed in the

'properties' of each table, when right clicking on the table.



Thanks

Declan

I don't believe it's ever possible to edit the results of a query that uses

GROUP BY--how would Access know which individual records in the underlying

table to change?  I'm afraid you're going to have to rethink your approach

here...



Cheers,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487 





-----Original Message-----

From: Declan Burns [mailto:declanburns@t...]

Sent: Tuesday, December 11, 2001 7:28 PM

To: Access

Subject: [access] Run Time Error 3027 generated with this code, why?





Hello all, 



Below is the code i'm using to tally stock levels from an order form, can

anyone explain why i'm getting the run time error 3027 on the 'rst.Edit'

command. I'm being told the database or object is read-only.



Dim db As Database

Dim rst As Recordset

Dim str As String

Dim OrderNumber As Variant



OrderNumber = Me.OrderID



MsgBox "OrderNumber = " & OrderNumber 'Order number marker for reference



 



Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT tblOrderDetails.UnitID,

tblOrderDetails.Quantity, AV_UNITS.NoInStock FROM AV_UNITS RIGHT JOIN

tblOrderDetails ON AV_UNITS.UnitID = tblOrderDetails.UnitID GROUP BY

tblOrderDetails.UnitID, tblOrderDetails.Quantity, AV_UNITS.NoInStock,

tblOrderDetails.OrderID HAVING (((tblOrderDetails.OrderID) = " & OrderNumber

& ")) ORDER BY tblOrderDetails.UnitID", dbOpenDynaset)



rst.MoveLast

rst.MoveFirst





Do While Not (rst.EOF)

    If (rst![Quantity] <= rst![NoInStock]) Then

        rst.Edit

        rst![NoInStock] = rst![NoInStock] - rst![Quantity]

        rst.update

        rst.MoveNext

    Else

        MsgBox "There is not enough stock of Unit ID" & rst("UnitID") & ",

to make this purchase."

    End If

Loop





The database properties are only marked as 'Archive' with the read only

option unchecked.



I'm running Access2000 on NT4 SP6.



It's late and i'm stumped.



Can anyone help, please.



Thanks

Declan Burns