Search code examples
vbams-accesscombobox

Access Combobox returns wrong values after query

I have the problem, that the Combobox returns strange values after I click a button, which performs a query on that form.

The rowsource is assigned in VBA and I have checked that the value of ST_Id always stays the same

Here is the VBA code:

Private Sub Form_Load()
dYear = Year(Now)
dMonth = Month(Now)
        
getDate = DateSerial(dYear, dMonth + 1, 0) 'to get the last day of this month
        
ST_Id = DLookup("[ID]", "ReportingDays", "[ReportingDay] =#" & Format(getDate, "yyyy\/mm\/dd") & "#") 'returns the ID that is saved in ReportingDays-table
        
        
Forms!frm_Team!ReportingMonth.RowSource = "SELECT ReportingDays.ID, ReportingDays.MonthText FROM ReportingDays " _
& " WHERE ReportingDays.ID > " & ST_Id - 3 & " AND ReportingDays.ID < " & ST_Id + 10 & ";" 'fills Combobox with values -2 and + 9 months from the actual month
End Sub

When I open the form the first time everything works fine:

Before Query

But after the form performs a query the combobox values get messed up like this:

After Query

Requerying the Combobox in VBA did not change anything.

I hope you can help me with this issue, because I have not been able to solve this for days.

Kind regards

Nick Thomas

Solution

  • This can be done much simpler:

    Private Sub Form_Load()

    Const RowSource   As String = _
        "Select ID, MonthText From ReportingDays " & _
        "Where DateDiff('m', Date(), ReportingDay) Between -2 And 10"

    Me!ReportingMonth.RowSource = RowSource

End Sub