Tuesday, February 14, 2017

Auto-populate a text box using a query

I have a small data warehouse I've built in MS Access 2016. I load new data in the warehouse every month, and I want to dynamically show users of the data warehouse the date range available for reports.

I created a label that says the following: "*Data loaded for January 1, 2016 through the end of".

Next, I created a textbox named txtMaxDate. I gave the textbox a transparent border and a font that matches the label above. I placed the textbox right after the label.

In the onLoad event of the form, I wrote the following code:

Private Sub Form_Load()
    Dim sql As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim maxLoadDate As Date
    Dim theYear As String
    Dim theMonth As String
    Set db = CurrentDb
    sql = "SELECT MAX(PayYearMonth) as MaxDate FROM tblPaySummary"
    Set rs = db.OpenRecordset(sql)

    rs.MoveFirst
    maxLoadDate = rs!MaxDate
   
    theYear = year(maxLoadDate)
    theMonth = MonthName(month(maxLoadDate))
    Me.txtMaxDate.Value = theMonth & " " & theYear
End Sub

The code gets the most recent date from the database and displays the date in the text box.