By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

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

P: 10
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_milestones). The Main form (FRM_staff_project) has the project information and it includes a subform (frmvw_milestones) 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Dim db As DAO.Database
  3.     Dim rst As DAO.Recordset
  4.     Dim strSQL As String
  5.     Dim TheDate As Date
  6.  
  7.     Set db = CurrentDb
  8.     strSQL = "SELECT max(Milestone_Date) as Dte " & _
  9.              "FROM qryvw_milestones " & _
  10.              "WHERE Milestone_Date<=Now()"
  11.     Set rst = db.OpenRecordset(strSQL)
  12.     TheDate = rst.Fields(0)
  13.     Me.Milestone_Date.SetFocus
  14.     DoCmd.FindRecord TheDate
  15.     Me.Milestone_Date.SetFocus
  16.  
  17. 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:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT max(Milestone_Date) as Dte " & _
  2.          "FROM qryvw_milestones " & _
  3.          "WHERE (Milestone_Date<=now() " & _
  4.          "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_projects) and the underlying query (qryvw_milestones)


Attached Images
File Type: jpg 2018-05-01_10-46-45 swoop project form.jpg (53.7 KB, 289 views)
File Type: jpg qryvw_milestones.jpg (57.0 KB, 291 views)
May 1 '18 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. On Error GoTo EH
  3.     Dim db          As DAO.Database
  4.     Dim rst         As DAO.Recordset
  5.     Dim strSQL      As String
  6.     Dim TheDate     As Date
  7.     Dim lngStaff    As Long
  8.     Dim fMilestone  As Boolean
  9.  
  10.     lngStaff = Me.ID_Staff
  11.     fMilestone = Nz(DLookup("ID_Staff", "qryvw_milestones", _
  12.         "ID_Staff = " & lngStaff), False)
  13.  
  14.     If fMilestone Then
  15.         strSQL = "SELECT max(Milestone_Date) as Dte " & _
  16.             "FROM qryvw_milestones " & _
  17.             "WHERE ((Milestone_Date<=now()) " & _
  18.             "AND (ID_staff = " & lngStaff & "))"
  19.         Set db = CurrentDb
  20.         Set rst = db.OpenRecordset(strSQL)
  21.         With rst
  22.             If Not .RecordCount = 0 Then
  23.                 TheDate = .Fields(0)
  24.                 Me.Milestone_Date.SetFocus
  25.                 DoCmd.FindRecord TheDate
  26.                 Me.Milestone_Date.SetFocus
  27.             End If
  28.             .Close
  29.         End With
  30.         db.Close
  31.         Set rst = Nothing
  32.         Set db = Nothing
  33.     Else
  34.         With Me!frmvw_milestones.Form
  35.             .Filter = "ID_Staff = 0"
  36.             .FilterOn = True
  37.         End With
  38.     End If
  39.  
  40.     Exit Sub
  41. EH:
  42.     MsgBox "There was an error loading the form!" & _
  43.         vbCrLf & vbCrLf & _
  44.         "Number: " & Err.Number & vbCrLf & _
  45.         "Desc.: " & Err.Description & vbCrLf & vbCrLf & _
  46.         "Please contact your Database Administrator.", _
  47.         vbCritical, "WARNING!"
  48.     Exit Sub
  49. 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.

Share this Question
Share on Google+
12 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
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_projects.ID_staff is part of the query qryvw_milestones. 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]:

Expand|Select|Wrap|Line Numbers
  1.     Dim lngStaff As Long
  2.     lngStaff = [FRM_staff_projects]![ID_staff]
  3.  
  4.     [all the rest of your code]
  5.  
  6.     strSQL = "SELECT max(Milestone_Date) as Dte " & _
  7.         "FROM qryvw_milestones " & _
  8.         "WHERE ((Milestone_Date<=now()) " & _
  9.         "AND (ID_staff = " & lngStaff & "))"
  10.  
  11.     [finish your code]
  12.  
  13.  
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!
May 1 '18 #2

P: 10
Thanks twinnyfo!

Yes, field tbl_pria_projects.ID_staff is part of the query qryvw_milestones. Your suggested coding worked with one slight modification:


Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim rst As DAO.Recordset
  3.     Dim strSQL As String
  4.     Dim TheDate As Date
  5.     Dim lngStaff As Long
  6.  
  7.     lngStaff = Me.ID_staff
  8.     Set db = CurrentDb
  9.  
  10.     strSQL = "SELECT max(Milestone_Date) as Dte " & _
  11.         "FROM qryvw_milestones " & _
  12.         "WHERE ((Milestone_Date<=now()) " & _
  13.         "AND (ID_staff = " & lngStaff & "))"
  14.     Set rst = db.OpenRecordset(strSQL)
  15.     TheDate = rst.Fields(0)
  16.     Me.Milestone_Date.SetFocus
  17.     DoCmd.FindRecord TheDate
  18.     Me.Milestone_Date.SetFocus
  19.  
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_milestones. Is there anyway to hide the subform or show a blank record when this occurs?
May 2 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
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:

