Results 1 to 7 of 7
  1. #1
    BrittenTony is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2012
    Posts
    4

    Unhappy Problem: can't keep main form and a pop up synchronized

    Completely stumped. I've tried several methods. The latest one, which looked very elegant to me, is this http://www.tek-tips.com/faqs.cfm?fid=5860

    The code compliles without error. I've inserted the procedure call in the "Master" form, in the OnCurrent event. Nothing happens, not even an error msg. The code is in the Master Form's module. I tried inserting it in a new, public module, but Access gave me errors. I have very little hair left to pull out. I am a VBA novice.

    Code in form module:
    Option Compare Database
    Function IsOpenFrm(frmName As String) As Boolean


    Dim cp As CurrentProject, Frms As Object
    Set cp = CurrentProject()
    Set Frms = cp.AllForms
    If Frms.Item(frmName).IsLoaded Then
    If Forms(frmName).CurrentView > 0 Then
    IsOpenFrm = True
    End If
    End If
    Set Frms = Nothing
    Set cp = Nothing
    End Function

    Function call in OnCurrent property:
    If IsOpenFirm("BranchesALLDecisionEditQForm") Then Forms!BranchesALLDecisionEditQForm.Requery End If

    The two forms are initially synchronized using this code:
    Public Sub ButtonOpenForm_Click()
    DoCmd.OpenForm "BranchesALLDecisionEditQForm", , , "[DecisionNo]=[Forms]![JoinAllBranchesForm]![DecisionNo]"

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Unless it's a typo, I'd expect an error. Here is the function name and how you call it:

    IsOpenFrm
    IsOpenFirm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    BrittenTony is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2012
    Posts
    4
    Quote Originally Posted by pbaldy View Post
    Unless it's a typo, I'd expect an error. Here is the function name and how you call it:

    IsOpenFrm
    IsOpenFirm
    Thanks. I accidentally sent an earlier copy of the code. I removed the "i" in response to an error msg saying Access couldn't find a VBA or macro by that name. After fixing the spelling, I didn't get hits from the compiler or any error messages. But the pop up didn't change records when the main form did. Any tips on tracking down errors? I'm going to try, for the first time, to use the VBA editor to single step through my code, though that sure doesn't look like as easy in VBA as it is for a Macro. Maybe I'll go backward and try to write it as a Macro, and if I can get it right, have Access convert it back to Code to see whatever logical or text problem I'm missing. Still, any tips are appreciated. Thanks. Tony

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    If you haven't figured this out, can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    BrittenTony is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2012
    Posts
    4

    Found a solution that works for me

    Quote Originally Posted by pbaldy View Post
    If you haven't figured this out, can you post the db here?
    I did finally get to something that worked for me (final code below). Some of my problems were rookie mistakes, like not realizing I had to call the function from the OnCurrent event using the "=" sign. In some of my attempts I may also have erred by mixing function routines with sub routines in the same procedure (don't know if that's allowed). After those kinds of errors, my main two problems were:

    1. Finding code to check if PopUp (slave form) is open before running the "OnCurrent" event from the Main (master) form. Shortest method that worked: CurrentProject.AllForms.Item("MyFormName").IsLoade d

    2. The Requery method changed the PopUp's underlying query to show the same record as the Main form. But the PopUp form itself wouldn't refresh to show the same records as its underlying query! Neither the Refresh nor Repaint methods work. All I could do to refresh it was close and reopen the form using DoCmd statements. It worked, but was sloppy. The Pop Up would disappear then reappear right over the Main form, blocking the view. After a LONG internet search, the solution was simple, but not intuitive: Reset the RecordSource after the Requery. Not change it. Just re-add the existing RecordSource: Forms!PopUp.RecordSource = "PopUpQuery"


    Full code (suggestions on tightening up still welcome).
    Option Compare Database
    Dim cp As CurrentProject
    Dim Frms As Object

    ' See if pop form is open, and if so, call sub to sync it with main form
    Public Function IsOpenFrm(frmName As String) As Boolean
    Set cp = CurrentProject()
    Set Frms = cp.AllForms
    If Frms.Item(frmName).IsLoaded Then
    IsOpenFrm = True
    End If
    If IsOpenFrm = True Then
    SyncForms
    Else
    ClearVars
    End If
    End Function

    'Refresh pop up to match main form
    '
    Sub SyncForms()
    Forms!BranchesALLDecisionEditQForm.Requery
    Forms!BranchesALLDecisionEditQForm.RecordSource = "BranchesALLDecisionEditQ"
    ClearVars
    End Sub

    'Clear variables used in module
    Sub ClearVars()
    Set Frms = Nothing
    Set cp = Nothing
    End Sub

  6. #6
    BrittenTony is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2012
    Posts
    4

    continued

    Call to procedure from OnCurrent event: =IsOpenFrm("PopUpForm")
    Directions I tried from Internet didn't include the "=" sign as part of the function call, and I'm too novice to know I needed it.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Glad you got it sorted out. You only need the "=" when you call the function directly from the properties window, which is presumably what you're doing.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 03-07-2012, 03:46 AM
  2. Synchronized Combo Box Difficulty
    By abc in forum Access
    Replies: 2
    Last Post: 01-03-2012, 03:18 PM
  3. Replies: 3
    Last Post: 12-17-2010, 06:31 AM
  4. Problem with main form
    By lukusm in forum Forms
    Replies: 1
    Last Post: 01-18-2010, 02:41 AM
  5. Synchronized Combo Boxes
    By LesleaOH in forum Forms
    Replies: 34
    Last Post: 10-10-2009, 09:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums