none
Calculate future date based off frequency selected RRS feed

  • Question

  • I’ve got a table called “Rqmttbl” with a field called Frequency (text data type) and another table called MbrRqmttbl with a field called DateComplete (date/time data type).  The Rqmttbl is a one to many relationship with the MbrRqmttbl and the relationship is linked by a field in each table called RqmtID.

    I’d like to setup the Frequency field as a combo box with a number of options to select as far as how often a requirement is due.  Probably like in 6 month increments up to 3 years so the frequency combo box would display something like:  6 months, 12 months, 18 months, 24 months, 30 months, 36 months, and also an option for “one time only.” 

    I have these tables setup in a form as a form/subform (via a query for each) and I think I would need to create an unbound field in the subform called DateDue where I’d like for it to add the frequency selected from the frmRqmtMbrEntry (parent form) to the DateComplete field in the frmMbrRqumt (subform) to get the date the requirement is due again. 

    I’d also like to be able to display the form/subform in a report that would show who is coming due that particular requirement within 90 days, 60 days, 30 days.  Please let me know if I left out any details that might help.


    TAK

    Friday, July 24, 2015 3:52 PM

Answers

  • Hi,

    If I understand it correctly, you can set up the frequency combobox so that the bound column is a number. That way, you can use the DateAdd() function to calculate the due date. For example: DateAdd("m",ComboboxName,DateCompleted)

    If the frequency changes, such as some tests are due in weeks while others are due in months, then you have two choices: (1) you can convert the intervals into the least common frequency. For example, if one test is due every two weeks and another every month, then you can use 2 for the first test and 8 for the second one. For instance:

    DateAdd("ww",2,DateCompleted) 'due every 2 weeks, and

    DateAdd("ww",8,DateCompleted) 'due every 2 months

    Or, (2) you can add a frequency column to the combobox. For example: 2;"ww";2;"m";6;"m"; and so on...

    That way, your function would look like this: DateAdd(Combobox.IntervalColumn, Combobox.FrequencyColumn, DateCompleted)

    And as for tying the frequency combobox with the date completed field, I think the easiest solution is to use a query for your subform that joins the two tables, so you can create a calculated column for the due date textbox.

    Hope that helps...

    • Marked as answer by taking Friday, July 31, 2015 10:19 PM
    Monday, July 27, 2015 4:00 PM
  • Hi taking,

    If you want to use a combo box to determine the time frequency, I would suggest designing a table as shown in the following:

    Field Name

    Data Type / Sz

    FreqID

    AutoNumber

    FreqName

    String (15)

    FreqInterval

    String (4)

    FreqNumber

    Long Integer

    FreqID

    FreqName

    FreqInterval

    FreqNumber

    1

    30 Days

    d

    30

    2

    60 Days

    d

    60

    3

    90 Days

    d

    90

    4

    6 Months

    m

    6

    5

    12 Months

    m

    12

    6

    18 Months

    m

    18

    7

    24 Months

    m

    24

    8

    30 Months

    m

    30

    9

    36 Months

    m

    36

    10

    One Time Only

    d

    0

    I would then create a function to calculate the future date given the start date and the saved FrequencyID as shown in the following:

    Function CalcFrequency(StartDate As Variant, FrequencyID As Long) As Variant
    
        On Error GoTo Err_Process
        
        Dim rst1 As Recordset
        Dim dbs1 As Database
        Dim strSQL As String
        Dim strMsg As String
        Dim varReturn As Variant
        
        varReturn = Null
        
        If (IsDate(StartDate)) Then
            strSQL = "SELECT FreqInterval, FreqNumber FROM Frequecy WHERE FreqID=" & FrequencyID
        
            Set dbs1 = CurrentDb
            Set rst1 = dbs1.OpenRecordset(strSQL, dbOpenSnapshot)
        
            With rst1
                If (Not .EOF) Then
                    varReturn = DateAdd(.Fields!FreqInterval, .Fields!FreqNumber, StartDate)
                End If
            End With
            rst1.Close
        End If
        
    Exit_Process:
        Set rst1 = Nothing
        Set dbs1 = Nothing
        CalcFrequency = varReturn
        
    Err_Process:
        strMsg = "Procedure: CalcFrequency" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description
        MsgBox strMsg, vbExclamation, "Error"
        Resume Exit_Process
        
    End Function

    You can then place a call to your function in your queries and/or forms to get the future date.



    • Edited by RunningManHD Monday, July 27, 2015 4:45 PM
    • Marked as answer by taking Friday, July 31, 2015 10:19 PM
    Monday, July 27, 2015 4:32 PM
  • I can try zipping it up.  Maybe then it will pass.  Give me one moment...

    OK, give the following link a try:

    https://onedrive.live.com/redir?resid=E55A76D6EB477199!120&authkey=!AGa90owc4_xKZtM&ithint=file%2czip

    Wednesday, July 29, 2015 2:55 PM

