471,580 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,580 software developers and data experts.

"Cannot update, databse or object may be read only"

Hi

I was wondering if anyone could help i have picked this database up
from a
designer who has now left the company. The code is a couple of years
old.

On click of a button it open a recordset which is a query loops until
it
finds the required people and then trys to update one field in the
query. I
then get the error message "Cannot update database or object is read
only".
But this isn't the case.

If i open the query and manually do want the code is trying to do i
works
perfectly and accepts the changes. This problem is driving me bananas
and i
can't fix it.

Would appreciate any help i have posted the code below sorry for the
length.

Private Sub btnAccept_Click()
On Error GoTo Err_btnAccept_Click

Dim db As Database
Dim rstProspects As Recordset
Dim HEIGHT_MAX, HEIGHT_MIN As Integer

Dim txtB_Got As Boolean
Dim txtB_Mind As String

DoCmd.Echo False
DoCmd.SetWarnings False

DoCmd.OpenQuery "qryDelete_Not_On_Hold"
DoCmd.OpenQuery "qryNot_On_Hold_1"
DoCmd.OpenQuery "qryNot_On_Hold_2"

DoCmd.SetWarnings True
DoCmd.Echo True

Me.Visible = False

If IsNull(txtAGE_MIN) Then

txtAGE_MIN = 18

End If

If IsNull(txtAGE_MAX) Then

txtAGE_MAX = 99

End If

If IsNull(txtHEIGHT_MIN) Then

HEIGHT_MIN = 48
txtHEIGHT_MIN = 48

Else

HEIGHT_MIN = txtHEIGHT_MIN

End If

If IsNull(txtHEIGHT_MAX) Then

HEIGHT_MAX = 99
txtHEIGHT_MAX = 99

Else

HEIGHT_MAX = txtHEIGHT_MAX

End If

If IsNull(txtSINGLE) Then

txtSINGLE = "Don't Care"

End If

If IsNull(txtSEPARATED) Then

txtSEPARATED = "Don't Care"

End If

If IsNull(txtDIVORCED) Then

txtDIVORCED = "Don't Care"

End If

If IsNull(txtWIDOWED) Then

txtWIDOWED = "Don't Care"

End If

' Matching Process

Set db = CurrentDb()

Set rstProspects = db.OpenRecordset("qryMembers_Matching")

With rstProspects

.MoveFirst

Do Until .EOF

'If Nz(!OWN_DEPENDANTS, 0) = 0 Then

'txtB_Got = False

'Else

'txtB_Got = True

'End If

'txtB_Mind = !IDEAL_CHILDREN

If !SEX = txtOpposite_Sex Then

If txtSINGLE = "Don't Care" Or _
(txtSINGLE = "Must be" And !MARITAL_STATUS = "Single")
Or _
(txtSINGLE = "Must not be" And !MARITAL_STATUS <>
"Single")
Then

If txtSEPARATED = "Don't Care" Or _
(txtSEPARATED = "Must be" And !MARITAL_STATUS =
"Separated")
Or _
(txtSEPARATED = "Must not be" And !MARITAL_STATUS <>
"Separated") Then

If txtDIVORCED = "Don't Care" Or _
(txtDIVORCED = "Must be" And !MARITAL_STATUS =
"Divorced") Or _
(txtDIVORCED = "Must not be" And !MARITAL_STATUS <>
"Divorced") Then

If txtWIDOWED = "Don't Care" Or _
(txtWIDOWED = "Must be" And !MARITAL_STATUS = "Widowed")
Or _
(txtWIDOWED = "Must not be" And !MARITAL_STATUS <>
"Widowed")
Then

If !DOB > DateAdd("yyyy", -(txtAGE_MAX + 1), Date) Then
If !DOB <= DateAdd("yyyy", -txtAGE_MIN, Date) Then
If !Total_Height_Inches <= HEIGHT_MAX Then

If !Total_Height_Inches >= HEIGHT_MIN Then

If DCount("[CLIENT_A]", "MATCH_HISTORY", _
"[CLIENT_A] = " & txtINTRO_OCC_NO & " AND " & _
"[CLIENT_B] = " & !MEMBER_NO) = 0 Then
If DCount("[CLIENT_A]", "qryAll_B_Matched", _
"[CLIENT_B] = " & txtINTRO_OCC_NO & " AND " & _
"[CLIENT_A] = " & !MEMBER_NO) = 0 Then

