Multiselect Listbox show Selected if record exists in table

[mib1019]

Hi all. Thanks in advance for any advice here.

I have a simple dialog form called from a subform based on tblIODetails. A multiselect listbox populates with available 'air dates'. Unbound fields are populated with StartDate, EndDate and DetailID from the calling form.

The multiselect listbox populates with the dates between StartDate and EndDate. After user makes selections and closes the dialot, records are added to tblIODetails_Dates. Relevant fields in that table are Detail_ID, Air_Date, and these two fields are indexed as AirDate.

If the user comes back to that subform, needs to change a record and initiates a call to the dialog again, I want the listbox to show any dates that exist in tblIODetails_Dates as SELECTED.

What's the most efficient way to do this?

Thanks!
MIB1019

[June7]

Loop through listbox items and test for that item in table and if match, set that item as selected. Something like:

Private Sub Form_Load()
Dim x As Integer
With Me.lbxDates
For x = 0 To .ListCount - 1
    If Not IsNull(DLookup("Air_Date", "tblIODetails", "Detail_ID=" & Me.DetailID & " AND Air_Date=#" & .ItemData(x) & "#")) Then .Selected(x) = True
Next
End With
End Sub

[ranman256]

The best way is NOT use multi select. It requires programming.
instead use 2 lists: 1 items in the table, 1 list items not in the table.
both are done via query.
a double click can run an append query to add 1 to the other.

[mib1019]

Thank you both! The multiselect list box is not bound and is working correctly to set dates (instead of calendar control) for insertion into the table. Was looking for a visual for the user to know which dates were already there.

This should work. Great learning experience for me here!
MIB1019

[mib1019]

June7, please help me debug this little glitch. The format of the dates in lstSelectDates is "ddd, mm/yy".

Here is the code:

Private Sub Form_Load()
'populate the fields on the popup form
     
    Dim strOpenArgs As Variant
    
    strOpenArgs = Split(Me.OpenArgs, ";")
    
    Me!txtDetailID = CLng(strOpenArgs(0))
    Me!txtStart = CDate(strOpenArgs(1))
    Me!txtEnd = CDate(strOpenArgs(2))
    Me!txtIODetailProgram = strOpenArgs(3)
    
    'loop through the dates and add them to the list
    Dim StartDate, EndDate As Date
    StartDate = Me.txtStart
    EndDate = Me.txtEnd
    
    Dim i As Integer
    For i = 0 To EndDate - StartDate
        Me.lstSelectDates.AddItem Format(CDate(StartDate + i), "ddd m/d")
    Next i
    
    Dim x As Integer
    With Me.lstSelectDates
     For x = 0 To .ListCount - 1
        If Not IsNull(DLookup("Air_Date", "tblIODetails_Dates", "Detail_ID=" & Me.txtDetailID & " AND Air_Date=#" & .ItemData(x) & "#")) Then .Selected(x) = True
    Next
    End With
      
End Sub

[June7]

Well, need to convert string to a valid date. "ddd" is short name of weekday. What day of month do you want to use? Why aren't you including day number in that format?

[mib1019]

Wouldn't the data in the listbox already be dates, since the listbox fills using the CDate function?

Short answer to your question is that when the user closes the dialog, the value of a textbox called txtDatesSelected is returned to a field on the calling subform, for a printed order and user verification as he scrolls the subform. I have code that adds the dates to tblIODetails_Dates which works with that textbox, but haven't tested it yet. That'll be next.

There's been a changing horses in midstream on this project when it comes to these dates. In the beginning, I had the simple textbox to display dates that the user selected for each detail_ID record. That went to creating a table to capture the dates for reports. I had him working with a similar dialog doing input to the table itself but he complained that it's more work than simply selecting dates from a list like he did in the beginning. So I went back to the multiselect listbox and opened this can of worms.

Anyway, here is a picture of the dialog to explain.

The code that I have to (eventually) update the table with new or changed records uses the textbox values, splitting them up into valid dates.

Perhaps filling the listbox could include a second hidden column in proper date format mm/dd/yyyy and your selection code could use the second column instead. And then, perhaps, the SQL for updating the table could use that column as well....?

Hope that helps. You're sure helping me, and I thank you for that!

MIB1019

[June7]

Nope, those are strings in listbox and they are not strings that Access can recognize as valid dates.

CDate is embedded within Format function (CDate most likely serves no purpose in this case and is redundant). This Formatted string is what is committed to listbox and is not a valid date.

Exactly what is saved into Air_Date field - "ddd, m/d" or full date?

Revise my earlier question, string has month and day so what year should be used to construct valid date? Suggest modify code loading dates into listbox to include year. Save full date values into record. This will remove ambiguity.

[mib1019]

The fact that they are strings and not dates explains why the code I picked up to add the dates to the table on dialog unload is splitting the text in that Dates box.

Air_Date field in the tblIODetails_Date is mm/dd/yyyy.

So revising code to load listbox as you suggested...he is picking dates often Mon, Wed, Fri, or Tue, Thu so likes to see the weekdays. That what prompted my question on loading the listbox with two columns, the second one he wouldn't see and would be in format mm/dd/yyyy that your selecting code could work with. And could that second column be used in the SQL for loading the selections into the table on form unload, instead of working with the string in Dates textbox?

