Henry Stockbridge wrote:
salad wrote:
David W. Fenton wrote:
salad <oi*@vinegar.com> wrote in
news:ja*****************@newsread1.news.atl.ear thlink.net:
You could try this.
....code at start line below
DoCmd.Close acForm, "frmSalesRepPopUp", acSaveNo
rst1.Close
Set rst1 = Nothing
Set rst1 = Forms!frmAccountProfile.Form.Recordsetclone
rst1.BookMark = Forms!frmAccountProfile.Form.BookMark
Forms!frmAccountProfile.Form.Requery
Forms!frmAccountProfile.Form.BookMark = rst1.Bookmark
A requery invalidates any bookmarks.
Good catch. I thought something looked odd. Usually I get the record
ID and then after requerying find the record and set the bookmark.
==============
I almost have it! The problem is only the first record is updated. I
need to update the record that matches the criteria. Since this code
is at the top of the routine could this be the culprit?
rst1.Edit
rst1!TrainerID = Me.txtID
rst1!TrainerName = Me.txtName
rst1!TrainerRepType = Me.txtRepType
rst1.Update
rst1.Requery
Where should this go? Does this need to be separated?
Thanks again for the help.
OK. First, I don't see you finding a record in "tblAccountProfile".
That is why I assumed there is only 1 record.
I'm not sure what frmAccountProfile is for. Why are you adding
something to a recordset and they requerying? For example, if you want
the values put into it, why not explicitly say so. Instead of opening
the table, why not do something like
Forms!frmAccountProfile!TrainerID = Me.txtID
Forms!frmAccountProfile!TrainerName = Me.txtName
Forms!frmAccountProfile!TrainerRepType = Me.txtRepType
and not bother requerying the form and opening tables and editting and
updating? IOW, forget this code.
Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblAccountProfile", dbOpenDynaset)
rst1.Edit
rst1!TrainerID = Me.txtID
rst1!TrainerName = Me.txtName
rst1!TrainerRepType = Me.txtRepType
rst1.Update
rst1.close
set rst1 = Nothing
Or are you adding the values to a table that is used by the form that
has calcs that it really needs to be requeried? If so then...
Let's pretend that the key to this record is called ID
Sub AddRep_Click()
Dim db As Database
Dim rst1 As Recordset
Dim lngID As Long
Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblAccountProfile", dbOpenDynaset)
'FIND THE RECORD
rst1.FindFirst "ID = " & Me.ID
'verify record found
If not rst1.NoMatch Then
'if found, update
rst1.Edit
rst1!TrainerID = Me.txtID
rst1!TrainerName = Me.txtName
rst1!TrainerRepType = Me.txtRepType
rst1.Update
Endif
rst1.close
set rst1 = Nothing
'set the ID from the form
lngID = Forms!frmAccountProfile!ID
'requery form to update
Forms!frmAccountProfile.Form.Requery
'now find and move to that record in the form's recordset
set rst1 = Forms!frmAccountProfile.Form.Recordsetclone
rst.FindFirst "ID = " & lngID
Forms!frmAccountProfile.Form.Bookmark = rst.Bookmark
MsgBox "Sales Rep information has been updated."
DoCmd.Close acForm, "frmSalesRepPopUp", acSaveNo
Set rst1 = Nothing
Set db = Nothing
End Sub
