Page 4 of 5 FirstFirst 12345 LastLast
Results 46 to 60 of 67
  1. #46
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Yes, but it doesn't run without errors in the create the query module. So, I looked at the related code.
    I don't get any errors.
    Can you please be more specific - there is no 'create query module'. What conditions cause the errors? What is the code location and error code? How can I make the db give me those errors?

  2. #47
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I should probably say, that when I initially implemented your feature, I later realized I had not change the data source for the form. It was still using the the previous filtering it had done from the beginning. When this issue showed itself, I went looking around, and found I had not also changed its data source, so I changed it, but it still has the same problem. I guess, I had better send you another db.

  3. #48
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I guess, I had better send you another db.
    Why not use the one that I sent in post#42?

  4. #49
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I did try your modification in post 42, but it does the same thing. It appears that the query does not get modified initially, but retains the active status, until after I leave the Members tab and come back to it. Then, I can switch back and forth at will, with no trouble. It is just after first opening the database with the Members form active and switching the Active Status off that it doesn't work. It will only find Active members regardless of how the Active status is set, and as many times I switch the status. I must first leave the Members form and return to it, before it will begin to work correctly. Here is the code I copied from the v03 db in post 42. I have since moved the called Function to my UDF module, thinking that might make a difference, but it did not. Notice how I modified the non-active code to include both active and non-active when Active is False.
    I have no idea how to get this to work.
    Code:
    Private Sub Is_Active_AfterUpdate()
        ' By Davegri on Access Forum
        Dim ssql As String
        Select Case Is_Active
            Case True
                ssql = "select * from members where Active = true"
            Case False
                ssql = "Select * from Members"
        End Select
        Call fcnMakeNamedQryFromSQLString(ssql, "qry_MembersSelected")
       'below will set the recordsource for each navigation subform as the buttons are clicked!
        'the previous subform is UNLOADED!
        DoEvents
        Select Case Me.NavigationControl0.SelectedTab.Name
            Case "nbMembers"
                Me.NavigationSubform.Form.RecordSource = "qry_MembersSelected"
            Case Else
                Forms![Manage SCATeam].NavigationSubform.Form.Refresh
        End Select
     End Sub

  5. #50
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    But that's not the code in the DB from Post#42. This is, and it works in that DB.
    Code:
    Private Sub Is_Active_AfterUpdate()
        'MsgBox Is_Active
        Dim ssql As String
        Select Case Is_Active
            Case -1
                ssql = "SELECT * FROM members WHERE [Active] = True ORDER BY [SurName];"
            Case 0
                ssql = "SELECT * FROM members WHERE [Active] = False ORDER BY [SurName];"
        End Select
        Call fcnMakeNamedQryFromSQLString(ssql, "qry_MembersSelected")
        'below will set the recordsource for each navigation subform as the buttons are clicked!
        'the previous subform is UNLOADED!
        DoEvents
        Select Case Me.NavigationControl0.SelectedTab.Name
            Case "nbMembers"
                Me.NavigationSubform.Form.RecordSource = "qry_MembersSelected"
            Case Else
                Forms![Manage SCATeam].NavigationSubform.Form.Refresh
        End Select
    End Sub
    
    Rather than copying this code to your version, just try running the DB in Post#42 to verify.
    I can't vouch for your version with unknown code.

  6. #51
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Davidgri, are you still around?
    I still have an issue. I don't think I stated the issue as clearly as I needed to before. Hopefully, this explanation will be more helpful.

    The code that you produced to recreate a virtual table each time the checkbox Is_Active status is changed, works properly. With each change, the query table gets created correctly. However, there is a certain situation where the Member search box underlying query does not see the change, and I have been unable to figure out why. I will outline the situation:

    1) Open the database as usual. The default Member navigation form is now active with the Is_Active checkbox checked.
    2) Before going to any other navigation form tab, uncheck the Is_Active checkbox. Confirm that the query table is correctly modified.
    3) Then, open the members search box. Confirm that all the members are listed in the list, both active and inactive. Now, select the member with 48 in the surname with N7EVC, who is an inactive member. You will see that he cannot be found. No inactive members can be found at this time. For some reason, the underlying query initiated by the search box, is still seeing only the previous active version of the query table. You can switch the checkbox back and forth all you want and the result is the same.
    4) Now, select the Organization form tab, or one of the other tabs, then switch back to the Members form.
    5) Repeat step 3). Record 48 and all inactive members will now be found along with all active members.

    Something happens when leaving and returning to the default Member form, that allows the search box to now be able to find the inactive members. After doing step 4) you can now switch back and forth between active and inactive and the search box's underlying query will see each change for the rest of the time the db is open. But close the db and reopen it, and follow the steps outlined above and the issue starts all over again. The only work-around is to leave the Members form and return to it.

    I am running this code, which is the same as your original code except for "Case False", which allows both active and inactive members to be selectable, instead of just inactive members only.
    Code:
    Private Sub Is_Active_AfterUpdate()
        ' By Davegri on Access Forum
        ' MsgBox Is_Active
        Dim ssql As String
        Select Case Is_Active
            Case True
                ssql = "select * from members where Active = true"
            Case False
                ssql = "Select * from Members"
        End Select
        Call fcnMakeNamedQryFromSQLString(ssql, "qry_MembersSelected")
       'below will set the recordsource for each navigation subform as the buttons are clicked!
        'the previous subform is UNLOADED!
        DoEvents
        Select Case Me.NavigationControl0.SelectedTab.Name
            Case "nbMembers"
                Me.NavigationSubform.Form.RecordSource = "qry_MembersSelected"
            Case Else
                Forms![Manage SCATeam].NavigationSubform.Form.Refresh
        End Select
     End Sub
    SCATeam.zip
    I am attaching the db with a sanitized version of the database. The db is split, so you will need to re-link the tables.

  7. #52
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Add one line of code:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Form_Load()
        'Call Is_Active_AfterUpdate
    End Sub
    
    
    Private Sub Is_Active_AfterUpdate()
        ' By Davegri on Access Forum
        Dim ssql As String
        Select Case Is_Active
            Case True
                ssql = "select * from members where Active = true"
            Case False
                ssql = "Select * from Members"
        End Select
        Call fcnMakeNamedQryFromSQLString(ssql, "qry_MembersSelected")
       'below will set the recordsource for each navigation subform as the buttons are clicked!
        'the previous subform is UNLOADED!
        Me.NavigationSubform.Requery
        DoEvents
        Select Case Me.NavigationControl0.SelectedTab.Name
            Case "nbMembers"
                Me.NavigationSubform.Form.RecordSource = "qry_MembersSelected"
            Case Else
                Forms![Manage SCATeam].NavigationSubform.Form.Refresh
        End Select
     End Sub
    Then blank this and get rid of that third bottom navigation button:

    Click image for larger version. 

