Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54

    Fill ListBox with dates in range between two dates on form's record

    I want to add a listbox to a continuous form subform, whereby the user can select one or more dates between two dates on the same record. User inputs at txtStartDate and txtEndDate. The Listbox should populate with dates in between in "DDD, mm/dd" format. Result of user selections would be inserted into a short text field in the underlying table.

    So if the user enters 6/1/20 at txtStartDate and 6/7/20 at txtEndDate, the listbox changes to these selections: Mon, 6/1; Tue 6/2; Wed 6/3, Thu 6/4, Fri 6/5, Sat 6/6 and Sun 6/7.

    Thanks in advance for your help!

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    So what's your question? What have you tried so far?

  3. #3
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54

    Fill unbound listbox with range of dates, write results to text box bound to table field.

    Thanks for your help!

    Added some notes in the form's module about formatting the list of dates etc. Would like the list to update on form events rather than with a button, since it will reside on continuous form records.

    Its purpose is to write the list of dates as "mm/dd, mm/dd, mm/dd" etc. in txtResult control, which is bound to a short text field in underlying table.

    Thanks for the help!

    Private Sub cmdGo_Click() 'would rather populate on afterupdate events on txtstartdate and txtenddate than use a button.




    Dim iDays As Integer, i As Integer, varDate As Date
    iDays = DateDiff("d", Me.txtStartDate, Me.txtEndDate)
    For i = 0 To iDays
    varDate = DateAdd("d", i, Me.txtStartDate) 'would like to format this as "ddd, mm/dd"
    Me.lstChooseDates.AddItem varDate
    Next
    End Sub
    Attached Files Attached Files

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I believe this will do what you've asked for:
    Code:
    Private Sub lstChooseDates_AfterUpdate()
        Dim i As Integer
        Dim dates As String
        
        dates = ""
        
        For i = 0 To Me.lstChooseDates.ListCount - 1
            If Me.lstChooseDates.Selected(i) Then
                dates = dates & Format(Me.lstChooseDates.ItemData(i), "MMM, mm/dd") & "; "
            End If
        Next i
        
        If dates <> "" Then
            'remove the last semicolon and space
            dates = Left(dates, Len(dates) - 2)
        End If
        
        Me.txtResult = dates
    End Sub
    BUT without knowing the details of your project it might be better to have a child table for these dates rather than to denormalize them to save it in a short text field. Good luck with your project.

  5. #5
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    The dates are really just for display purpose on the form and a printed order for a media buy; there won’t be any need for manipulation of the dates. So writing them to a text field will suffice.

    Thanks for your help with this. I can’t test at the moment but will let you know if I have any other questions.

    MIB

  6. #6
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    kd2017, your solution works perfectly in your ListBoxDemo file that you sent. I copied the procedures from the class module in your db and pasted it into the form's module in mine.

    The listbox is set to fill on the afterupdate event of txtEndDate. I can see the listbox actually change in that the vertical scroll bar appears. But no dates appear. I can select lines in the 'blank' listbox'.

    Debug.Print shows me the txtstartdate and txtenddate values, but does not show me dates that should be appearing in the listbox with each value as filled in the For-Next loop.

    Is the problem that the code is residing in the form's module and not in a class module? I am not very experienced with this stuff, obviously, and do not really grasp the difference.

    Thanks for your time!
    MIB1019

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I can't tell for sure without seeing your database but it sort of sounds like the listbox column count, column width, or bound column properties might be messed up.

  8. #8
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    That was the problem. It works as your form did now, except for two things: I'd like the format in the listbox to be "DDD, mm/dd". And the bigger issue is that the listbox repeats on every record of the continuous form. It, of course, only updates the txtResult field on the current record. Is there a way around that?

    Thanks for your excellent advice!
    MIB1019

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Study the format function from the previous code
    Replace Me.lstChooseDates.ItemData(i) with the date variable you want to format.

    Unfortunately any unbound controls in a continuous form will all display the same data on each record, your listbox is an unbound control.

  10. #10
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    kd2017, I have almost resolved my dilema. Maybe you can guide me just a little more in this.

    I opted to put the date select process on a popup dialog, since the continuous form displays the list box on each recored. The dialog popup is opened by double click on txtAirDates field, with openargs set to the values of txtStartDate and txtEndDate. The dialog's purpose, of course is to get the user's selection(s) of dates and return those values to the subform txtAirDates field when the user clicks OK on the dialog.

    It works perfectly so far. Double-click opens the dialog and populates the listbox as it should. I opted to display the selections in the listbox in a visible textbox, and the count of selections in another visible textbox.
    So if, say, I choose 4/11/2020, 4/18/2020 and 4/25/2020, the textbox updates correctly (with format function used, displays "4/11, 4/18, 4/25"). I can also see the 'count' textbox update correctly as I select or un-select dates in the list box.

    I have two buttons on the dialog, OK and Cancel. If I press Okay, I obviously want the values in the two visible textboxes, txtDatesSelected and txtAirCount, to be written to the corresponding fields on the subform. If I press Cancel, I want nothing to happen, for the dialog to just close (this works, of course).

    My problem is: How do I get the values to pass back to the subform controls? I think I have to set the Visible property of the dialog to False, write the values, then close the dialog. Where does the code to write those values go?

    Also, I still cannot get the dates in the listbox to format as 'ddd, mm/yy'. Putting the dateadd function inside the format function gives me a type mismatch.

    Hope all of this makes sense. I am almost there, with your help.

    MIB1019

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    See attached example database. I've used TempVars to save the results from the dialog form to a temporary global variable that the calling subform can then read after the dialog is closed.

    *There are other ways to do this, I do NOT know the best way to do it, but this works for me.
    Attached Files Attached Files

  12. #12
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    One more suggestion (see attachment) with interaction between the continuous form and the popup form.

    I hope helps.
    John
    Attached Files Attached Files

  13. #13
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    kd2017, your suggestions worked! One tiny issue is that I'd like the text box that gathers the dates to not include DDD in the format, for keeping the field contents, and ultimately the printed report more compact. I only need to see the days of the week in the picker itself, so I don't have to look at a calendar!

    You help is greatly appreciated!
    MIB1019

  14. #14
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    accesstos (John), thank you for that great example of how this could work! it would take good bit of redo at this point to incorporate your suggestion, and I've got a workable solution. But I will keep your sample file for future study/use.

    You folks are the best!
    MIB1019

  15. #15
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Quote Originally Posted by mib1019 View Post
    kd2017, your suggestions worked! One tiny issue is that I'd like the text box that gathers the dates to not include DDD in the format, for keeping the field contents, and ultimately the printed report more compact. I only need to see the days of the week in the picker itself, so I don't have to look at a calendar!

    You help is greatly appreciated!
    MIB1019
    KD2017, I figured that part out this morning, trimming off the DDD from the dates.

    Thanks again for your help

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-02-2017, 02:02 PM
  2. Replies: 2
    Last Post: 09-19-2017, 11:31 AM
  3. Creating Range of Dates in a Form
    By melb in forum Forms
    Replies: 6
    Last Post: 03-16-2015, 02:32 PM
  4. 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
  5. Replies: 10
    Last Post: 11-16-2011, 10:58 AM

Tags for this Thread

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