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

[Karen20785]

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:

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:

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)

[twinnyfo]

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]:

    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!

[Karen20785]

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:

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?

[twinnyfo]

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:

With Me.frmvw_milestones.Form
    .Filter = "ID_Staff = 0"
    .FilterOn = True
End With

Hope this hepps!

[Karen20785]

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)

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
 

[Karen20785]

I think I found a solution:

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
 

[twinnyfo]

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.

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.

[NeoPa]

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 :-)

[Karen20785]

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

[NeoPa]

No worries on that score Karen.

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