Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 67
  1. #16
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    1) What does BOJ mean?
    Beginning of Job


    2) Is that RecordSource as referred to in the ManageSCATeam form available to the subforms, Members, Events, Assets, etc.? I am not sure how I would reference it elsewhere. Do I repeat that code in each ComboBox which selects members?
    Yes. Any form recordsource that wants to draw fields from members would need to have the is_availGlobal in its WHERE clause. Same for all the comboboxes.
    3) You say using the phrase "Select * from Members where Status = Is_ActiveGlobal" elsewhere when referencing the Members RecordSource. But doesn't that just select either Active members, or InActive members, rather than Active members or All members, as the Case statement does?
    Didn't realize you wanted to differentiate in that manner. You need to select either Active or Inactive, but not both? In that case, ALL the selects need to have criteria for is_activeGlobal being either true or false.
    4) How do my reports that need to be sensitive to Is_Active relate to this?
    Same deal. The report recordsource needs the criteria.
    5) I don't understand why my Joined and Resigned controls need to be unbound. I would like them to store into the members' profiles, so I don't have to do ELookUp()s everywhere I need to show their values. Is there something fundamentally wrong with doing that?
    Why did you have ELookups in the first place? For new records there's nothing to lookup. That's why we skipped it for the new records. If we make the fields bound, then do away with the dlookups and have the user just enter the dates in the first place. The Joined would never change, but the Resigned certainly could. Can that be blank until something needs to be there?

  2. #17
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    In regards to #2, you could simplify things by creating a query based on Members with criteria "WHERE [Active] = " & is_ActiveGlobal
    If this query were named qMembersActiveTF, then substitute that query wherever table members appears in SQL.
    You could also do the same for combobox rowsource: SELECT ID, Mem_Name FR0M qMembersActiveTF ORDER BY Mem_Name;

  3. #18
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    To answer your question: I originally had the Members Joined and Resigned fields filled in by hand, but that allows potential sync issues between them and the corresponding value in the Memberships table. The Memberships child table is the "authority" for this, and other many-to-one membership related data. Since the Joined field, in particular, is used in a number of contexts, I wanted to keep the data redundantly in the profile record to avoid the bother of using the ELookUp() in all the other contexts. To avoid sync issues, I now populate them using the ELookUP() function. The SCATeam is over 35 years old. The db is new this year. As I am reconstructing the historical data for the team, there are many cases where I had to estimate the Joined date. Later, as I found new information, I may be able to provide more accurate Joined dates. Keeping the data in sync manually, was a bothersome task. If there is no data (no corresponding entries in the Memberships table) for either the Joined or Resigned fields, they do display as blank.

    Thanks for your help. I will be implementing your suggestions.

  4. #19
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    If I understand correctly, this suggestion would produce data sets for All Active or All Inactive, but what I need is to produce data sets for Active only members, or All members (active and inactive). Your earlier Case procedure does that. Incidentally, there is a report (Hall of Fame), which deals only with Inactive members. For this, I just say WHERE Members.Active = False. Many other reports need to be sensitive to the Is_Active checkbox to include or not include inactive members with the Active members.

  5. #20
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by WCStarks View Post
    Many other reports need to be sensitive to the Is_Active checkbox to include or not include inactive members with the Active members.
    Indeed, you will have to determine whether the recordsource/rowsource needed is for active, inactive or all, as appropriate. I think you are moving along nicely.

  6. #21
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I implemented your On Current suggestion and it works fine. I can now add a new record without errors. The is_Active is also working fine. Thanks.

  7. #22
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    As I indicated earlier, I implemented your form Current procedure
    Code:
    Private Sub Form_Current()    
        If Me.NewRecord Then
            Me.Joined = vbNullString
            Me.Resigned = vbNullString
            Exit Sub
        End If
        Me.Joined = ELookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and [Member_ID] =" & [ID], "[Status_Date] Desc")
        Me.Resigned = ELookup("[Status_Date]", "Membership", "([Status] = ""Resigned"" or [Status] = ""Terminated"" or [Status] = ""Emeritus"") and [Member_ID] =" & [ID], "[Status_Date] Desc")
    End Sub
    I found that with this method, When I modify a Joined date in the Membership sub-form, for example, the Joined control in the Members form does not update, even after executing a refresh. It only updates when I leave the record and return to it. Any thoughts?

  8. #23
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    This post became irrelevant.

  9. #24
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I found that with this method, When I modify a Joined date in the Membership sub-form, for example, the Joined control in the Members form does not update, even after executing a refresh. It only updates when I leave the record and return to it. Any thoughts?
    If Resigned and Joined are bound, you don't want the red lines.

  10. #25
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Yes. I understand about the red lines, However, I am still having a serious problem with the current implementation. With the above code in the Members form Current property, the members record will not reflect changes made in the Membership sub-form, until after returning to the same record, after having gone to a different record. This is the same whether the Joined and Resigned controls are Bound or Unbound.
    Last edited by WCStarks; 12-17-2018 at 10:37 AM. Reason: further clarificaiton

  11. #26
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Change the form_current event in SCATeam to public instead of private.
    Then add this code to form Membership:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Status_Date_AfterUpdate()
        If Me.Dirty Then Me.Dirty = False
        Call Form_SCATeam.Form_Current
    End Sub
    Last edited by davegri; 12-17-2018 at 11:43 AM. Reason: formatting

  12. #27
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Thank you. That works great!

  13. #28
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Earlier, in post 11, you suggested the following code:

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Is_Active_AfterUpdate()
        Select Case Is_Active
            Case True
                Me.RecordSource = "select * from members where Active = true"
            Case False
                Me.RecordSource = "members"
        End Select
     End Sub
    I didn't implement this as my Is_Active is working as is. It would, however, be very interesting if the results of the RecordSource in that code could be saved to a public variable, to be referenced by other contexts instead of the Members table directly. Can that be done?

  14. #29
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Yes. We can create a customized named query on the fly when the active check box is changed. That query can be the recordsource or rowsource for all objects that need such criteria.
    Make the recordsource for form ManageScatTeam blank then add this code to the form. The code will create qry_MembersSelected with built in criteria for is_active. The query will automatically
    select active or not active depending on the checkbox.

    Code:
    Option Compare DatabaseOption Explicit
    
    
    Private Sub Form_Load()
        Call Is_Active_AfterUpdate
    End Sub
    
    
    Private Sub Is_Active_AfterUpdate()
        Dim ssql As String
        Select Case Is_Active
            Case True
                ssql = "select * from members where Active = true"
            Case False
                ssql = "select * from members where Active = false"
        End Select
        Call fcnMakeNamedQryFromSQLString(ssql, "qry_MembersSelected")
        Me.RecordSource = "qry_MembersSelected"
     End Sub
    Public Function fcnMakeNamedQryFromSQLString(ssql As String, qName As String)
        Dim Qdf As DAO.QueryDef
        Dim Dbs As DAO.Database
        Dim qExists As Boolean
        Set Dbs = CurrentDb
        qExists = False
        For Each Qdf In Dbs.QueryDefs
            If Qdf.Name = qName Then
                qExists = True
                Exit For
            End If
        Next
        If qExists Then
            Qdf.SQL = ssql
        Else
            Set Qdf = Dbs.CreateQueryDef(qName)
            Qdf.SQL = ssql
        End If
        Set Dbs = Nothing
        Set Qdf = Nothing
    End Function

  15. #30
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Wow. That is quite involved. I really appreciate you taking the time to produce this form me. While I have learned a lot over this last year, this code is well beyond my horizons. How long did it take for you to produce this? Can you suggest a good reference I could study to attempt to better understand all this and VB in general? I have mainly learned as I have developed one feature after another and solved one problem after another. I have a Coding document wherein I keep all of these solutions I have been provided. But what I need is a better general understanding of the logic.

    Again, the purpose of the Is_Active checkbox is to either restrict or not restrict the Members RecordSource to only Active. So, I need to modify the selection, when Is_Active is False, to say, ssql = "Members", as you did originally in Post 11, so it gets all, not just non-active.

    I thought I had to select an Event property and place the code discretely for it, and then do the same for another event property. However, as you describe how to do this, it seems I can just select one of the properties to get me to the code window, or select View Code, and then just put all the code there, and the remaining referenced event property will automatically be linked to the code. And this function can also be placed either in a form as you have done, or in a custom Module, as I have done? I didn't know that either. Access does so much that it has a big learning curve to learn how to do tasks.

    It is going to be nice when all my combo-boxes and reports, which reference Members can simply reference this RecordSource definition by name and the restrictions or not are already taken care of.

Page 2 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