?
Solved

Edit Table Accessed thru Inner Join SQL

Posted on 2005-05-17
5
Medium Priority
?
327 Views
Last Modified: 2008-02-01
Hello,

Here is the code I am using to return a recordset ... all works fine till I try to update the table ... can anyone help?

Error 3027  Cannot update.  Database or object is read-only.

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo HandleError
   
    Dim rs As DAO.Recordset
   
    Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM tblMain INNER JOIN tblUserProfile ON tblMain.PENDCODE = tblUserProfile.PendID WHERE datatype = '" & Me.OpenArgs & "'")

    If rs.RecordCount = 0 Then
        MsgBox "No more records.", vbOKOnly, "No More Records"
        DoCmd.OpenForm ("frmMainMenu")
        DoCmd.Close acForm, "frmedit", acSaveNo
        Cancel = True
        Exit Sub
    End If
   
    With rs
        .Edit  (THIS IS WHERE THE CODE BREAK OCCURS)
        .Fields("BeingProcessed").Value = True
        .Update
       
        m_RecordID = rs("ID").Value
        m_Argument = Me.OpenArgs

Thanks in advance for your help.
0
Comment
Question by:NEWJACK
  • 3
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 14021138
Try again, with this one line changed...

Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM tblMain INNER JOIN tblUserProfile ON tblMain.PENDCODE = tblUserProfile.PendID WHERE datatype = '" & Me.OpenArgs & "'"), adOpenDynamic, adLockOptimistic

-Jim
0
 

Author Comment

by:NEWJACK
ID: 14021187
It doesnt like the way its written

I get an error .. Compile Error.  Expected end of sentence.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 375 total points
ID: 14021211
Oops...

Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM tblMain INNER JOIN tblUserProfile ON tblMain.PENDCODE = tblUserProfile.PendID WHERE datatype = '" & Me.OpenArgs & "'", adOpenDynamic, adLockOptimistic)
0
 

Author Comment

by:NEWJACK
ID: 14021250
Thats not it .. same as before

Error 3027  Cannot update.  Database or object is read-only.
0
 

Author Comment

by:NEWJACK
ID: 14072235
Please delete question.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question