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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Works like a charm! Thanks so much.

"kingston via AccessMonster.com" wrote:

Add two commands around the line of code that causes the error:
Rst.Edit
Rst!crewno
Rst.Update

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

--
Message posted via http://www.accessmonster.com


.

Relevant Pages