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

"NEWER USER" <(E-Mail Removed)> wrote in message
news02E110A-F30C-4039-BD3E-(E-Mail Removed)...
>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


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)

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" wrote:

> "NEWER USER" <(E-Mail Removed)> wrote in message
> news02E110A-F30C-4039-BD3E-(E-Mail Removed)...
> >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
>
>
> 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)
>
> .
>

"NEWER USER" <(E-Mail Removed)> wrote in message
news:BA6CBC84-F5D7-4EF4-B76C-(E-Mail Removed)...
> 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


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)

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" wrote:

> "NEWER USER" <(E-Mail Removed)> wrote in message
> news:BA6CBC84-F5D7-4EF4-B76C-(E-Mail Removed)...
> > 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
>
>
> 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)
>

"NEWER USER" <(E-Mail Removed)> wrote in message
news:BCCCD6D5-DC12-4B74-ADA5-(E-Mail Removed)...
> 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.


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)

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

"Dirk Goldgar" wrote:

> "NEWER USER" <(E-Mail Removed)> wrote in message
> news:BCCCD6D5-DC12-4B74-ADA5-(E-Mail Removed)...
> > 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.
>
>
> 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)
>