By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,963 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,963 IT Pros & Developers. It's quick & easy.

Form Does Not Refresh

P: n/a
Hi,

I have a popup that is used to update the records on an open form. I
cannot get form to refresh with the new values. Any help you can lend
would be appreciated. Here is the code for the popup form:

'-----------------------------------------------
Sub AddRep_Click()

Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset

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.Requery

MsgBox "Sales Rep information has been updated.", vbOKOnly, "Sales Rep
Information"

DoCmd.Close acForm, "frmSalesRepPopUp", acSaveNo
DoCmd.RepaintObject acForm, "frmAccountProfile"
Forms!frmAccountProfile.Refresh

Set rst1 = Nothing
Set db = Nothing

End Sub
'----------------------------

Thank you.

Henry

Mar 7 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Henry Stockbridge wrote:
Hi,

I have a popup that is used to update the records on an open form. I
cannot get form to refresh with the new values. Any help you can lend
would be appreciated. Here is the code for the popup form:

'-----------------------------------------------
Sub AddRep_Click()

Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset

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.Requery

MsgBox "Sales Rep information has been updated.", vbOKOnly, "Sales Rep
Information"

DoCmd.Close acForm, "frmSalesRepPopUp", acSaveNo
DoCmd.RepaintObject acForm, "frmAccountProfile"
Forms!frmAccountProfile.Refresh

Set rst1 = Nothing
Set db = Nothing

End Sub
'----------------------------

Thank you.

Henry
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

rst1.Close
Set rst1 = Nothing
Set db = Nothing

End Sub
'----------------------------

Thank you.

Henry

Mar 7 '06 #2

P: n/a
salad <oi*@vinegar.com> wrote in
news:ja*****************@newsread1.news.atl.earthl ink.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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Mar 7 '06 #3

P: n/a
David W. Fenton wrote:
salad <oi*@vinegar.com> wrote in
news:ja*****************@newsread1.news.atl.earthl ink.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.
Mar 8 '06 #4

P: n/a

salad wrote:
David W. Fenton wrote:
salad <oi*@vinegar.com> wrote in
news:ja*****************@newsread1.news.atl.earthl ink.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.

Mar 8 '06 #5

P: n/a
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
Mar 8 '06 #6

P: n/a
Thanks for the help. I am all set now.

Mar 9 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.