Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / February 2007

Tip: Looking for answers? Try searching our database.

updating a recordset that is not the record source of a form

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SandyR - 28 Feb 2007 18:58 GMT
I have a form in which I create a new crew record, and a members subform in
which I assign members to the new crew.  The records for the crew members
already exist – I just need to update the crew number field in the
appropriate record.   I also want to display any members already assigned to
the crew.   To do the latter, I have made the record source for the form the
member records with this form number on them.   To assign members to the new
crew, I have an unbound combo box which shows the unassigned members.   In
the on_click procedure of this combo box, I want to update the selected
member record (which is not in the record source for the subform yet) with
the crew number.   So I am using a recordset which is not the recordset of
the form.  My code is shown below.   When I execute it, I get this error:  
Error 3020 (update or cancelupdate without AddNew or Edit) on the statement
assigning a value to rst!crewno.     I don’t know what this means or how to
fix it.  Can anyone help?

Private Sub cboEmpno_Click()
Dim Rst As DAO.Recordset
Dim dbs As Database

Dim stsql As String
Dim STWHERE As String
  On Error GoTo List2_Click_Error
   
   stsql = "SELECT tbldayemps.empno, tbldayemps.crewno, tbldayemps.logday "
   stsql = stsql & "FROM tbldayemps WHERE ((tbldayemps.crewno)=0) "
   stsql = stsql & " AND (( tbldayemps.logday )= #" &
[Forms]![dailylog].[Form]![cboTdate] & "# )"
   Set dbs = CurrentDb
   Set Rst = dbs.OpenRecordset(stsql, dbOpenDynaset)

   STWHERE = "empno = " & Me![cboEmpno]
   Rst.FindFirst STWHERE
   ' the following statement should never be true
   If Rst.NoMatch Then
       MsgBox "no match"
   End If
   
   Rst!crewno = Me.Parent!cboCrewid    <========== error occurs here
   
   Me.Dirty = False
   Me.Requery
   Me.cboEmpno.Requery
   

List2_Click_exit:
  On Error GoTo 0
  Exit Sub

List2_Click_Error:

   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
List2_Click of VBA Document Form_members subform"
   Resume List2_Click_exit
End Sub
kingston - 28 Feb 2007 19:07 GMT
Add two commands around the line of code that causes the error:
Rst.Edit
Rst!crewno
Rst.Update

>I have a form in which I create a new crew record, and a members subform in
>which I assign members to the new crew.  The records for the crew members
[quoted text clipped - 50 lines]
>    Resume List2_Click_exit
>End Sub
SandyR - 28 Feb 2007 19:20 GMT
Works like a charm!   Thanks so much.

> Add two commands around the line of code that causes the error:
> Rst.Edit
[quoted text clipped - 55 lines]
> >    Resume List2_Click_exit
> >End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.