Compile Error - Don't know WHY?

N

NEWER USER

I am trying to update a field 'Order' using the current Recordset of my
subform. I get a Compile error @ rs.Edit - Method or data member not found.
Can anyone tell me WHY and help me correct? Any help appreciated.

Private Sub QF_Click()
Dim rs As Recordset
Dim rsC As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.fsubSearch.Form.RecordSource)
If rs.RecordCount > 0 Then
If MsgBox("Order Quantity will be updated to Per Car Quantity." &
vbNewLine & vbNewLine & "Continue?", vbYesNo + vbQuestion, "Confirm.") =
vbYes Then
DoCmd.Hourglass True
rs.MoveFirst
While Not rs.EOF
rs.Edit
rs!Order = [Per Car]
rs.Update
rs.MoveNext
Wend
Me.fsubSearch.Form.Requery
DoCmd.Hourglass False
MsgBox rs.RecordCount & " record/s have been updated.",
vbInformation, "Finished."
End If

Else
MsgBox "No record to update.", vbInformation, "Message."
End If
rs.Close
Set db = Nothing
Me.fsubSearch.Form.Requery

End Sub
 
D

Dirk Goldgar

NEWER USER said:
I am trying to update a field 'Order' using the current Recordset of my
subform. I get a Compile error @ rs.Edit - Method or data member not
found.
Can anyone tell me WHY and help me correct? Any help appreciated.

Private Sub QF_Click()
Dim rs As Recordset
Dim rsC As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.fsubSearch.Form.RecordSource)
If rs.RecordCount > 0 Then
If MsgBox("Order Quantity will be updated to Per Car Quantity." &
vbNewLine & vbNewLine & "Continue?", vbYesNo + vbQuestion, "Confirm.") =
vbYes Then
DoCmd.Hourglass True
rs.MoveFirst
While Not rs.EOF
rs.Edit
rs!Order = [Per Car]
rs.Update
rs.MoveNext
Wend
Me.fsubSearch.Form.Requery
DoCmd.Hourglass False
MsgBox rs.RecordCount & " record/s have been updated.",
vbInformation, "Finished."
End If

Else
MsgBox "No record to update.", vbInformation, "Message."
End If
rs.Close
Set db = Nothing
Me.fsubSearch.Form.Requery

End Sub
Click to expand...


I suspect that you have a reference set to ActiveX Data Objects (ADO), as
well as one to Data Access Objects (DAO), and Access thinks your recordset
declarations are for ADO recordsets, which don't have an Edit method. If
you're not going to use ADO for anything, go to Tools -> References... and
uncheck the reference to ActiveX Data Objects.

If you *are* using ADO, or think you may in the future, disambiguate your
declarations by prefixing them with "DAO.":

Dim rs As DAO.Recordset
Dim rsC As DAO.Recordset
Dim db As DAO.Database

Technically, you don't need to disambiguate the Database declaration, since
ADO doesn't define a Database object, but better safe (and consistent) than
sorry.
 
N

NEWER USER

Adding DAO allowed the procedure to compile. However, when I run it, I now
get an error message Runtime error 3027; Cannot update. Database or object
is read only. When I open the Debug command, the rs.Edit line is highlighted
yellow as before. I opened the database as Open (made sure). I checked both
the form and subform and don't see any locks. Allow Edits, Additions,
Deletions are set to Yes. Any further ideas I might check.

Private Sub QF_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.fsubSearch.Form.RecordSource)
If rs.RecordCount > 0 Then
If MsgBox("Order Quantity will be updated to Per Car Quantity." &
vbNewLine & vbNewLine & "Continue?", vbYesNo + vbQuestion, "Confirm.") =
vbYes Then
DoCmd.Hourglass True
rs.MoveFirst
While Not rs.EOF
rs.Edit
rs!Order = [Per Car]
rs.Update
rs.MoveNext
Wend
Me.fsubSearch.Form.Requery
DoCmd.Hourglass False
MsgBox rs.RecordCount & " record/s have been updated.",
vbInformation, "Finished."
End If

Else
MsgBox "No record to update.", vbInformation, "Message."
End If
rs.Close
Set db = Nothing
Me.fsubSearch.Form.Requery

End Sub

Dirk Goldgar said:
NEWER USER said:
I am trying to update a field 'Order' using the current Recordset of my
subform. I get a Compile error @ rs.Edit - Method or data member not
found.
Can anyone tell me WHY and help me correct? Any help appreciated.

