Hi.
How do I get frmTC to reflect the changes that were made on frmSE? I've
tried refresh and requery
You must requery the form in order to retrieve all changes in the form's
recordset from the database. Refresh just refreshes the records obtained
from the database when the form was last opened. Refresh won't reflect
changes made by other processes to this recordset, such as updates, additions
or deletions.
In the Activate event of frmTC the code closes frmSE.
Is there a reason that frmSE can't -- or shouldn't -- close itself? The
OnActivate( ) event for frmTC isn't required for saving the record or closing
the other form, so unless you need it for some other purpose you haven't
mentioned, I'd leave it out in order to keep the code as simple as possible.
I chose the refresh method rather than the requery b/c the requery will
scroll to the first record whereas the refresh will remain at the current
record. (Is this correct?)
No. Scroll to the original record after requerying the form's recordset.
Here's some code for you to try, but there are a few prerequisites necessary
in order for it to work. You must set a reference to the DAO library if you
haven't already. The value passed to frmSE must be the primary key of the
record that you want to edit. The code uses the same record source for both
bound forms. The code is designed to work with a primary key with a Long
data type, so if you have a different data type for your primary key, the
syntax will be slightly different. Please let me know, and I can give a
sample of the syntax for whatever data type you have.
In this example, frmTC has one button, "EditBtn," to open the frmSE form and
make itself invisible. The primary key is "ID" and the text box control for
this value is named "txtID" in both forms. Copy the following (up to the
first "End Code" line) into frmTC's code module and change the name of the
sub to your button's name with Click( ) event:
' * * * * Start Code * * * *
Private Sub EditBtn_Click()
On Error GoTo ErrHandler
DoCmd.OpenForm "frmSE", acNormal, , "ID = " & Me!txtID.Value
Me.Visible = False
Exit Sub
ErrHandler:
MsgBox "Error in EditBtn_Click( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub
' * * * * End Code * * * *
In this example, frmSE has one button, "CloseBtn," to save the record,
requery frmTC, scroll to the correct record, make frmTC visible again, then
close itself. txtStuff is the name of a control that can be edited, because
the focus can't be on the button when the record is saved. Copy the
following (up to the second "End Code" line) into frmSE's code module and
change the name of the sub to your button's name with Click( ) event:
' * * * * Start Code * * * *
Private Sub CloseBtn_Click()
On Error GoTo ErrHandler
Dim recSet As DAO.Recordset
Dim fOpenedRecSet As Boolean
Me!txtStuff.SetFocus ' Set focus to any editable control.
Me.Dirty = True
Forms("frmTC").Requery
Set recSet = Forms("frmTC").RecordsetClone
fOpenedRecSet = True
If (Not (recSet.BOF And recSet.EOF)) Then
recSet.FindFirst "ID = " & Me!txtID.Value
If (Not (recSet.NoMatch)) Then
Forms("frmTC").Bookmark = recSet.Bookmark
Else
MsgBox "Cannot find record matching " & vbCrLf & """ID = " & _
Me!txtID.Value & """", vbExclamation + vbOKOnly, _
"Record Not Found!"
End If
End If
Forms("frmTC").Visible = True
DoCmd.Close acForm, Me.Name
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
Set recSet = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in CloseBtn_Click( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
' * * * * End Code * * * *
Save both forms and compile the code. Close frmSE and open frmTC in Form
View. Scroll to any record, select the EditBtn button to open the frmSE
form, change the record, select the CloseBtn button to close frmSE and frmTC
will appear again, scrolled to the correct record with the change you just
made.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
