Open a subform on a record closest to today's date

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Karen20785
    New Member
    • May 2018
    • 10

    Open a subform on a record closest to today's date

    Hi,

    I'm trying to get MS Access to automatically move the cursor to the record near today's date within a subform in a project management database (frmvw_mileston es). The Main form (FRM_staff_proj ect) has the project information and it includes a subform (frmvw_mileston es) that has details about the important milestones within certain projects. The subform shows all the milestones for the fiscal year for the staff member. I want to move the cursor down within the form so that the staff member sees his/her current milestones upon opening the form instead of having to scroll down.

    The following code almost works:
    Code:
    Private Sub Form_Load()
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim TheDate As Date
    
        Set db = CurrentDb
        strSQL = "SELECT max(Milestone_Date) as Dte " & _
                 "FROM qryvw_milestones " & _
                 "WHERE Milestone_Date<=Now()"
        Set rst = db.OpenRecordset(strSQL)
        TheDate = rst.Fields(0)
        Me.Milestone_Date.SetFocus
        DoCmd.FindRecord TheDate
        Me.Milestone_Date.SetFocus
    
    End Sub
    ---------------
    However, I need to modify this code so that it selects the maximum Milestone_Date for that staff member based on his/her id (ID_staff) instead of the maximum Milestone_Date for all staff members. I tried modifying the SELECT statement to following but it generates errors:
    Code:
    strSQL = "SELECT max(Milestone_Date) as Dte " & _
             "FROM qryvw_milestones " & _
             "WHERE (Milestone_Date<=now() " & _
             "AND (tbl_pria_projects.ID_staff)=[Forms]![FRM_staff_projects]![ID_staff])"
    Any help would be appreciated. Attached are pictures of the main form (FRM_staff_proj ects) and the underlying query (qryvw_mileston es)
    [IMGNOTHUMB]https://bytes.com/attachments/attachment/9432d1525201197/2018-05-01_10-46-45-swoop-project-form.jpg[/IMGNOTHUMB]
    [IMGNOTHUMB]https://bytes.com/attachments/attachment/9433d1525201197/qryvw_milestone s.jpg[/IMGNOTHUMB]
    Attached Files
    Last edited by NeoPa; May 3 '18, 03:15 AM. Reason: Added code tags and adjusted formatting - Made pics viewable (NeoPa).
  • twinnyfo
    Recognized Expert Moderator Specialist
    • Nov 2011
    • 3653

    #2
    Karen,

    Welcome to Bytes!

    First, as an administrative note, when posting code to this forum, please use the code tags in your text editor, as it is easier for users to see your code and debug it. I've done so above, as well as adjusted some formatting so things don't have to slide so far to the right....

    Second, I must assume that the field tbl_pria_projec ts.ID_staff is part of the query qryvw_milestone s. If not, then this is definitely part of your problem. I may presume that it is not, since you are declaring it explicitly with the table name and the field name. If it was part of your Query, it would probably simply be ID_Staff. This should be corrected by adding the ID_Staff as an output field of your Query (you will have to join the Staff table to your Query).

    Third, an additional problem has to do with the syntax of your SQL statement. You must use the value from your form within the SQL statement, and not just list it within the WHERE clause. Additionally, it is often better to determine what it is you are looking for first, assign it to a variable, and then use that variable within your SQL WHERE clause. Here is what I am talking about [assuming all else remains the same in your code]:

    Code:
        Dim lngStaff As Long
        lngStaff = [FRM_staff_projects]![ID_staff]
    
        [all the rest of your code]
    
        strSQL = "SELECT max(Milestone_Date) as Dte " & _
            "FROM qryvw_milestones " & _
            "WHERE ((Milestone_Date<=now()) " & _
            "AND (ID_staff = " & lngStaff & "))"
    
        [finish your code]
    Notice 1) that we are concatenating the value of lngStaff (I am assuming an autonumbering index here) into the SQL statement and 2) that this method can significantly clean up your Code.

    Let me know if you have any additional questions.

    Hope this hepps!

    Comment

    • Karen20785
      New Member
      • May 2018
      • 10

      #3
      Thanks twinnyfo!

      Yes, field tbl_pria_projec ts.ID_staff is part of the query qryvw_milestone s. Your suggested coding worked with one slight modification:


      Code:
      Dim db As DAO.Database
          Dim rst As DAO.Recordset
          Dim strSQL As String
          Dim TheDate As Date
          Dim lngStaff As Long
      
          lngStaff = Me.ID_staff
          Set db = CurrentDb
          
          strSQL = "SELECT max(Milestone_Date) as Dte " & _
              "FROM qryvw_milestones " & _
              "WHERE ((Milestone_Date<=now()) " & _
              "AND (ID_staff = " & lngStaff & "))"
          Set rst = db.OpenRecordset(strSQL)
          TheDate = rst.Fields(0)
          Me.Milestone_Date.SetFocus
          DoCmd.FindRecord TheDate
          Me.Milestone_Date.SetFocus
      However, I encountered an unexpected problem. A few of our staff members do not have any milestones. This results in a runtime error when they open the form since they don't have any records to show in the subform frmvw_milestone s. Is there anyway to hide the subform or show a blank record when this occurs?
      Last edited by twinnyfo; May 2 '18, 10:23 AM. Reason: formatting issues

      Comment

      • twinnyfo
        Recognized Expert Moderator Specialist
        • Nov 2011
        • 3653

        #4
        Is there anyway to hide the subform or show a blank record when this occurs?
        This is actually quite easy. Since you know the person, using ID_Staff, before you go to the particular record, check to see if the user has any Milestones in the recordset in question. Then, a simple If ... Then ... Else. You have the Then part; the Else would be something like this:

        Code:
        With Me.frmvw_milestones.Form
            .Filter = "ID_Staff = 0"
            .FilterOn = True
        End With
        Hope this hepps!

        Comment

        • Karen20785
          New Member
          • May 2018
          • 10

          #5
          Thanks Twinnyfo,

          Unfortunately, it still gives me "Run-time error '2427': You entered an expression that has no value" message whenever the user has no milestones.

          Below is a copy of coding (for some reason I had to replace Me.frmvw_milest ones.Form with Me!formvw_miles tones.Form)


          Code:
          Dim db As DAO.Database
              Dim rst As DAO.Recordset
              Dim strSQL As String
              Dim TheDate As Date
               Dim lngStaff As Long
           
              Set db = CurrentDb
              
              If Me.ID_staff <> 0 Then
              lngStaff = Me.ID_staff
              strSQL = "SELECT max(Milestone_Date) as Dte " & _
                  "FROM qryvw_milestones " & _
                  "WHERE ((Milestone_Date<=now()) " & _
                  "AND (ID_staff = " & lngStaff & "))"
              Set rst = db.OpenRecordset(strSQL)
              TheDate = rst.Fields(0)
              Me.Milestone_Date.SetFocus
              DoCmd.FindRecord TheDate
              Me.Milestone_Date.SetFocus
                
                
            Else
            With Me!frmvw_milestones.Form
              .Filter = "ID_Staff = 0"
              .FilterOn = True
          End With
          
          
          End If

          Comment

          • Karen20785
            New Member
            • May 2018
            • 10

            #6
            I think I found a solution:

            Code:
            On Error GoTo Done
             Dim db As DAO.Database
                Dim rst As DAO.Recordset
                Dim strSQL As String
                Dim TheDate As Date
                 Dim lngStaff As Long
             
                Set db = CurrentDb
                
                
                lngStaff = Me.ID_staff
                
                
                strSQL = "SELECT max(Milestone_Date) as Dte " & _
                    "FROM qryvw_milestones " & _
                    "WHERE ((Milestone_Date<=now()) " & _
                    "AND (ID_staff = " & lngStaff & "))"
                Set rst = db.OpenRecordset(strSQL)
                TheDate = rst.Fields(0)
                Me.Milestone_Date.SetFocus
                DoCmd.FindRecord TheDate
                Me.Milestone_Date.SetFocus
                  
                  
            Done:
            Exit Sub

            Comment

            • twinnyfo
              Recognized Expert Moderator Specialist
              • Nov 2011
              • 3653

              #7
              I think you are trying to put the cart slightly before the horse here, but sometimes it's difficult to communicate properly via a forum.

              First, you want to find out if the user has any milestones in your query. Depending on the results of that inquiry, then you use the Staff ID.

              Here is a guess at what it might look like, but you will probably have to clean it up to suit your exact situation.

              Code:
              Private Sub Form_Load()
              On Error GoTo EH
                  Dim db          As DAO.Database
                  Dim rst         As DAO.Recordset
                  Dim strSQL      As String
                  Dim TheDate     As Date
                  Dim lngStaff    As Long
                  Dim fMilestone  As Boolean
               
                  lngStaff = Me.ID_Staff
                  fMilestone = Nz(DLookup("ID_Staff", "qryvw_milestones", _
                      "ID_Staff = " & lngStaff), False)
                  
                  If fMilestone Then
                      strSQL = "SELECT max(Milestone_Date) as Dte " & _
                          "FROM qryvw_milestones " & _
                          "WHERE ((Milestone_Date<=now()) " & _
                          "AND (ID_staff = " & lngStaff & "))"
                      Set db = CurrentDb
                      Set rst = db.OpenRecordset(strSQL)
                      With rst
                          If Not .RecordCount = 0 Then
                              TheDate = .Fields(0)
                              Me.Milestone_Date.SetFocus
                              DoCmd.FindRecord TheDate
                              Me.Milestone_Date.SetFocus
                          End If
                          .Close
                      End With
                      db.Close
                      Set rst = Nothing
                      Set db = Nothing
                  Else
                      With Me!frmvw_milestones.Form
                          .Filter = "ID_Staff = 0"
                          .FilterOn = True
                      End With
                  End If
              
                  Exit Sub
              EH:
                  MsgBox "There was an error loading the form!" & _
                      vbCrLf & vbCrLf & _
                      "Number: " & Err.Number & vbCrLf & _
                      "Desc.: " & Err.Description & vbCrLf & vbCrLf & _
                      "Please contact your Database Administrator.", _
                      vbCritical, "WARNING!"
                  Exit Sub
              End Sub
              Notice lines 8-11. See how we have identified the employee, then we use that to look up that employee's milestones. If there are none assigned to that employee, the fMilestone flag is set to false. Then, we use that result to figure out what to do. If the mployee has milestones (and you can modify the DLookup criteria to match whatever you need to suit your purposes), then we go to that particular record. If not, then we filter the subform to show no records.

              A couple additional thoughts (to help you out in the long-term, bigger scheme of things).

              First, when posting your code, try, if at all possible, to post the entire procedure. Unless the procedure is huge and you are only having issues with a tiny portion of it. Yours is small enough that seeing the whole procedure is helpful. And always do a fresh copy and paste directly from your VBA editor.

              Second, I have added "Error Handling" to your code (the On Error GoTo EH at the beginning of your code and EH at the end of your Code). This is a good habit to get into with all of your procedures (I am a notorious "harpy" about error handling on this forum, but I can't over emphasize the need for it). This keeps your code from "breaking" or simply dying in the middle of a user's session.

              I've made some changes with how you manage your recordset. Notice lines 28 and 30-32. You should always close all your recordsets and DBs, and then set them to nothing. This frees up computer resources and can prevent other problems within your DB engine. Always another good habit to get into, not to mention that having extraneous declared objects open can cause the DB to sometimes get confused.

              Finally, notice line 22. Even though you may KNOW that you will have at least one record in your recordset (and probably in your case only one record), it is also another good habit to check to see if there are any records before you start using the recordset's values. failing to do so can cause an error in those rare cases when there is no record.

              Probably more than you wanted at this time, but these principles will help you along the way no mater how far you go with your Access/VBA projects.

              Let me know how these changes worked out.

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32633

                #8
                Guys.

                Be very careful of using Now() to compare with dates. Now() is a Date/Time value that includes the time element of now. Date() is another matter entirely and is nearly always what you need. Using Now() is one of those common errors used by developers until they realise that it really isn't what they mean at all.

                Good luck :-)

                Comment

                • Karen20785
                  New Member
                  • May 2018
                  • 10

                  #9
                  Thanks Twinnyfo and NeoPa,

                  Sorry for the delay in responding back. I had back-to-back meetings all day. I definitely will try your suggestions tomorrow and let you how it works out. Karen

                  Comment

                  • NeoPa
                    Recognized Expert Moderator MVP
                    • Oct 2006
                    • 32633

                    #10
                    No worries on that score Karen.

                    We fully understand that people work and have lives and all that stuff. We're the same too ;-)

                    Comment

                    • Karen20785
                      New Member
                      • May 2018
                      • 10

                      #11
                      Thanks Twinnyfo and NeoPa,

                      I just wanted to let you know that I tried your suggestions and the form now works perfectly! I really appreciate your help!

                      Karen

                      Comment

                      • NeoPa
                        Recognized Expert Moderator MVP
                        • Oct 2006
                        • 32633

                        #12
                        It was a pleasure to give what little help I did Karen.

                        Clearly TwinnyFo did all the heavy lifting. I just dropped a little guidance in where I thought it may be helpful.

                        Comment

                        • Karen20785
                          New Member
                          • May 2018
                          • 10

                          #13
                          I found every bit of guidance very helpful since I'm still learning VBA. I do have a somewhat related question and could use your help. I posted a new thread: https://bytes.com/topic/access/answe...ds#post3815999

                          Comment

                          Working...