MS Access Combobox/VBA

Question

Afternoon all;

I'm looking for some advice on a MS Access 2016 app that I've been working on. I have a form with a combo box that is sourced from a table containing a week beginning date (Monnday) and a week ending date (Sunday).

My hope was that when the form was opened, the combo box would jump to the current week's Monday. For example, today is the third, so the combo box would have 2/1/21 - 2/7/211 pre-selected.

Is there a way to do this or is this a pipe dream?

Any suggestions would be most appreciated.

Welcome to StackOverflow. Please read this help doc about how to ask a question that will get good answers. https://stackoverflow.com/help/how-to-ask — HackSlash, Feb 03 '21 at 22:17
For a decent answer, you will have to tell what your table looks like (fields, data types, etc.) and details about the combobox (columns, rowsource, bound column, etc.). — Gustav, Feb 05 '21 at 10:02

score 0 · Answer 1 · answered Feb 06 '21 at 04:11

I think it will be something like this.

Private Sub Command_Click()
Dim sDate As String
Dim i As Integer

    sDate = "01/01/" & Year(Date)
    
    If Day(CDate(sDate)) <> vbMonday Then
        sDate = DateAdd("d", vbMonday - Day(CDate(sDate)) - 1, CDate(sDate))
    End If
    
    For i = 0 To 51
        Combo1.AddItem Format(DateAdd("ww", i, sDate), "mmm dd, yyyy")
        If Date > DateAdd("ww", i, sDate) And Date < DateAdd("ww", i + 1, sDate) Then
            Combo1.ListIndex = i
        End If
    Next i

End Sub

Also, see the relevant discussion from the link below.

MS Access 2010 (Design View): return Monday of the current week with Monday as 1st day of the week

MS Access Combobox/VBA

1 Answers1