I have 2 tables and from them I do a query called "Query_Dates". There, I created 4 calculated fields which compares fields of the two tables previously mentioned.
What I am trying to do is everytime the calculated fields are equal to "Diff", gets the value from the second table (ADHOC) and and puts it in the 1st table (Master_Table). In total there are 4 fields I would change depending on other 4 fields called (CheckRR, CheckQual, CheckProd, CheckCap).
The problem is that it gives me an error 3027, object is read-only. Could anybody help me or give me any feedback?. Thank you in advance.
Below, you can find the code.
Private Sub Comando27_Click()
Dim rst As DAO.Recordset
Dim rsd As DAO.Recordset
Dim supplierName As String
Set rsd = Application.CurrentDb.OpenRecordset("Query_Dates", dbOpenDynaset)
Set rst = Application.CurrentDb.OpenRecordset("Master_Table", dbOpenDynaset)
retry: supplierName = InputBox("Please enter the Supplier Name", _
"Information Required")
'MsgBox "Supplier requested: " & supplierName
valid = MsgBox("Supplier Requested = " & supplierName & "??", vbYesNoCancel, "Validation")
If valid = 7 Then
GoTo retry
ElseIf valid = 2 Then
GoTo salir
End If
rsd.MoveFirst
Do While Not rsd.EOF
If rsd![Supplier Name] = supplierName Then
If rsd![CheckRR] = "Diff" And rsd![ADHOC_Run at Rate Dt] <> Empty Then
MsgBox rsd![Part Base] & rsd![Part Suffix]
MsgBox rst![Part Base] & rst![Part Suffix]
rsd.Edit
rsd![Run at Rate Dt] = rsd![ADHOC_Run at Rate Dt]
rsd.Update
End If
If rsd![CheckQual] = "Diff" And rsd![ADHOC_Qual Verif Dt] <> Empty Then
rst.Edit
rst![Master_Table_Qual Verif Dt] = rsd![ADHOC_Qual Verif Dt]
rst.Update
End If
If rsd![CheckProd] = "Diff" And rsd![ADHOC_Prod Verif Dt] <> Empty Then
rst.Edit
rst![Master_Table_Prod Verif Dt] = rsd![ADHOC_Prod Verif Dt]
rst.Update
End If
If rsd![CheckCap] = "Diff" And rsd![ADHOC_Prod Verif Dt] <> Empty Then
rst.Edit
rst![Master_Table_Cap Verif Dt] = rsd![ADHOC_Cap Verif Dt]
rst.Update
End If
End If
rsd.MoveNext
Loop
salir: rsd.Close
Set rsd = Nothing
rst.Close
Set rst = Nothing
End Sub
What I am trying to do is everytime the calculated fields are equal to "Diff", gets the value from the second table (ADHOC) and and puts it in the 1st table (Master_Table). In total there are 4 fields I would change depending on other 4 fields called (CheckRR, CheckQual, CheckProd, CheckCap).
The problem is that it gives me an error 3027, object is read-only. Could anybody help me or give me any feedback?. Thank you in advance.
Below, you can find the code.
Private Sub Comando27_Click()
Dim rst As DAO.Recordset
Dim rsd As DAO.Recordset
Dim supplierName As String
Set rsd = Application.CurrentDb.OpenRecordset("Query_Dates", dbOpenDynaset)
Set rst = Application.CurrentDb.OpenRecordset("Master_Table", dbOpenDynaset)
retry: supplierName = InputBox("Please enter the Supplier Name", _
"Information Required")
'MsgBox "Supplier requested: " & supplierName
valid = MsgBox("Supplier Requested = " & supplierName & "??", vbYesNoCancel, "Validation")
If valid = 7 Then
GoTo retry
ElseIf valid = 2 Then
GoTo salir
End If
rsd.MoveFirst
Do While Not rsd.EOF
If rsd![Supplier Name] = supplierName Then
If rsd![CheckRR] = "Diff" And rsd![ADHOC_Run at Rate Dt] <> Empty Then
MsgBox rsd![Part Base] & rsd![Part Suffix]
MsgBox rst![Part Base] & rst![Part Suffix]
rsd.Edit
rsd![Run at Rate Dt] = rsd![ADHOC_Run at Rate Dt]
rsd.Update
End If
If rsd![CheckQual] = "Diff" And rsd![ADHOC_Qual Verif Dt] <> Empty Then
rst.Edit
rst![Master_Table_Qual Verif Dt] = rsd![ADHOC_Qual Verif Dt]
rst.Update
End If
If rsd![CheckProd] = "Diff" And rsd![ADHOC_Prod Verif Dt] <> Empty Then
rst.Edit
rst![Master_Table_Prod Verif Dt] = rsd![ADHOC_Prod Verif Dt]
rst.Update
End If
If rsd![CheckCap] = "Diff" And rsd![ADHOC_Prod Verif Dt] <> Empty Then
rst.Edit
rst![Master_Table_Cap Verif Dt] = rsd![ADHOC_Cap Verif Dt]
rst.Update
End If
End If
rsd.MoveNext
Loop
salir: rsd.Close
Set rsd = Nothing
rst.Close
Set rst = Nothing
End Sub