Date in recordset leading to Update Edit error

[ChrisLayfield]

I have the following code to retrieve a recordset, which works perfectly. The issue comes form trying to assign the DateofClear data. It is retrieved correctly with the stored Short Date format, but when I run this where I assign the data to the text box on the form I get an 'Update or CancelUpdate without AddNew or Edit." error.

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT EmployeeID, BckgrdClearLvl, DateofClear FROM jtbl_EmployeeBackground " & _
    "WHERE jtbl_EmployeeBackground.EmployeeID = '" & Forms!frmBackgroundChecks.cbxSelectEmployee & "';")
Me.txtEmployeeName = rs(0)
Me.BckgrdClearLvl.RowSourceType = "Field List"
Me.BckgrdClearLvl.RowSource = rs(1)
Me.DateofClear = rs(2)

 

[Rabbie]

Is your form bound to a table or query? Please help us to help you by giving as much info as possible

 

[gemma-the-husky]

in order to edit within a record set you need to do this

rs.edit (to edit an existing record) OR
rs.addnew (to add a new record)
rs!newfield = whatever
rs.update

i think you are missing the edit and update statements.
note that if the rs is non updateable, or any other constraint is breached, then the update will fail

 

[ChrisLayfield]

The form is bound to a table which contains employeeID, background clearance level and date of clearance. On the form the user has the choice of adding a new user which opens a new record or editing an existing record. When the user selects the employee to update, the form is populated with the employee recordset (at least that's what I want). then when the edits are made, the user clicks the save command button to save the changes. The recordset loads properly except for the date data. This looks like it is loading correctly, but in the line

Me.DateofClear = rs(2)

I get the error. Even then when I highligt rs(2), it has the date data there with the format. Is it the short date format (mm/dd/yy) with the / marks that is giving me the issue?

I'll see if the rs.edit will work, I am a little confused on how to use it with separate edit and save subs.

 

[ChrisLayfield]

I figured out another way. since there is only one record per employee, I just used a dlookup with an error catch when there is no data.

This thread can be deleted.

 

[gemma-the-husky]

sorry chris, i got the wrong end of the stick

the rs.edit etc, is needed to use the recordset dynamically - but not update controls from the recordset, as you were trying to do.