Results 1 to 5 of 5
  1. #1
    easyrider is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44

    DateAdd: get N value from a combobox

    Hi everyone. I have a database of equipment owned by a company. Each piece of equipment needs to be inspected on a particular schedule, or frequency. I created a table called InspSched and placed monthly values in it - 3, 6, 12. Also added a drop-down combo box on the equipment detail form that pulls the schedule values from InspSched. The equipment detail form also has a text box called LastInsp.




    Trying to use the DateAdd function to determine when something is due for inspection. I currently have this VBA code assigned to a button:


    Private Sub DueDate_Click()

    Dim N As Integer
    Dim LastInsp As Date
    N = InspComboBox.Value
    LastDate = LastInsp.Value
    DueDate = DateAdd("m", N, LastDate)
    End Sub


    This works, sorta. The Interval parameter is being assigned the value of the primary key of InspSched instead of the actual Schedule field value of 3, 6 or 12.



    Any ideas?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try

    N = InspComboBox.column(1)

    for something like a 3 record table with unique numbers as values, you don't really need a PK, just set the values column to indexed, no duplicates

    you also don't need your multiline code or reference .value as that is the default

    Code:
    Private Sub DueDate_Click()
    
        DueDate = DateAdd("m", InspComboBox.column(1), LastInsp)
    
    End Sub
    and finally you should not be storing values that are easily calculated. On your form you could have an unbound textbox with

    = DateAdd("m", InspComboBox.column(1), LastInsp)

    as it's control source

    or in a query

    DueDate:
    DateAdd("m", InspComboBox.column(1), LastInsp)

  3. #3
    easyrider is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44
    Thanks, Ajax - that worked! Also, took your advice and made it a single line of code. And I'm not storing these values - I just want a due date to show up on the Equipment form to let the user know that this unit needs inspected. Thanks again!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If each asset has a set frequency why isn't your due date textbox on your form just adding the frequency to the last performed date? Instead you're picking frequency values from a combo, so does that mean you want to be able to pick a different value for an asset sometimes? If not, I think you're complicating it a bit.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    easyrider is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44
    Yes, the client wants the option/ability to change the frequency of inspections, and they're the boss!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DateAdd
    By nggman in forum Programming
    Replies: 2
    Last Post: 09-06-2018, 11:39 AM
  2. DateAdd
    By Bentley in forum Queries
    Replies: 3
    Last Post: 01-23-2017, 05:20 PM
  3. the DateAdd
    By azhar2006 in forum Queries
    Replies: 1
    Last Post: 04-20-2014, 03:18 PM
  4. DateAdd()
    By cbrsix in forum Programming
    Replies: 3
    Last Post: 11-22-2011, 02:20 PM
  5. dateadd help
    By dubsdj in forum Queries
    Replies: 3
    Last Post: 03-03-2011, 07:22 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