Solved Can not update. Database or Object is read-only (1 Viewer)

Ashfaque

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
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"
Code:
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:LOL:. Both tables has primary key as advised by honorable members of this forum
 

Minty

AWF VIP
Local time
Today, 16:35
Joined
Jul 26, 2013
Messages
10,354
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

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
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

AWF VIP
Local time
Today, 16:35
Joined
Jul 26, 2013
Messages
10,354
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
SQL:
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

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
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
SQL:
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

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
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

AWF VIP
Local time
Today, 16:35
Joined
Jul 26, 2013
Messages
10,354
Is this code being run from the form F_EmpOTHeader
SQL:
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

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
Is this code being run from the form F_EmpOTHeader
SQL:
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

Enthusiastic Amateur
Local time
Today, 16:35
Joined
Sep 21, 2011
Messages
14,044
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

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
Now started giving error on

Code:
CurrentDb.Execute strSql, dbOpenDynaset, dbSeeChanges

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

Gasman

Enthusiastic Amateur
Local time
Today, 16:35
Joined
Sep 21, 2011
Messages
14,044
So show the result of the debug.print? :(
 

Ashfaque

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
When I placed
?Debug.Print strSql
in immediate window the dialog msg appeared
 

Attachments

  • error.png
    error.png
    25.2 KB · Views: 373

Gasman

Enthusiastic Amateur
Local time
Today, 16:35
Joined
Sep 21, 2011
Messages
14,044
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

AWF VIP
Local time
Today, 16:35
Joined
Jul 26, 2013
Messages
10,354
if you want to print out strSQL just use
? strSQL
 

Minty

AWF VIP
Local time
Today, 16:35
Joined
Jul 26, 2013
Messages
10,354
Also

CurrentDb.Execute strSql, dbOpenDynaset, dbSeeChanges

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

Ashfaque

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
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

Enthusiastic Amateur
Local time
Today, 16:35
Joined
Sep 21, 2011
Messages
14,044
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? :unsure:
 

Ashfaque

Student
Local time
Today, 22:05
Joined
Sep 6, 2004
Messages
894
Here is another pic....
 

Attachments

  • error.png
    error.png
    24.9 KB · Views: 364

Users who are viewing this thread

Top Bottom