Expand|Select|Wrap|Line Numbers
  1. With Me.frmvw_milestones.Form
  2.     .Filter = "ID_Staff = 0"
  3.     .FilterOn = True
  4. End With
Hope this hepps!
May 2 '18 #4

P: 10
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_milestones.Form with Me!formvw_milestones.Form)


Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim rst As DAO.Recordset
  3.     Dim strSQL As String
  4.     Dim TheDate As Date
  5.      Dim lngStaff As Long
  6.  
  7.     Set db = CurrentDb
  8.  
  9.     If Me.ID_staff <> 0 Then
  10.     lngStaff = Me.ID_staff
  11.     strSQL = "SELECT max(Milestone_Date) as Dte " & _
  12.         "FROM qryvw_milestones " & _
  13.         "WHERE ((Milestone_Date<=now()) " & _
  14.         "AND (ID_staff = " & lngStaff & "))"
  15.     Set rst = db.OpenRecordset(strSQL)
  16.     TheDate = rst.Fields(0)
  17.     Me.Milestone_Date.SetFocus
  18.     DoCmd.FindRecord TheDate
  19.     Me.Milestone_Date.SetFocus
  20.  
  21.  
  22.   Else
  23.   With Me!frmvw_milestones.Form
  24.     .Filter = "ID_Staff = 0"
  25.     .FilterOn = True
  26. End With
  27.  
  28.  
  29. End If
  30.  
May 2 '18 #5

P: 10
I think I found a solution:

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Done
  2.  Dim db As DAO.Database
  3.     Dim rst As DAO.Recordset
  4.     Dim strSQL As String
  5.     Dim TheDate As Date
  6.      Dim lngStaff As Long
  7.  
  8.     Set db = CurrentDb
  9.  
  10.  
  11.     lngStaff = Me.ID_staff
  12.  
  13.  
  14.     strSQL = "SELECT max(Milestone_Date) as Dte " & _
  15.         "FROM qryvw_milestones " & _
  16.         "WHERE ((Milestone_Date<=now()) " & _
  17.         "AND (ID_staff = " & lngStaff & "))"
  18.     Set rst = db.OpenRecordset(strSQL)
  19.     TheDate = rst.Fields(0)
  20.     Me.Milestone_Date.SetFocus
  21.     DoCmd.FindRecord TheDate
  22.     Me.Milestone_Date.SetFocus
  23.  
  24.  
  25. Done:
  26. Exit Sub
  27.  
May 2 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 3,284
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. On Error GoTo EH
  3.     Dim db          As DAO.Database
  4.     Dim rst         As DAO.Recordset
  5.     Dim strSQL      As String
  6.     Dim TheDate     As Date
  7.     Dim lngStaff    As Long
  8.     Dim fMilestone  As Boolean
  9.  
  10.     lngStaff = Me.ID_Staff
  11.     fMilestone = Nz(DLookup("ID_Staff", "qryvw_milestones", _
  12.         "ID_Staff = " & lngStaff), False)
  13.  
  14.     If fMilestone Then
  15.         strSQL = "SELECT max(Milestone_Date) as Dte " & _
  16.             "FROM qryvw_milestones " & _
  17.             "WHERE ((Milestone_Date<=now()) " & _
  18.             "AND (ID_staff = " & lngStaff & "))"
  19.         Set db = CurrentDb
  20.         Set rst = db.OpenRecordset(strSQL)
  21.         With rst
  22.             If Not .RecordCount = 0 Then
  23.                 TheDate = .Fields(0)
  24.                 Me.Milestone_Date.SetFocus
  25.                 DoCmd.FindRecord TheDate
  26.                 Me.Milestone_Date.SetFocus
  27.             End If
  28.             .Close
  29.         End With
  30.         db.Close
  31.         Set rst = Nothing
  32.         Set db = Nothing
  33.     Else
  34.         With Me!frmvw_milestones.Form
  35.             .Filter = "ID_Staff = 0"
  36.             .FilterOn = True
  37.         End With
  38.     End If
  39.  
  40.     Exit Sub
  41. EH:
  42.     MsgBox "There was an error loading the form!" & _
  43.         vbCrLf & vbCrLf & _
  44.         "Number: " & Err.Number & vbCrLf & _
  45.         "Desc.: " & Err.Description & vbCrLf & vbCrLf & _
  46.         "Please contact your Database Administrator.", _
  47.         vbCritical, "WARNING!"
  48.     Exit Sub
  49. 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.
May 2 '18 #7

NeoPa
Expert Mod 15k+
P: 31,491
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 :-)
May 3 '18 #8

P: 10
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
May 4 '18 #9

NeoPa
Expert Mod 15k+
P: 31,491
No worries on that score Karen.

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

P: 10
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
May 5 '18 #11

NeoPa
Expert Mod 15k+
P: 31,491
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.
May 6 '18 #12

P: 10
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
May 6 '18 #13

Post your reply

Sign in to post your reply or Sign up for a free account.