All replies

  • Hi,

    It's hard to imagine what you're working with without seeing it but if you want to use comboboxes, it might be simpler if you set up your requirements table (or even perhaps a separate frequency table) to include all the intervals you want to display in the combobox, so you can just modify the row source based on the requirement (ID?). Hope that makes sense...

    Friday, July 24, 2015 3:59 PM
  • Below is how I've got the form/subform setup.  If I make the "Frequency" field into a combobox with set options to select (the frequency of recurring requirements can vary, usually in increments of 6 months though), how can I have this field associated with the "DateComplete" field in the subform to populate the "DateDue" field in the subform based off the "Frequency" field in the parent form? 

    So my first part is A: how do I need to setup the frequency combo box format and B: how do I tie that into the subform DateComplete field to reflect in the DateDue field.  Or If it is easier (or makes better sense), should I just add Conditional Formatting to the DateComplete field to turn yellow if the date is getting within 90 days of the frequency selected in the Requirements form and then red after it is past the frequency it was due. If this option, how do I set this up?   


    TAK

    Monday, July 27, 2015 3:35 PM
  • Hi,

    If I understand it correctly, you can set up the frequency combobox so that the bound column is a number. That way, you can use the DateAdd() function to calculate the due date. For example: DateAdd("m",ComboboxName,DateCompleted)

    If the frequency changes, such as some tests are due in weeks while others are due in months, then you have two choices: (1) you can convert the intervals into the least common frequency. For example, if one test is due every two weeks and another every month, then you can use 2 for the first test and 8 for the second one. For instance:

    DateAdd("ww",2,DateCompleted) 'due every 2 weeks, and

    DateAdd("ww",8,DateCompleted) 'due every 2 months

    Or, (2) you can add a frequency column to the combobox. For example: 2;"ww";2;"m";6;"m"; and so on...

    That way, your function would look like this: DateAdd(Combobox.IntervalColumn, Combobox.FrequencyColumn, DateCompleted)

    And as for tying the frequency combobox with the date completed field, I think the easiest solution is to use a query for your subform that joins the two tables, so you can create a calculated column for the due date textbox.

    Hope that helps...

    • Marked as answer by taking Friday, July 31, 2015 10:19 PM
    Monday, July 27, 2015 4:00 PM
  • Hi taking,

    If you want to use a combo box to determine the time frequency, I would suggest designing a table as shown in the following:

    Field Name

    Data Type / Sz

    FreqID

    AutoNumber

    FreqName

    String (15)

    FreqInterval

    String (4)

    FreqNumber

    Long Integer

    FreqID

    FreqName

    FreqInterval

    FreqNumber

    1

    30 Days

    d

    30

    2

    60 Days

    d

    60

    3

    90 Days

    d

    90

    4

    6 Months

    m

    6

    5

    12 Months

    m

    12

    6

    18 Months

    m

    18

    7

    24 Months

    m

    24

    8

    30 Months

    m

    30

    9

    36 Months

    m

    36

    10

    One Time Only

    d

    0

    I would then create a function to calculate the future date given the start date and the saved FrequencyID as shown in the following:

    Function CalcFrequency(StartDate As Variant, FrequencyID As Long) As Variant
    
        On Error GoTo Err_Process
        
        Dim rst1 As Recordset
        Dim dbs1 As Database
        Dim strSQL As String
        Dim strMsg As String
        Dim varReturn As Variant
        
        varReturn = Null
        
        If (IsDate(StartDate)) Then
            strSQL = "SELECT FreqInterval, FreqNumber FROM Frequecy WHERE FreqID=" & FrequencyID
        
            Set dbs1 = CurrentDb
            Set rst1 = dbs1.OpenRecordset(strSQL, dbOpenSnapshot)
        
            With rst1
                If (Not .EOF) Then
                    varReturn = DateAdd(.Fields!FreqInterval, .Fields!FreqNumber, StartDate)
                End If
            End With
            rst1.Close
        End If
        
    Exit_Process:
        Set rst1 = Nothing
        Set dbs1 = Nothing
        CalcFrequency = varReturn
        
    Err_Process:
        strMsg = "Procedure: CalcFrequency" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description
        MsgBox strMsg, vbExclamation, "Error"
        Resume Exit_Process
        
    End Function

    You can then place a call to your function in your queries and/or forms to get the future date.



    • Edited by RunningManHD Monday, July 27, 2015 4:45 PM
    • Marked as answer by taking Friday, July 31, 2015 10:19 PM
    Monday, July 27, 2015 4:32 PM
  • Below is how I've got the form/subform setup.  If I make the "Frequency" field into a combobox with set options to select (the frequency of recurring requirements can vary, usually in increments of 6 months though), how can I have this field associated with the "DateComplete" field in the subform to populate the "DateDue" field in the subform based off the "Frequency" field in the parent form? 

    So my first part is A: how do I need to setup the frequency combo box format and B: how do I tie that into the subform DateComplete field to reflect in the DateDue field.  Or If it is easier (or makes better sense), should I just add Conditional Formatting to the DateComplete field to turn yellow if the date is getting within 90 days of the frequency selected in the Requirements form and then red after it is past the frequency it was due. If this option, how do I set this up?   

    Your combo box will require the following properties:

    • Row Source: "SELECT FreqID, FreqName FROM Frequency ORDER BY FreqName"
    • Control Source: FrequencyID
    • Bound Column: 1
    • Column Count: 2
    • Column Widths = 0";1"

    Date Complete will be handled as a manual entry field

    Date Due is unbound and is calculated using the procedure provided above.

    Monday, July 27, 2015 4:55 PM
  • I created the table you mentioned and called it FreqTbl.  Where do I put the Function mentioned above?  Also, how/where do I place a call for this function in the form or subform? 

    TAK


    I added the above Function as a Module and called the Module "CalculateFreq".  Is this the correct place to put this function?  Also, should I change the field "StartDate" from the above function to "DateComplete" to match my user entry field in the subform?
    • Edited by taking Monday, July 27, 2015 6:25 PM
    Monday, July 27, 2015 5:49 PM
  • I created the table you mentioned and called it FreqTbl.  Where do I put the Function mentioned above?  Also, how/where do I place a call for this function in the form or subform? 

    TAK

    You place the function in a standard module.

    You call the function in a query, or from a control on your form as the control source.

    Monday, July 27, 2015 6:21 PM
  • In my underlying table called Rqmttbl should I just leave the frequency field as a text field or create it as a combo box like you mentioned above?  Or should the above combo box be created in the actual frmRqmtMbrEntry?

    TAK

    Monday, July 27, 2015 6:28 PM
  • In my underlying table called Rqmttbl should I just leave the frequency field as a text field or create it as a combo box like you mentioned above?  Or should the above combo box be created in the actual frmRqmtMbrEntry?

    TAK

    First, you'd be doing yourself and others that must follow behind you a big favor if you used a better naming convention for your tables.  I assume that Rqmttbl is actually a table intended to store Requirements.  Is that right?  So why don't you name your table Requirement or Requirements?  It really isn't necessary to add a prefix or suffix to table names.  Outside of the Access world, most designers do not name their tables using such conventions.  However, even still, you should not abbreviate a table name unless absolutely necessary.  "Rqmttbl" appears cryptic.  Same goes for "Freqtbl."  If you must use the tbl indicator, then I suggest that you use it in one of the following ways:

    • tblRequirement
    • RequirementTbl
    • tbl_Requirement
    • Requirement_Tbl

    In addition, many professional designers prefer to name tables in the singular rather than the plural form.  So once again, your one table should be named "Requirement," and the new table should be named "Frequency."  One particular reason is that it reduces the number of characters used in the naming process, making it easier to read and type.  The fewer characters there are to type, the less chance of making mistakes.  In queries and other places, fewer characters makes long statements easier to read.

    In the Requirement table, you should have a field for FrequencyID.  It should be a long integer data type to match the id field in the reference table you just created "Frequency."  You should then define the relationship between Requirement and Frequency for referential integrity. 

    Requirement.FrequencyID <One To Many> Frequency.FreqID

    As for making the field into a combo box, it's better to create the combo box on your interface form rather than in the table.







    Monday, July 27, 2015 6:54 PM
  • Looks like I got the combobox working right and the function is added as a standard module.  How do I call the function in the underlying query or on the form?


    TAK

    Would it be better to call the function in the query? Just wondering because I'm also wanting to be able to print each of the requirements showing member names and dates done and coming due. 

    All I changed in the function you provided is StartDate to DateComplete, is this correct?  I did change the table name to Frequency and added the field FrequencyID to the Rqmttbl and created the relationship.

    • Edited by taking Monday, July 27, 2015 8:10 PM
    Monday, July 27, 2015 7:59 PM
  • All I changed in the function you provided is StartDate to DateComplete, is this correct?  I did change the table name to Frequency and added the field FrequencyID to the Rqmttbl and created the relationship.

    No! I already explained that your completion date is a manual entry for when the task is complete. The system can't compute such things. What it can compute is when the task should be complete, so it requires a start date and an interval in time to forecast the date the task should be completed.  Therefore, it is StartDate + days or months = anticipated completion date or DueDate.

    Your field "DateComplete" should be the actual date the task is completed...


    Monday, July 27, 2015 8:21 PM
  • Looks like I got the combobox working right and the function is added as a standard module.  How do I call the function in the underlying query or on the form?


    TAK

    Would it be better to call the function in the query? Just wondering because I'm also wanting to be able to print each of the requirements showing member names and dates done and coming due. 


    SELECT Field1, Field2, Field3, ...,  StartDate, CalcFrequency(StartDate, FrequencyID) As DateDue FROM Requirement;

    Monday, July 27, 2015 8:27 PM
  • I'm probably confusing things with the way I have some fields labeled.  The way I have right now is the DateComplete field is the manual entry field for when the member completed the requirement.  When that date is entered the DateDue field (unbound) would populate to the future date (based off what was selected from the FrequencyID in the parent form) and show when that requirement is due again.  In the control source of the unbound DateDue field I have the control source as: =CalcFrequency([StartDate],[FrequencyID])

    Is this correct for calling the Function? What is displaying in the DateDue (unbound) field is this error #Name? when I'm looking at the form/subform in form view.


    TAK

    Monday, July 27, 2015 9:20 PM
  • I assume that the error issue is because you used the name [StartDate] as an input parameter for the function.  StartDate is just my name for whatever field I assume you must have to indicate when the task is expected to begin.

    Do you not have such a field in your table?

    Monday, July 27, 2015 9:39 PM
  • Hi,

    Pardon me for jumping in, but I suspect the #Name error may be because [FrequencyID] is in the Parent form rather than the subform. If so, try using [Parent].[FrequencyID]. Just a thought...

    Monday, July 27, 2015 10:13 PM
  • I agree, if he's using this in his subform, then you are correct.  But, my last post still may be applicable as well.
    Monday, July 27, 2015 10:16 PM
  • I agree, if he's using this in his subform, then you are correct.  But, my last post still may be applicable as well.

    Agreed! I think it's good to take a look at both of them to cover all possible issues. Just my 2 cents...
    Monday, July 27, 2015 10:19 PM
  • FrequencyID is in the parent form and DateComplete is in the subform.  So DateComplete is the actual date the member completed the task and the unbound field in the subform (called DateDue) will be the field where I put the function right? 

    Should I insert [frmRqmtMbrEntry].[FrequencyID] in all instances in the function where FrequencyID is located?


    TAK

    Tuesday, July 28, 2015 1:19 PM
  • FrequencyID is in the parent form and DateComplete is in the subform.  So DateComplete is the actual date the member completed the task and the unbound field in the subform (called DateDue) will be the field where I put the function right? 

    Should I insert [frmRqmtMbrEntry].[FrequencyID] in all instances in the function where FrequencyID is located?


    TAK

    No!  You don't need to change the function at all.  You supply the second input argument with the FrequencyID, referenced accordingly as to wherever it may come from.  In your case, it is located on the parent form, so you need to write the call to the function as:

    DateDue.Control Source: =CalcFrequency(<Your Task Start Date>, [Parent].[FrequencyID])

    Tuesday, July 28, 2015 2:03 PM
  • Below is the Function I used (as you presented earlier) and it is called CalcFrequency:

    Function CalcFrequency(StartDate As Variant, FrequencyID As Long) As Variant

        On Error GoTo Err_Process
       
        Dim rst1 As Recordset
        Dim dbs1 As Database
        Dim strSQL As String
        Dim strMsg As String
        Dim varReturn As Variant
       
        varReturn = Null
       
        If (IsDate(StartDate)) Then
            strSQL = "SELECT FreqInterval, FreqNumber FROM Frequecy WHERE FreqID=" & FrequencyID
       
            Set dbs1 = CurrentDb
            Set rst1 = dbs1.OpenRecordset(strSQL, dbOpenSnapshot)
       
            With rst1
                If (Not .EOF) Then
                    varReturn = DateAdd(.Fields!FreqInterval, .Fields!FreqNumber, StartDate)
                End If
            End With
            rst1.Close
        End If
       
    Exit_Process:
        Set rst1 = Nothing
        Set dbs1 = Nothing
        CalcFrequency = varReturn
       
    Err_Process:
        strMsg = "Procedure: CalcFrequency" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description
        MsgBox strMsg, vbExclamation, "Error"
        Resume Exit_Process
       
    End Function

    This is the screenshot of what I've got in the control source of the unbound text field in the subform called DateDue:  (the parent form that has both the form & subform is called frmRqmtMbrEntry)  - DateComplete is the field where we will enter the date the requirement was complete for the member and the unbound DateDue will be the field that populates the future date the requirement is due based off the frequency selected in the parent form.

    I'm still getting the #Name? error in the unbound DateDue subform field.


    TAK

    Unbound Control Source for DateDue:
    =CalcFrequency([DateComplete],[frmRqmtMbrEntry].[FrequencyID])
    • Edited by taking Tuesday, July 28, 2015 3:26 PM
    Tuesday, July 28, 2015 3:24 PM
  • Re: "I'm still getting the #Name? error in the unbound DateDue subform field."

    I think that's because you still haven't properly resolved the reference to the FrequencyID textbox in the parent or main form. As we have suggested earlier, try using the [Parent] keyword. For example:

    =CalcFrequency([DateComplete],[Parent].[FrequencyID])

    If that doesn't work, you can continue to use the syntax you're using with the absolute reference to the main form. For example:

    =CalcFrequence([DateComplete],Forms!frmRqmtMbrEntry.FrequencyID)

    Hope that's clearer...

    Tuesday, July 28, 2015 3:38 PM
  • Tak,

    I've explained several times now that DateComplete is not to be used for the calculation of when the Requirement is due to be complete.  Unless I'm missing something, DateComplete is the actual date you will have completed the task.  The first argument of the function should be the date you intend to start the task.  Then the function can calculate how far out from the start date you will expect to complete the task (DateDue).   

    Again, unless I'm not following you correctly, you should have something like the following:

    RequirementID

    The record id

    RequirementName

    The name of the requirement

    RequirementDesc

    An optional description of the requirement

    TaskStartDate

    The date the requirement task will begin

    TaskCompleteDate

    The date the requirement task is actually completed

    TaskDueDate

    The date the requirement is expected to be completed (calculated   using TaskStartDate and time frequency)

    =CalcFrequency(TaskStartDate, [Parent].[FrequencyID])


    Tuesday, July 28, 2015 3:47 PM
  • Below is a screenshot of how I've got the form/subform setup.  On the top part I'd enter the requirement and then select the Frequency (from the dropdown).  On the "Add Member below to the above requirement" I will enter the member name and put the Date Complete (which is the last time that requirement was accomplished by that member.  Then the Date Due (unbound field) would populate based off the Function when this requirement is to be accomplished again (based off the Frequency selected).  So, when the member accomplishes the requirement again I'd change the Date Complete to the most recent date they did the requirement and the Date Due would again recalculate the date that requirement is due again in the future based off the frequency for the requirement. 


    TAK

    Tuesday, July 28, 2015 4:06 PM
  • I tried both options you suggested in the unbound DateDue field control source but still get the same #Name? error.  I agree it's got to be reference and not being able to recognize it.  When you mentioned the FrequencyID being a textbox, I've got it as a combobox, could this be the issue?

    TAK


    FrequencyID is the DataType "Number" in the underlying Rqmttbl table though.
    • Edited by taking Tuesday, July 28, 2015 4:18 PM
    Tuesday, July 28, 2015 4:16 PM
  • Below is a screenshot of how I've got the form/subform setup.  On the top part I'd enter the requirement and then select the Frequency (from the dropdown).  On the "Add Member below to the above requirement" I will enter the member name and put the Date Complete (which is the last time that requirement was accomplished by that member.  Then the Date Due (unbound field) would populate based off the Function when this requirement is to be accomplished again (based off the Frequency selected).  So, when the member accomplishes the requirement again I'd change the Date Complete to the most recent date they did the requirement and the Date Due would again recalculate the date that requirement is due again in the future based off the frequency for the requirement. 


    TAK


    That doesn't make sense to me for one reason.  When you initially add a record to the member subform, I assume you would not have a complete date yet.  You still want to see when the task is due, regardless.  Right?  You seem to be missing the one important element here which is the date the task is set to begin.  If you have the member working on the task further after the initial completion date, you then add a new record for the same member with a new start date, the calculated due date.  And when task is done, you update record by adding the date the task was completed.
    Tuesday, July 28, 2015 4:21 PM
  • I tried both options you suggested in the unbound DateDue field control source but still get the same #Name? error.  I agree it's got to be reference and not being able to recognize it.  When you mentioned the FrequencyID being a textbox, I've got it as a combobox, could this be the issue?

    TAK


    FrequencyID is the DataType "Number" in the underlying Rqmttbl table though.

    Hi. Not trying to overstep my bounds here, but I think if you're willing, you can email me a copy of your db, so we can fix the problem sooner.

    Just a thought...

    Tuesday, July 28, 2015 4:31 PM
  • I tried both options you suggested in the unbound DateDue field control source but still get the same #Name? error.  I agree it's got to be reference and not being able to recognize it.  When you mentioned the FrequencyID being a textbox, I've got it as a combobox, could this be the issue?


    TAK


    FrequencyID is the DataType "Number" in the underlying Rqmttbl table though.

    Hi. Not trying to overstep my bounds here, but I think if you're willing, you can email me a copy of your db, so we can fix the problem sooner.

    Just a thought...

    DB Guy, I'm not as concerned at the moment about his #Name? error as I am on his comprehension of what is required to reach his goal.  He seems to want to create a record for a Requirement task and assign it to a member of the team.  When the member completes the task, he seems to want to utilize the same record again to indicate that the task has been extended.  See my last post and my advice against such management.
    Tuesday, July 28, 2015 4:50 PM
  • There will be many members listed in the subform for each requirement and each person may have a different date they completed a requirement.  I'm sure there will be members who haven't completed a requirement before and their DateComplete would be empty until that requirement is done initially.  Requirements can be anything from date CPR was completed by each member to when they did another required course.  Therefore, to me, it would make sense to just change/enter the DateComplete for the member within that requirement each time they initially accomplish or re-accomplish a recurring requirement and showing when that requirement is due again for each person.


    TAK

    Tuesday, July 28, 2015 5:44 PM
  • Sure, what's your email address.  It's got to be something I'm doing wrong or configured wrong.  But I think I'm following what RunningManHD has instructed.

    TAK

    Tuesday, July 28, 2015 6:39 PM
  • Sure, what's your email address.  It's got to be something I'm doing wrong or configured wrong.  But I think I'm following what RunningManHD has instructed.

    TAK


    My email address is thedbguy[at]gmail.com (Note: replace [at] with @). I'm just trying to help...
    Tuesday, July 28, 2015 6:46 PM
  • Sure, what's your email address.  It's got to be something I'm doing wrong or configured wrong.  But I think I'm following what RunningManHD has instructed.

    TAK

    Tak,

    Why don't you post your db to OneDrive so both of us can have a look.

    Thanks

    Tuesday, July 28, 2015 7:40 PM
  • I haven't used One Drive before but will check it out when I get home.  I don't have the rights to install anything on my work computer.

    TAK

    Tuesday, July 28, 2015 7:54 PM
  • As you have an account to post here on the MS website, you also have a OneDrive account that you can use to post files to.  When you upload your file to OneDrive, you will then want to right click on the file and Share it.  The system isn't the most intuitive.  But once you select Share from the short cut menu, perform the following steps:

    1. On the left side of the screen, click Get a link.
    2. On the right side of the screen, in the Choose an option combo box, select Edit.
    3. Click the button Create Link.  A text box will be displayed showing the link path to your file.
    4. Use your mouse and place the cursor over top of the text box, then left click the text to select the entire string.
    5. Copy the link path by pressing Ctrl+C.
    6. Return to your post and paste the link by pressing Ctrl+V
    Tuesday, July 28, 2015 8:29 PM
  • See if this works:

    https://onedrive.live.com/redir?resid=2A34F9392A3F83B9!113&authkey=!AETGQnS3SLbBOCc&ithint=file%2caccdb


    TAK


    Hi TAK. Thanks. I'll have to try it when I get home since I am not allowed to download anything from OneDrive at work. Hopefully, RM can get to it before I get a chance, so you don't have to wait too long. Cheers!
    • Edited by .theDBguy Tuesday, July 28, 2015 8:44 PM
    Tuesday, July 28, 2015 8:43 PM
  • No problem.  Thanks to both of you for the help!

    TAK

    Tuesday, July 28, 2015 9:08 PM
  • DB Guy, to save you any unnecessary effort, I have picked up on this and I am working on his DB now.  Of course you are welcome to add your "two cents" :)

    Thanks!

    Tuesday, July 28, 2015 10:54 PM
  • Tak,

    Below you will find the link to your revised database.  Look it over and let me know if you have questions or concerns.

    https://onedrive.live.com/redir?resid=E55A76D6EB477199!118&authkey=!AEYGf51ROXtfCuo&ithint=file%2caccdb

    Best Regards

    Tuesday, July 28, 2015 11:59 PM
  • DB Guy, to save you any unnecessary effort, I have picked up on this and I am working on his DB now.  Of course you are welcome to add your "two cents" :)

    Thanks!

    Right on! Thanks!
    Wednesday, July 29, 2015 12:48 AM
  • Tak,

    Below you will find the link to your revised database.  Look it over and let me know if you have questions or concerns.

    https://onedrive.live.com/redir?resid=E55A76D6EB477199!118&authkey=!AEYGf51ROXtfCuo&ithint=file%2caccdb

    Best Regards

    Tak,

    Please disregard the previous link and use the one below.  I walked through your db again this morning and realized that a couple of issues existed.  I have corrected them and you should be ready to fly with this new version.

    https://onedrive.live.com/redir?resid=E55A76D6EB477199!119&authkey=!AAJG5YAnjhP7y6o&ithint=file%2caccdb

    Please let me know if you have questions or concerns.

    Thanks,


    Wednesday, July 29, 2015 1:03 PM
  • Thanks RunningManHD!  I can't download the file for some reason.  I think we probably have a firewall for downloading certain type files at my office.  Or it might be a setting on my pc.  When I go to download it I get the message "Page Cannot be displayed."  I'll have to check it out when I get home tonight unless you have any suggestions.

    TAK

    Wednesday, July 29, 2015 1:25 PM
  • Wednesday, July 29, 2015 1:55 PM
  • I tried and I'm sure your link(s) work because I do see the dbase but it keeps blocking me from downloading.  Lots of restrictions on a mil base.

    TAK

    Wednesday, July 29, 2015 2:44 PM
  • I can try zipping it up.  Maybe then it will pass.  Give me one moment...

    OK, give the following link a try:

    https://onedrive.live.com/redir?resid=E55A76D6EB477199!120&authkey=!AGa90owc4_xKZtM&ithint=file%2czip

    Wednesday, July 29, 2015 2:55 PM
  • Hello Taking,

    Been following this thread and pardon the intrusion just making sure to understand your need.

    A task is completed and the date recorded. This gives you a calculated date in an unbound control to let you know the next scheduled Due Date. Is this correct?

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, July 29, 2015 9:36 PM
  • I was able to download the zip file from home but of course I'm running access 2007 at home and apparently some of the Navigation form features in 2007 are not available in 2010 or at least that's what the error message I'm getting says.  I've got 2010 at work though.  I did check out the changes made and these look great (naming convention, form setup, fix with the frequency calculation, etc)!  I'll take a closer look at what all was done tmrw and report back.   

    Thank you for all the help!  Thank goodness you guys are patient people.


    TAK


    Did you want to post the code correction? I know most of the previous responses are correct and just want to pick the right ones for answered.
    • Edited by taking Thursday, July 30, 2015 2:07 AM
    Thursday, July 30, 2015 2:00 AM
  • Yes, you are correct.  It looks like RunningManHD and DBGuy got me on the straight and narrow now.

    TAK

    Thursday, July 30, 2015 2:01 AM
  • Hi Tak,

    Just wondering how you are making out with the new db.  If everything is working and all looks good, please close out your post by marking it answered.  Otherwise, please feel free to post back with any questions or comments.

    Thanks,

    Friday, July 31, 2015 1:58 PM