If (txtA_Smoker = False And !SMOKER = False) Or _
(txtA_Smoker = True And !IDEAL_SMOKING <> "Never") Or _
(txtA_Ideal_Smoker = True And !SMOKER = False) Or _
(txtA_Ideal_Smoker = False And !SMOKER = True) Then

If (txtA_Got = False And txtA_Mind <> "Yes") Or _
(Nz(!OWN_DEPENDANTS, 0) = 0 And !IDEAL_CHILDREN <>
"Yes") Or _
(txtA_Got = False And txtA_Mind = "Yes" And _
Nz(!OWN_DEPENDANTS, 0) = 0 And !IDEAL_CHILDREN = "Yes")
Or _
(txtA_Got = True And txtA_Mind = "No" And _
Nz(!OWN_DEPENDANTS, 0) > 0 And !IDEAL_CHILDREN = "No")
Or _
(txtA_Got = True And txtA_Mind = "Maybe" And _
Nz(!OWN_DEPENDANTS, 0) > 0 And !IDEAL_CHILDREN =
"Maybe") Or _
(txtA_Got = True And txtA_Mind = "Maybe" And _
Nz(!OWN_DEPENDANTS, 0) > 0 And !IDEAL_CHILDREN = "No")
Or _
(Nz(!OWN_DEPENDANTS, 0) > 0 And !IDEAL_CHILDREN =
"Maybe" And _
txtA_Got = True And txtA_Mind = "No") Then

.Edit

!PICKED = True

.Update

End If ' Children

End If ' Smoker

End If ' Client B No Match

End If ' Client A No Match

End If ' Minimum Height

End If ' Maximum Height

End If ' Minimum Age

End If ' Maximum Age

End If ' Widowed

End If ' Divorced

End If ' Separated

End If ' Single

End If ' Opposite sex

.MoveNext

Loop

.Close

End With

Forms!frmIntroductions!fsubMatching_Prospects.Requ ery
Forms!frmIntroductions!pgProspects.SetFocus

Exit_btnAccept_Click:
Exit Sub

Err_btnAccept_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnAccept_Click

End Sub

Thanks

Gillian

Apr 10 '06 #1
1 1939
Br
So what line does the error occur on???

I assume the .update ?

Perhaps try....

Set rstProspects = db.OpenRecordset("qryMembers_Matching", dbOpenDynaset)

It sounds like your recordset is not updateable.

Or is it happening when calling one of the other queries?

Gilz wrote:
Hi

I was wondering if anyone could help i have picked this database up
from a
designer who has now left the company. The code is a couple of years
old.

On click of a button it open a recordset which is a query loops until
it
finds the required people and then trys to update one field in the
query. I
then get the error message "Cannot update database or object is read
only".
But this isn't the case.

If i open the query and manually do want the code is trying to do i
works
perfectly and accepts the changes. This problem is driving me bananas
and i
can't fix it.

Would appreciate any help i have posted the code below sorry for the
length.

Private Sub btnAccept_Click()
On Error GoTo Err_btnAccept_Click

Dim db As Database
Dim rstProspects As Recordset
Dim HEIGHT_MAX, HEIGHT_MIN As Integer

Dim txtB_Got As Boolean
Dim txtB_Mind As String

DoCmd.Echo False
DoCmd.SetWarnings False

DoCmd.OpenQuery "qryDelete_Not_On_Hold"
DoCmd.OpenQuery "qryNot_On_Hold_1"
DoCmd.OpenQuery "qryNot_On_Hold_2"

DoCmd.SetWarnings True
DoCmd.Echo True

Me.Visible = False

If IsNull(txtAGE_MIN) Then

txtAGE_MIN = 18

End If

If IsNull(txtAGE_MAX) Then

txtAGE_MAX = 99

End If

If IsNull(txtHEIGHT_MIN) Then

HEIGHT_MIN = 48
txtHEIGHT_MIN = 48

Else

HEIGHT_MIN = txtHEIGHT_MIN

End If

If IsNull(txtHEIGHT_MAX) Then

HEIGHT_MAX = 99
txtHEIGHT_MAX = 99

Else

HEIGHT_MAX = txtHEIGHT_MAX

End If

If IsNull(txtSINGLE) Then

txtSINGLE = "Don't Care"

End If

If IsNull(txtSEPARATED) Then

txtSEPARATED = "Don't Care"

End If

If IsNull(txtDIVORCED) Then

txtDIVORCED = "Don't Care"

End If

If IsNull(txtWIDOWED) Then

txtWIDOWED = "Don't Care"

End If

' Matching Process