Private Sub QF_Click()
Dim rs As Recordset
Dim rsC As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.fsubSearch.Form.RecordSource)
If rs.RecordCount > 0 Then
If MsgBox("Order Quantity will be updated to Per Car Quantity." &
vbNewLine & vbNewLine & "Continue?", vbYesNo + vbQuestion, "Confirm.") =
vbYes Then
DoCmd.Hourglass True
rs.MoveFirst
While Not rs.EOF
rs.Edit
rs!Order = [Per Car]
rs.Update
rs.MoveNext
Wend
Me.fsubSearch.Form.Requery
DoCmd.Hourglass False
MsgBox rs.RecordCount & " record/s have been updated.",
vbInformation, "Finished."
End If

Else
MsgBox "No record to update.", vbInformation, "Message."
End If
rs.Close
Set db = Nothing
Me.fsubSearch.Form.Requery

End Sub
Click to expand...


I suspect that you have a reference set to ActiveX Data Objects (ADO), as
well as one to Data Access Objects (DAO), and Access thinks your recordset
declarations are for ADO recordsets, which don't have an Edit method. If
you're not going to use ADO for anything, go to Tools -> References... and
uncheck the reference to ActiveX Data Objects.

If you *are* using ADO, or think you may in the future, disambiguate your
declarations by prefixing them with "DAO.":

Dim rs As DAO.Recordset
Dim rsC As DAO.Recordset
Dim db As DAO.Database

Technically, you don't need to disambiguate the Database declaration, since
ADO doesn't define a Database object, but better safe (and consistent) than
sorry.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

.
Click to expand...
 
D

Dirk Goldgar

NEWER USER said:
Adding DAO allowed the procedure to compile. However, when I run it, I
now
get an error message Runtime error 3027; Cannot update. Database or
object
is read only. When I open the Debug command, the rs.Edit line is
highlighted
yellow as before. I opened the database as Open (made sure). I checked
both
the form and subform and don't see any locks. Allow Edits, Additions,
Deletions are set to Yes. Any further ideas I might check.

Private Sub QF_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.fsubSearch.Form.RecordSource)
If rs.RecordCount > 0 Then
If MsgBox("Order Quantity will be updated to Per Car Quantity." &
vbNewLine & vbNewLine & "Continue?", vbYesNo + vbQuestion, "Confirm.") =
vbYes Then
DoCmd.Hourglass True
rs.MoveFirst
While Not rs.EOF
rs.Edit
rs!Order = [Per Car]
rs.Update
rs.MoveNext
Wend
Me.fsubSearch.Form.Requery
DoCmd.Hourglass False
MsgBox rs.RecordCount & " record/s have been updated.",
vbInformation, "Finished."
End If

Else
MsgBox "No record to update.", vbInformation, "Message."
End If
rs.Close
Set db = Nothing
Me.fsubSearch.Form.Requery

End Sub
Click to expand...


What is the recordsource of fsubSearch? If it's a query, is that query
updatable?
 
N

NEWER USER

When my Main form 'frmSearch' opens, I have it coded as follows - On Open:
Me.fsubSearch.Form.RecordSource = "Select * from qryProduct where false"
Me.txtBrand.SetFocus

The subform 'fsubSearch' is blank. I manually fill in unbound fields on the
form and Apply Filter command to retrieve records in subform. I have set the
Recordset Type to Dynaset (Inconsistent Updates) on the subform's Property
Sheet. I can manually change the number quantity in the Order field of the
subform (could not when set to Dynaset only). Do I need something additional
in code to update this field, even though set to Inconsistent Updates? I
appreciate the help.

Dirk Goldgar said:
NEWER USER said:
Adding DAO allowed the procedure to compile. However, when I run it, I
now
get an error message Runtime error 3027; Cannot update. Database or
object
is read only. When I open the Debug command, the rs.Edit line is
highlighted
yellow as before. I opened the database as Open (made sure). I checked
both
the form and subform and don't see any locks. Allow Edits, Additions,
Deletions are set to Yes. Any further ideas I might check.

Private Sub QF_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.fsubSearch.Form.RecordSource)
If rs.RecordCount > 0 Then
If MsgBox("Order Quantity will be updated to Per Car Quantity." &
vbNewLine & vbNewLine & "Continue?", vbYesNo + vbQuestion, "Confirm.") =
vbYes Then
DoCmd.Hourglass True
rs.MoveFirst
While Not rs.EOF
rs.Edit
rs!Order = [Per Car]
rs.Update
rs.MoveNext
Wend
Me.fsubSearch.Form.Requery
DoCmd.Hourglass False
MsgBox rs.RecordCount & " record/s have been updated.",
vbInformation, "Finished."
End If

Else
MsgBox "No record to update.", vbInformation, "Message."
End If
rs.Close
Set db = Nothing
Me.fsubSearch.Form.Requery

End Sub
Click to expand...


What is the recordsource of fsubSearch? If it's a query, is that query
updatable?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