And while at it, perhaps a third column in the listbox to calculate the 'Air_Week'; Monday's date of the list's date. That Air_Week is a column in the table used for other type of plotting the user is doing on certain Detail_IDs, but would be handy to have that data for insertion to the table.

Thanks so much for your kindness.
MIB1019




I hope I am making some sense here.

[June7]

Yes, if you have a hidden column with full date value, reference it by its index. Column index begins with 0.

If Not IsNull(DLookup("Air_Date", "tblIODetails_Dates", "Detail_ID=" & Me.txtDetailID & " AND Air_Date=#" & .Column(1, x) & "#")) Then .Selected(x) = True

Sorry, that should have occurred to me much earlier.

[mib1019]

I'm not that great at VBA, but I'm learning, thanks to good people like yourself.

Can you help me populate the list box? I have set to 3 columns, will hide the 2nd and 3rd once it works.
Column 1 should be the original ddd mm/yy;
Column 2 should be mm/dd/yyyy (which your selection code will work with, hopefully, even though hidden)
Column 3 using weekday function, to calculate start date of week (Monday) of the date

Thanks so much.
MIB1019

[mib1019]

Working on getting the three columns to populate. Here's my code and it almost works.

 'loop through the dates and add them to the list    Dim StartDate, EndDate As Date
    StartDate = Me.txtStart
    EndDate = Me.txtEnd
    Dim str1, str2, str3
    
    Dim i As Integer
    For i = 0 To EndDate - StartDate
        str1 = Format(CDate(StartDate + i), "ddd, m/d/yy")
        str2 = Format(CDate(StartDate + i), "mm/dd/yyyy")
        str3 = Format(CDate(StartDate + i) - Weekday(CDate(StartDate + i), 2) + 1, "mm/dd/yyyy")
        Debug.Print str1
        Debug.Print str2
        Debug.Print str3
        
        Me.lstSelectDates.AddItem str1 & ";" & str2 & ";" & str3

The debug.print is showing the variables correctly.
Tue, 12/29/20
12/29/2020
12/28/2020

Something wrong in the .additem that I can't understand. Maybe I'm just thick Here is my result on the dialog...


I have a feeling that it has to do with the comma in the format to ddd, mm/dd/yy.

[June7]

Make sure listbox properties are set for:
ColumnCount: 3
ColumnWidths: 1;0;0

I think the comma in "ddd, m/d/yy" value is read as column break.

Me.lstSelectDates.AddItem Format(StartDate + i, "ddd m/d/yy") & "," & StartDate + i & "," & StartDate - Weekday(StartDate, vbMonday) + 1

When declaring variables, each variable must be explicitely defined or it will default to Variant.
Dim StartDate, EndDate As Date
should be
Dim StartDate As Date, EndDate As Date

[mib1019]

That was it! It now works correctly. All I want to do now is populate the txtDatesSelected textbox with the selected dates, plus add in the code to insert the selected list into the table.

I'll probably be in touch! Thanks so much for your awesome help!!!
MIB1019

[mib1019]

Hi June7!

Perhaps you can give me a little help with this other dialog. Sometimes the user has to plot his details weekly rather than daily. If that's the case on the calling subform, the code opens a different dialog box that's got some other detail. I've got it working correctly, filling the listbox with Week Start Dates; but the the 'selecting' of those "Air_Weeks" that range of dates is not working. I can see that the Air_Date records exist in the table, but aren't selected.

Here is the code. Inserting some debugging into it, I see that the Dlookup function is returning Nulls on the Air_Weeks of the table. Must be the string value of .itemdata(x) causing this.

 'populate the date list box    'determine week start of txtstartdate
    'Clear ListboxFirst
    Call ClearListBox
    
    Dim ListStart, ListEnd As Date
    ListStart = Me.txtStart
    ListEnd = Me.txtEnd
    Dim FirstWkStart
    Dim LastWkStart
    FirstWkStart = ListStart - Weekday(ListStart, 2) + 1
    LastWkStart = ListEnd - Weekday(ListEnd, 2) + 1
    
    Dim iWeeks, i, varDate
    iWeeks = DateDiff("w", FirstWkStart, LastWkStart)
    Debug.Print FirstWkStart
    Debug.Print LastWkStart
    Debug.Print iWeeks
  
    For i = 0 To iWeeks
        varDate = DateAdd("ww", i, CDate(FirstWkStart))
        Me.lstSelectDates.AddItem Format(varDate, "mm/dd/yy")
    Next
    
    'Show Weeks that exist in tblIODetails_Dates as Selected
    Dim x As Integer
    With Me.lstSelectDates
     For x = 0 To .ListCount - 1
     If Not IsNull(DLookup("Air_Week", "tblIODetails_Dates", "Detail_ID =" & Me.txtDetailID & " AND Air_Week = #" & .ItemData(x) & "#")) Then .Selected(x) = True
    Next
    End With

Again, I really appreciate the help!