Enter a Date in a table when access opens

[gmazza76]

Good morning

I have a landing page in access and am trying to update the "Sub Form_Load" so that when it opens it enters 2 dates in an existing table called Load Dates.
I want to use the dates in this table to run a few other queries so people dont have to keep changing the queries possibly causing errors.

I have written the code below which runs correctly as far as I can tell (when hovering over the dates etc) and it identifies the correct dates, but doesnt align them to the "LoadDates" table.

I have used this format before and it usually will add the data as needed. The "LoadDAtes" table has the fields set up as Date/Time format.

Dim dcurrent_day As Date
Dim rst As Recordset
Dim strsql As String

    If dcurrent_day = DateAdd("d", 1 - Weekday(Date, vbMonday), Date) Then
        DoCmd.RunSQL "Delete LoadDates.* from LoadDates;"
            Set rst = CurrentDb.OpenRecordset("LoadDates")
                rst.AddNew
                    rst.Fields("From Date") = Date - 3
                    rst.Fields("To Date") = Date
    Else
        DoCmd.RunSQL "Delete LoadDates.* from LoadDates;"
            Set rst = CurrentDb.OpenRecordset("LoadDates")
                rst.AddNew
                    rst.Fields("From Date") = Date - 1
                    rst.Fields("To Date") = Date
    End If

thanks in advance
Gavin

 

[welshgasman]

Well I would be closing the recordset and setting it to nothing, but not sure that would affect your issue, just tidiness on my part.
Edit: spotted the error, I tried to highlight it as bold, so do not use the html characters in the code

BTW, any time you repeat code, generally there is a better way?
You are deleting from lodadates regardless, so just do it once?

Dim dcurrent_day As Date
Dim rst As Recordset
Dim strsql As String
Dim intDay as Integer
    DoCmd.RunSQL "Delete LoadDates.* from LoadDates;"

    If dcurrent_day = DateAdd("d", 1 - Weekday(Date, vbMonday), Date) Then
        intDay = -3
    Else
        intDay = -1
    End If
     Set rst = CurrentDb.OpenRecordset("LoadDates")
     rst.AddNew
     rst.Fields("From Date") = Date - intDay
     rst.Fields("To Date") = Date
     rst.Update
     rst.close
     Set rst = Nothing

When something does not work as you think it should, look up the syntax.

Recordset.AddNew method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[gmazza76]

thanks @welshgasman, I cant believe I missed the rst.update part downwards as that was a schoolboy error.
Thanks again

 

[welshgasman]

Well TBH I missed it as well, as I do not use Access much these days, and only a little when I was working.
However I tried the code on one of my DBs and whilst the first field was filled, the second was not, so I went googling.