Results 1 to 5 of 5
  1. #1
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54

    Fill table with range of dates (some may exist)

    I hope everyone is well. I need some help with inserting records using VBA. Thanks in advance for your help, which is always great!

    Subform will open a dialog which contains a subform showing the resulting list, among other things.

    The calling form has the following relevant fields:
    txtDetailID
    txtStartDate
    txtEndDate


    cboPlot (values either "Daily" or "Weekly")

    The receiving table, tbleIODetails_Dates, has the following fields:
    Date_ID (PK)
    Detail_ID (many side of OTM relationship with the underlying table of the calling subform).
    Air_Date
    Air_Week
    Other fields are not really relevant to the filling process, the user will interact with them on the dialog, and I have those processes working correctly.

    So the calling subform looks at cboPlot to determine whether tblIODetails_Dates should be filled in the Air_Date column or Air_Week column, with the range being between txtStartDate and txtEndDate. Filling Air_Week should just be incremental dates, obviously. Air_Weeks fills with Monday dates.

    The procedure needs to test first whether there are already records matching txtDetailID and the range of dates, and skip the dates or weeks which are already there, in case the user has changed his range of dates on editing his records, for example.

    As always, I appreciate your help!

    MIB1019

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Instead of two date fields, consider 1 field along with another field for plot type. Can set a compound index with Detail_ID and DetailDate field to prevent duplicate pairs.
    This is more normalized structure and will simplify code.
    Might want to delete existing records for Detail_ID to handle revised range.
    Consider code:
    Code:
    Sub AddRecs(iID As Integer, dStart As Date, dEnd As Date, sPlot As String)
    Dim db As DAO.Database
    Dim dDte As Date
    Set db = CurrentDb
    db.Execute "DELETE FROM tbleIODetails WHERE Detail_ID=" & iID
    dDte = dStart
    If sPlot = "Weekly" And Weekday(dStart, vbMonday) <> 1 Then
        dDte = DateAdd("d", 1 - Weekday(dStart, vbMonday), dStart) + 7
    End If
    Do Until dDte > dEnd
        db.Execute "INSERT INTO tbleIODetails(Detail_ID,DetailDate,Plot) VALUES(" & iID & ",#" & dDte & "#,'" & sPlot & "')"
        dDte = dDte + IIf(sPlot = "Daily", 1, 7)
    Loop
    End Sub
    
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Thanks for that compact code!

    One issue: there are other fields in tblIOdetails_dates whose values I would not want to lose, so a 'second pass' of filling table with dates shouldn't delete all the records related to DetailID. Code should add dates between ranges that don't already exist.

    I'll consider the combining of date fields.

    Thanks again!

    Thanks!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Okay, remove the DELETE action. But a little confused why would want to retain records outside a revised range.

    Setting a compound index will prevent duplicate pairs which means don't need to check if that pair already exists, the INSERT will just not happen for that pair.

    If you don't reduce to a single date field, then code will have to be modified with If Then Else or IIf() to determine which field to build into the INSERT action SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Thank you very much! This will work!

    MIB1019

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

Similar Threads

  1. Replies: 21
    Last Post: 06-23-2020, 11:45 AM
  2. Add a range of dates to a table
    By gemadan96 in forum Programming
    Replies: 3
    Last Post: 06-20-2014, 01:49 PM
  3. fill a table with a sequence of hourly dates
    By maxbre in forum Programming
    Replies: 2
    Last Post: 04-16-2014, 01:36 AM
  4. Replies: 1
    Last Post: 10-28-2013, 01:54 PM
  5. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 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