NEWER USER said:
When my Main form 'frmSearch' opens, I have it coded as follows - On Open:
Me.fsubSearch.Form.RecordSource = "Select * from qryProduct where false"
Me.txtBrand.SetFocus

The subform 'fsubSearch' is blank. I manually fill in unbound fields on
the
form and Apply Filter command to retrieve records in subform. I have set
the
Recordset Type to Dynaset (Inconsistent Updates) on the subform's Property
Sheet. I can manually change the number quantity in the Order field of
the
subform (could not when set to Dynaset only). Do I need something
additional
in code to update this field, even though set to Inconsistent Updates? I
appreciate the help.
Click to expand...


It sounds like qryProduct has some updatability issues, which circumvented
on the form by setting the form's recordset type to "Dynaset (Inconsistent
Updates)". Your code is opening a totally separate recordset on the same
recordsource, so you would need to specify inconsistent updates for that new
recordset:

Set rs = db.OpenRecordset( _
Me.fsubSearch.Form.RecordSource, _
dbOpenDynaset, dbInconsistent)

However, I'm not sure that you need or should open a separate recordset,
when you have the subform's recordset right there at your disposal. Why not
just use that:

'------ start of revised example code ------
Private Sub QF_Click()

With Me.fsubSearch.Form.RecordsetClone

If .RecordCount > 0 Then

If MsgBox( _
"Order Quantity will be updated to Per Car Quantity." &
_
vbNewLine & vbNewLine & "Continue?", _
vbYesNo + vbQuestion, _
"Confirm.") _
= vbYes _
Then
DoCmd.Hourglass True

.MoveFirst
While Not .EOF
.Edit
!Order = [Per Car]
.Update
.MoveNext
Wend

DoCmd.Hourglass False
MsgBox _
.RecordCount & " record/s have been updated.", _
vbInformation, _
"Finished."
End If

Else
MsgBox "No record to update.", vbInformation, "Message."
End If

End With

End Sub
'------ end of revised example code ------

Incidentally, doing it that way would avoid any concern about the fact that
any filter applied to the subform would not be reflected in the subform's
RecordSource property, which I was worried about when I read your
explanation.
 
N

NEWER USER

YOUR CODE worked fantastic. I had to add an exclamation befrore - ![Per
Car]. Thank you SO MUCH for your persistance and patience.

Dirk Goldgar said:
NEWER USER said:
When my Main form 'frmSearch' opens, I have it coded as follows - On Open:
Me.fsubSearch.Form.RecordSource = "Select * from qryProduct where false"
Me.txtBrand.SetFocus

The subform 'fsubSearch' is blank. I manually fill in unbound fields on
the
form and Apply Filter command to retrieve records in subform. I have set
the
Recordset Type to Dynaset (Inconsistent Updates) on the subform's Property
Sheet. I can manually change the number quantity in the Order field of
the
subform (could not when set to Dynaset only). Do I need something
additional
in code to update this field, even though set to Inconsistent Updates? I
appreciate the help.
Click to expand...


It sounds like qryProduct has some updatability issues, which circumvented
on the form by setting the form's recordset type to "Dynaset (Inconsistent
Updates)". Your code is opening a totally separate recordset on the same
recordsource, so you would need to specify inconsistent updates for that new
recordset:

Set rs = db.OpenRecordset( _
Me.fsubSearch.Form.RecordSource, _
dbOpenDynaset, dbInconsistent)

However, I'm not sure that you need or should open a separate recordset,
when you have the subform's recordset right there at your disposal. Why not
just use that:

'------ start of revised example code ------
Private Sub QF_Click()

With Me.fsubSearch.Form.RecordsetClone

If .RecordCount > 0 Then

If MsgBox( _
"Order Quantity will be updated to Per Car Quantity." &
_
vbNewLine & vbNewLine & "Continue?", _
vbYesNo + vbQuestion, _
"Confirm.") _
= vbYes _
Then
DoCmd.Hourglass True

.MoveFirst
While Not .EOF
.Edit
!Order = [Per Car]
.Update
.MoveNext
Wend

DoCmd.Hourglass False
MsgBox _
.RecordCount & " record/s have been updated.", _
vbInformation, _
"Finished."
End If

Else
MsgBox "No record to update.", vbInformation, "Message."
End If

End With

End Sub
'------ end of revised example code ------

Incidentally, doing it that way would avoid any concern about the fact that
any filter applied to the subform would not be reflected in the subform's
RecordSource property, which I was worried about when I read your
explanation.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

coding help - please 9
traversing a large recordset 4
Addnew throwing error 3027 1
Data type conversion error 0
Compile Error ?? 0
Compile Error ?? 2
FindFirst 3
Cannot update DAO Recordset 6

Top