Set db = CurrentDb()

Set rstProspects = db.OpenRecordset("qryMembers_Matching")

With rstProspects

.MoveFirst

Do Until .EOF

'If Nz(!OWN_DEPENDANTS, 0) = 0 Then

'txtB_Got = False

'Else

'txtB_Got = True

'End If

'txtB_Mind = !IDEAL_CHILDREN

If !SEX = txtOpposite_Sex Then

If txtSINGLE = "Don't Care" Or _
(txtSINGLE = "Must be" And !MARITAL_STATUS = "Single")
Or _
(txtSINGLE = "Must not be" And !MARITAL_STATUS <>
"Single")
Then

If txtSEPARATED = "Don't Care" Or _
(txtSEPARATED = "Must be" And !MARITAL_STATUS =
"Separated")
Or _
(txtSEPARATED = "Must not be" And !MARITAL_STATUS <>
"Separated") Then

If txtDIVORCED = "Don't Care" Or _
(txtDIVORCED = "Must be" And !MARITAL_STATUS =
"Divorced") Or _
(txtDIVORCED = "Must not be" And !MARITAL_STATUS <>
"Divorced") Then

If txtWIDOWED = "Don't Care" Or _
(txtWIDOWED = "Must be" And !MARITAL_STATUS = "Widowed")
Or _
(txtWIDOWED = "Must not be" And !MARITAL_STATUS <>
"Widowed")
Then

If !DOB > DateAdd("yyyy", -(txtAGE_MAX + 1), Date) Then
If !DOB <= DateAdd("yyyy", -txtAGE_MIN, Date) Then
If !Total_Height_Inches <= HEIGHT_MAX Then

If !Total_Height_Inches >= HEIGHT_MIN Then

If DCount("[CLIENT_A]", "MATCH_HISTORY", _
"[CLIENT_A] = " & txtINTRO_OCC_NO & " AND " & _
"[CLIENT_B] = " & !MEMBER_NO) = 0 Then
If DCount("[CLIENT_A]", "qryAll_B_Matched", _
"[CLIENT_B] = " & txtINTRO_OCC_NO & " AND " & _
"[CLIENT_A] = " & !MEMBER_NO) = 0 Then

If (txtA_Smoker = False And !SMOKER = False) Or _
(txtA_Smoker = True And !IDEAL_SMOKING <> "Never") Or _
(txtA_Ideal_Smoker = True And !SMOKER = False) Or _
(txtA_Ideal_Smoker = False And !SMOKER = True) Then

If (txtA_Got = False And txtA_Mind <> "Yes") Or _
(Nz(!OWN_DEPENDANTS, 0) = 0 And !IDEAL_CHILDREN <>
"Yes") Or _
(txtA_Got = False And txtA_Mind = "Yes" And _
Nz(!OWN_DEPENDANTS, 0) = 0 And !IDEAL_CHILDREN = "Yes")
Or _
(txtA_Got = True And txtA_Mind = "No" And _
Nz(!OWN_DEPENDANTS, 0) > 0 And !IDEAL_CHILDREN = "No")
Or _
(txtA_Got = True And txtA_Mind = "Maybe" And _
Nz(!OWN_DEPENDANTS, 0) > 0 And !IDEAL_CHILDREN =
"Maybe") Or _
(txtA_Got = True And txtA_Mind = "Maybe" And _
Nz(!OWN_DEPENDANTS, 0) > 0 And !IDEAL_CHILDREN = "No")
Or _
(Nz(!OWN_DEPENDANTS, 0) > 0 And !IDEAL_CHILDREN =
"Maybe" And _
txtA_Got = True And txtA_Mind = "No") Then

.Edit

!PICKED = True

.Update

End If ' Children

End If ' Smoker

End If ' Client B No Match

End If ' Client A No Match

End If ' Minimum Height

End If ' Maximum Height

End If ' Minimum Age

End If ' Maximum Age

End If ' Widowed

End If ' Divorced

End If ' Separated

End If ' Single

End If ' Opposite sex

.MoveNext

Loop

.Close

End With

Forms!frmIntroductions!fsubMatching_Prospects.Requ ery
Forms!frmIntroductions!pgProspects.SetFocus

Exit_btnAccept_Click:
Exit Sub

Err_btnAccept_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnAccept_Click

End Sub

Thanks

Gillian


--
regards,

Br@dley
Apr 10 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by hao | last post: by
3 posts views Thread by Kevin Pedersen | last post: by
1 post views Thread by marknewbery | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
1 post views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.