Solved Can not update. Database or Object is read-only

[Ashfaque]

Hi,

My main form has a subform that displaying relevant records once I select record from combo on min form. My subform Data Entry has set to No.

There is a fields on main form called PWDmgr after updating which I am getting relevant department head name and placing on main form.

The main question is my code has stuck on rst.edit saying "Can not update. Database or Object is read-only"

Private Sub OTApprovedBy_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select * from T_Dept where CDepartment=' " & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr =  '" & Forms!F_EmpOTHeader!OTApprovedBy & "'", dbOpenDynaset, dbSeeChanges)
If rst.BOF = False And rst.EOF = False Then
MsgBox ("NO Match Found")
Else
rst.Edit
Me.EmpHOD = rst!DeptManager
End If
rst.Close
Set db = Nothing
End Sub

If I Re-set subform property Data Entry to No then no relevant record is being fetched in sub-form.

Since I deployed my BE o SQl Server, it looks server tables are giving lots of trouble to me. Both tables has primary key as advised by honorable members of this forum

 

[Minty]

This is the wrong way around.

Me.EmpHOD = rst!DeptManager

You are trying to set the forms EmpHOD to the recordset value, I'm guessing the form isn't editable.
I also think you should research using recordsets a little more. In this case I suspect a DLookup would have been sufficient, and an update if required.

 

[Ashfaque]

This is the wrong way around.

Me.EmpHOD = rst!DeptManager

You are trying to set the forms EmpHOD to the recordset value, I'm guessing the form isn't editable.
I also think you should research using recordsets a little more. In this case I suspect a DLookup would have been sufficient, and an update if required.

Thanks Minty,
But if I use Dlookup then how I can get EmpHOD from that tbl T_Dept.

Using record set method look a bit lengthy but little easy for me.

 

[Minty]

I'm not entirely sure I understand the process flow here - it seems a bit convoluted but the same can be achieved with something like

Dim sManager as String
Dim strSql as string

