Move to record on another form & update listbox

[MePenguin]

I'm VBAing myself in knots here trying to get a listbox to synchronise with it's parent form from another form...

[highlight]Short version[/highlight]

Why doesn't this work? I want form A to move to the same record that form B was displaying when it closed. Form A must be requeried to reflect changes made on B. Form B's recordsource is the linked table TSite, and Form A's recordsource is a query on TSite... the listbox ListSites needs to show the correct site...

Private Sub Form_Close() 'Form B
Dim ViewedSite As String
Dim frm As New Form_F2000SiteManager 'form A as object
ViewedSite = Me.BoxSiteCODE 'get code for site view/added

'Requery F2000SiteManager & listbox
        Forms![F2000sitemanager].Form.Requery
        Forms!F2000sitemanager.ListSites.Requery
'ListSites still shows the correct site, but the current 
'record is not the first record in TSite (naturally after 
'the requery)

'move to the correct record
    Dim rs As Object
    Set rs = frm.Recordset.Clone
    rs.FindFirst "[SiteCODE] = '" & ViewedSite & "'"
    Debug.Print rs.NoMatch
    frm.Bookmark = rs.Bookmark

'update summary data    
    Forms!F2000sitemanager.GetSiteSummaryNums
End Sub

The string ViewedSite always holds the correct code, the rs.findfirst method always find the record, but the form will just not move to it... I've tried various other methods... gotorecord... rs.abosoluteposition... I'm stuck.

[highlight]Long version[/highlight]

I have two forms based on linked tables:

A) F2000SiteManager - which gets its rowsource from SQL on table TSites, and has a listbox (ListSites) which lists all the sites in TSites. Clicking on an item in ListSites shows summary data for the site on this form.

B) F2000SiteDetails - can be opened from F2000SiteManager using a button and shows the full details of the site selected in ListSites. It can also be opened from another button on F2000SiteManager for entering a new record into TSite.

I want A to show the same site before and after B has been opened... sounds easy, eh?

This works fine if just using B to view details of the site selected in A. My problem is that after creating a new site in B, then A and ListSites need to be requeried to show the new site. I have managed to be able to:
i) highlight the correct site in ListSites
or
ii) move to the correct site on A
but NEVER BOTH AT THE SAME TIME!!!

My users, rightly so (albeit with limited powers of observation), cannot cope with this, and get thoroughly confused and delete the wrong site etc...

I'm trying to use code in the OnClose sub of form B, a variant follows, the key field/data item in both forms and listbox is SiteCODE:

Here's the code with various experiments remmed incase they spark ideas. This should be easy...

Private Sub Form_Close()
Dim ViewedSite As String
Dim frm As New Form_F2000SiteManager
ViewedSite = Me.BoxSiteCODE

'Refresh site manager form and recalculate summary info
        Forms![F2000sitemanager].Form.Requery
        Forms!F2000sitemanager.ListSites.Requery
'        Forms!F2000Sitemanager.Refresh
'    Forms!F2000sitemanager.ListSites.SetFocus
'    Forms!F2000sitemanager.ListSites = ViewedSite
    'Forms!F2000sitemanager.BoxSiteCODE.SetFocus
    'DoCmd.FindRecord ViewedSite, acEntire, , acSearchAll, , acCurrent, True
    'Forms!F2000sitemanager.Form.ListSites_AfterUpdate
'    frm.ListSites_AfterUpdate
    
    Dim rs As Object

    Set rs = frm.Recordset.Clone
    rs.FindFirst "[SiteCODE] = '" & ViewedSite & "'"
    Debug.Print rs.NoMatch
    frm.Bookmark = rs.Bookmark
    'frm.SetFocus
    'DoCmd.GoToRecord acDataForm, "F2000SiteDetails", acFirst, rs.AbsolutePosition

    'Call GetSiteSummaryNums 'this mod
    'Debug.Print " afterup " & Me.ListSites

    rs.Close
    Set rs = Nothing
    
    Forms!F2000sitemanager.GetSiteSummaryNums 'in F2000SiteManager

End Sub

Help me please!...

Phil

---------------
Pass me the ether.

 

[dRahme]

Hi, something like this should be pretty close:

Dim rst As DAO.Recordset
Dim frm As Form

DoCmd.OpenForm "frm2", acNormal

Set frm = Forms![frm2]

Set rst = frm.RecordsetClone

rst.FindFirst [yourValue] = Me![yourValue] 'form1

frm.Bookmark = rst.Bookmark
frm.[yourValue] = Me![yourValue]

Set rst = Nothing
Set frm = Nothing

DoCmd.Close acForm, "frm1", acSaveNo

Hope that helps.

 

[PHV]

And what about this ?
Private Sub Form_Close()
Dim ViewedSite As String
Dim frm As Access.Form
Dim rs As DAO.Recordset
ViewedSite = Me!BoxSiteCODE
Set frm = Forms!F2000sitemanager.Form
'Refresh site manager form and recalculate summary info
frm.Requery
frm!ListSites.Requery
Set rs = frm.RecordsetClone
rs.FindFirst "[SiteCODE] = '" & ViewedSite & "'"
If Not rs.NoMatch Then
frm.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
frm.GetSiteSummaryNums 'in F2000SiteManager
frm.SetFocus
Set frm = Nothing
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MePenguin]

Thanks for your help guys, PHV's solution works fine when viewing existing sites, but fails when closing a newly created site...

For some reason [tt]ViewedSite = Me!BoxSiteCODE[/tt] enumerates to the first sitecode in the table when closing from a new site, even though the F2000SiteDetails form is showing the correct sitecode.

If I debug.print BoxSiteCODE or the SiteCODE field in the Immediate window whilst F2000SiteDetails is showing the new site then it enumerates correctly to the new sitecode. It seems that the OnClose code can't access this information... and so takes the first record???

Updated sub:

Private Sub Form_Close()
Dim ViewedSite As String
Dim frm As Access.Form
Dim rs As DAO.Recordset

ViewedSite = Forms!F2000SiteDetails.BoxSiteCODE

Set frm = Forms!F2000sitemanager.Form

Debug.Print "site explorer closing from " & Me.BoxSiteName, ViewedSite

'Refresh site manager form and recalculate summary info
        frm.Requery
        frm!ListSites.Requery

    Set rs = frm.RecordsetClone
    rs.FindFirst "[SiteCODE] = '" & ViewedSite & "'"
    If Not rs.NoMatch Then
        frm.Bookmark = rs.Bookmark
    End If

    rs.Close
    Set rs = Nothing
        
    Forms!F2000sitemanager.GetSiteSummaryNums 
    frm.SetFocus
    Set frm = Nothing
    
End Sub
[\code]

Any ideas? I suppose I could work around the problem by dissabling the close box and setting the viewedsite variable on a CloseButton - but I'd prefer to keep both.

Another thought is that it is due to the way I generate new sites - although I don't see how, since debugging F2000SiteDetails shows everythign it should.

Thanks,

Phil

Phil

---------------
Pass me the ether.

 

[MePenguin]

Just one more thing - could this be due to the fact that the record source for F2000SiteDetails is a linked table?

Phil

---------------
Pass me the ether.