DAO update event on unbound table gives "No current record" error

[LexSteel]

Thanks in advance for any help, you'll be a real lifesaver.
Anyway, when I try to update a unbound table based on a bound field in the current record, I get a "No current record" error and the field is not updated. The bound table stores the correct data, but the unbound field fails to update. Anyone have an idea what's wrong?
Thanks...


'********************************
Private Sub Form_AfterUpdate() '*
'********************************
On Error GoTo form_err

Dim db As Database
Dim rs As DAO.Recordset
Dim meetingdate As Date


Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from Contacts WHERE contactID LIKE '& Me!ContactID &'", dbOpenDynaset)
meetingdate = Me!CallDate
With rs
.Edit
!LastMeetingDate = meetingdate
.Update
End With
rs.Close
db.Close
Form_after_update_Exit:
Exit Sub
form_err:
MsgBox Err.Description
Resume Form_after_update_Exit
End Sub

 

[mflancour]

Fist thing to check is if rs returns anything. If so then the missing code is .movefirst unless multiple records are returned, in which case you will have to find the one you want.

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from Contacts WHERE contactID LIKE '& Me!ContactID &'", dbOpenDynaset)
meetingdate = Me!CallDate
With rs
.movefirst
.Edit
!LastMeetingDate = meetingdate
.Update
End With
rs.Close
db.Close
Form_after_update_Exit:
Exit Sub
form_err:
MsgBox Err.Description
Resume Form_after_update_Exit
End Sub

 

[CTOROCK]

try using

rst.fields("LastMeetingDate &quot = meeetingdate






Eric

 

[LexSteel]

Thanks in advance for any help, you'll be a real lifesaver.
Anyway, when I try to update a unbound table based on a bound field in the current record, I get a "
"You can't go to the specified record"" error and the field is not updated. The bound table stores the correct data, but the unbound field fails to update. Anyone have an idea what's wrong?
Thanks...
On Error GoTo form_err

Dim db As Database, rs As DAO.Recordset
Dim meetingdate As Date
Dim x As String

txtID.SetFocus
x = txtID.Text


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Contacts WHERE contactID LIKE '" & _
x & "'", dbOpenDynaset)
meetingdate = Me!CallDate
With rs
.MoveFirst
.Edit
.Fields("LastMeetingDate&quot = meetingdate
.Update
End With
rs.Close
'db.Close
Form_after_update_Exit:
Exit Sub
form_err:
MsgBox Err.Description
Resume Form_after_update_Exit
ContactID.SetFocus
End Sub

Originally, I was getting a "No current record" message, but then after further work, this is what I'm getting.
Thanks!

 

[cmmrfrds]

dim sql as string
sql = "SELECT * FROM Contacts WHERE contactID LIKE '*" & _
x & "*'"

'-MAKE SURE your sql string is good
debug.print "sql string = " ; sql
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

'- MAKE SURE you test if a record was returned
If rs.EOF = true then
msgbox "no record"
exit sub
end if
meetingdate = Me!CallDate


If you are using Access 97, you may need to do
rs.movefirst
rs.movelast
before testing for rs.EOF