sManager = "" & DLookup("DeptManager","TDept","CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr =  '" & Forms!F_EmpOTHeader!OTApprovedBy & "'"

If Len(sManager) > 0 Then 
    strSql = "Update T_Dept SET DeptManager = '" & Me.EmpHOD & "' WHERE CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr =  '" & Forms!F_EmpOTHeader!OTApprovedBy & "'"
    Debug.Print strSql
    CurrentDb.Execute strSql, dbSeeChanges
End If

 

[Ashfaque]

I'm not entirely sure I understand the process flow here - it seems a bit convoluted but the same can be achieved with something like

Dim sManager as String
Dim strSql as string

sManager = "" & DLookup("DeptManager","TDept","CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr =  '" & Forms!F_EmpOTHeader!OTApprovedBy & "'"

If Len(sManager) > 0 Then
    strSql = "Update T_Dept SET DeptManager = '" & Me.EmpHOD & "' WHERE CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr =  '" & Forms!F_EmpOTHeader!OTApprovedBy & "'"
    Debug.Print strSql
    CurrentDb.Execute strSql, dbSeeChanges
End If

Showing Syntax error in

sManager = "" & DLookup("DeptManager","TDept","CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr = '" & Forms!F_EmpOTHeader!OTApprovedBy & "'"

Dont understand why recordset can work with one condition either of CDepartment or PWDmgr. But if I place 2 conditions it is not working.

 

[Ashfaque]

Dim sManager As String
Dim strSql As String

sManager = "" & DLookup("DeptManager", "T_Dept", "CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr = '" & Forms!F_EmpOTHeader!OTApprovedBy & "'")

If Len(sManager) > 0 Then
strSql = "Update T_Dept SET DeptManager = '" & Me.EmpHOD & "' WHERE CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr = '" & Forms!F_EmpOTHeader!OTApprovedBy & "'"
Debug.Print strSql
CurrentDb.Execute strSql, dbSeeChanges
End If

Error corrected in above but doesn't bring data...

Dont understand why recordset can work with one condition either of CDepartment or PWDmgr. But if I place 2 conditions it is not working.

 

[Minty]

Is this code being run from the form F_EmpOTHeader

Dim sManager As String
Dim strSql As String


Debug.Print "My Values are: " &  Me.EmpDept , Me.OTApprovedBy

sManager = "" & DLookup("DeptManager", "T_Dept", "CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr = '" & Forms!F_EmpOTHeader!OTApprovedBy & "'")

Debug.Print "sManager: " sManager

If Len(sManager) > 0 Then
    strSql = "Update T_Dept SET DeptManager = '" & Me.EmpHOD & "' WHERE CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr = '" & Forms!F_EmpOTHeader!OTApprovedBy & "'"
    Debug.Print strSql
    CurrentDb.Execute strSql, dbSeeChanges
End If

?

If so a simple debug will help, and there is no need to use the Forms! referencing in code run from that form.

 

[Ashfaque]

Is this code being run from the form F_EmpOTHeader

Dim sManager As String
Dim strSql As String


Debug.Print "My Values are: " &  Me.EmpDept , Me.OTApprovedBy

sManager = "" & DLookup("DeptManager", "T_Dept", "CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr = '" & Forms!F_EmpOTHeader!OTApprovedBy & "'")

Debug.Print "sManager: " sManager

If Len(sManager) > 0 Then
    strSql = "Update T_Dept SET DeptManager = '" & Me.EmpHOD & "' WHERE CDepartment = '" & Forms!F_EmpOTHeader!EmpDept & "' And PWDmgr = '" & Forms!F_EmpOTHeader!OTApprovedBy & "'"
    Debug.Print strSql
    CurrentDb.Execute strSql, dbSeeChanges
End If

?

If so a simple debug will help, and there is no need to use the Forms! referencing in code run from that form.

Done..it Thanks......

 

[Gasman]

If I have more than one criteria or it is to me, somewhat complicated, as it appears to you as well, then I put the criteria into a string variable and then I can debug.print it to make sure it is correct.?

Applies to DlookUp() criteria and can also be used in SQL criteria?

 

[Ashfaque]

Now started giving error on

CurrentDb.Execute strSql, dbOpenDynaset, dbSeeChanges

Wrong number of arguments or invalid property assignment on CurrentDb.Execute

 

[Gasman]

So show the result of the debug.print?

 

[Ashfaque]

So show the result of the debug.print?

Compile Error Expected: Expression

 

[Gasman]

Compile Error Expected: Expression

No , show the output of the Debug.Print strSql from the Immediate Window

 

[Ashfaque]

When I placed
?Debug.Print strSql
in immediate window the dialog msg appeared

 

[Gasman]

No
You have debug.print just before you try an execute the SQL.
The reason it is there is to check what it holds?
The result will already be in the immediate window.

Set a breakpoint on the If len() statement and then press F8 until you get to the execute line. DO NOT press F8 again. Switch to the immediate window *COPY* and paste the output back here.

Perhaps look at my Debugging link in my siganture to get the basics. learning just simple debugging techniques will save you a lot of heartache and pay for itself time and time again.?

 

[Minty]

if you want to print out strSQL just use
? strSQL

 

[Minty]

Also

CurrentDb.Execute strSql, dbOpenDynaset, dbSeeChanges

It's an action query dbOpenDynaset is unnecessary and maybe causing an error.

 

[Ashfaque]

if you want to print out strSQL just use
? strSQL

Immidiate Window presents :

My Values are: ADMINISTRATION S321
sManager: SKN
Update T_Dept SET DeptManager = '' WHERE CDepartment = 'ADMINISTRATION' And PWDmgr = 'S321'

 

[Gasman]

Well there you go, the problem should be self evident now?

There is little point issuing debug.print statements unless you are going to take notice of them?

 

[Ashfaque]

Here is another pic....