Name:	wc.png 
Views:	16 
Size:	41.9 KB 
ID:	41657

  8. #53
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Thank you for your response. Please clarify what you mean by setting the navigation buttons to NO? You didn't mean to set their Enabled properties to NO, did you?

  9. #54
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I didn't do anything to the navigation buttons. I tried your fix and it now works correctly. Thank you so much!!
    I notice one thing that is happening. When I switch the checkbox the current member screen goes blank, until I select someone again. Is that necessary?

  10. #55
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I notice one thing that is happening. When I switch the checkbox the current member screen goes blank, until I select someone again. Is that necessary?
    This doesn't happen in the version that you uploaded here.

    As for the Nav button:

    Click image for larger version. 

Name:	nav.png 
Views:	16 
Size:	46.2 KB 
ID:	41659

  11. #56
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Thanks for the clarification on the Navigation Button "NO". Maybe I did not explain the blank screen very well. The screen itself does not go blank, only the current member's data is blanked out. Is that necessary? I suppose that is not a big deal. I am just used to seeing the current data remain until I select someone else.

  12. #57
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Again, doesn't happen with db you supplied.

  13. #58
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    That is curious. I sent the current front-end before your fix. Oh Well. The important thing is that it can dependably find the members now.

  14. #59
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    When the form first loads active is checked and it shows surname 23. When I uncheck active it shows surname 41. No blanks.

  15. #60
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    When I first open my database, record 51 is displayed. That is a dummy record used to show an event assignment that has not yet been filled. It is all blank, except for the Note field. So, the surname and given name are both blank. I just realized that when I check or uncheck, the db returns to this same record 51. It is alphabetically first, having a blank surname. I hadn't noticed that the note field wasn't blank. So, what I said earlier, that the fields went blank was not true, it was just returning to record 51 with its blank surname. I suppose what you are seeing is how it is sorting differently because the surname now has a number and not a actual surname as before. Before the "fix" the form apparently did not refresh after toggling, which is why it remained on the current record after toggling. I guess there is no way to keep displaying the current record and still get the form to see the newly changed query table? Before the fix, it is interesting, that once I left and returned to the Member form, I could toggle the Is_Active checkbox over and over and it would always work correctly. It was just when I first opened db and hadn't left the Member form, that it would not ever see the new query table. I also notice that now the forms seem to refresh several times following each toggle.

Page 4 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. DoCmd.RunCommand acCmdSaveRecord Problem
    By musicopr in forum Programming
    Replies: 3
    Last Post: 06-23-2017, 06:45 PM
  2. Replies: 2
    Last Post: 09-27-2016, 09:10 PM
  3. docmd.runcommand accmdpaste - data is NULL
    By dickn in forum Programming
    Replies: 7
    Last Post: 07-16-2013, 04:27 PM
  4. Replies: 0
    Last Post: 06-17-2010, 04:51 AM
  5. DoCmd.RunCommand acCmdSaveRecord
    By Rick West in forum Programming
    Replies: 3
    Last Post: 04-22-2010, 02:52 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