Creating a Table with Records for Each Date in a Range

[rjgriffin46]

My apologies if this is a duplicate post.

I am attempting to create a function that will read in records from a recordset that have a Start Date and End Date for when a site will not be in service. I would like to read in that record set and append records to another table for each date within the Start and End Date range. Here is an example:

Non Service Days Table

Site	Non Service Start Date	Non Service End Date
Boys and Girls Hackensack	10/10/18	10/12/18

Output Table

Site	Date Not in Service
Boys and Girls Hackensack	10/10/18
Boys and Girls Hackensack	10/11/18
Boys and Girls Hackensack	10/12/18

I've attempted to create a function for this in the attached database: Creating Mult Recs fm Date Range for Forum.zip using and modifying code from another forum. But I've been unable to resolve the errors with my very limited VBA knowledge. I would appreciate any guidance you may have.

Thanks

[ranman256]

dim vDat
dim sSql as string


docmd.Setwarnings false


vDat = txtStart
while vDat <= txtEnd
   sSql = "Insert into table (Site, DateNotInSvc) values ('" & txtSite & "',#" & vDat & "#)
   docmd.runSql sSql
  
   vDat = DAteAdd("d",1,vDat)
wend
docmd.Setwarnings true

[rjgriffin46]

Thank you so much for this code. I do have a couple of questions:

1. Is it not necessary to “dim” either the input or output table or is what I had correct?
2. Where is reading a record from the input table?
3. How does sSql know which table to insert into?
4. No “as date” is neeed after “dim vDat”?
5. Does “wend” loop back to the “while?

Thanks.

dim vDat
dim sSql as string


docmd.Setwarnings false


vDat = txtStart
while vDat <= txtEnd
   sSql = "Insert into table (Site, DateNotInSvc) values ('" & txtSite & "',#" & vDat & "#)
   docmd.runSql sSql
  
   vDat = DAteAdd("d",1,vDat)
wend
docmd.Setwarnings true

[ssanfu]

You had a lot of errors in your code.

Try this.... I added code and an errors table.

Shouldn't have Look up FIELDS in tables!!

[rjgriffin46]

I don’t have a good understanding of VBA, hence the errors. I appreciate your interest and will take a look at and hopefully learn from your code. Thanks!

[rjgriffin46]

Steve:

I am extremely grateful for all the work you put into this and it has greatly increased my understanding of VBA. To further that understanding, I created the attached Word document (Code Questions.zip). It has your code in the left column of a table. I read through each line, looked things up in Access Help I didn't understand and then put my resulting questions in the right column.

If you (or anyone) has the time, I would appreciate responses to the questions. Don't feel obligated since as I indicated it is already very informative.

Thanks again so much!

[ssanfu]

Answers mostly inline ...... (I think I answered all of the questions)

Private Sub CreateAllNServDateRecs_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    '    Dim tblNonServDays As DAO.Recordset            '<-- This declaration not used - line should be deleted
    '    Dim tblNonServAllDatesTemp As DAO.Recordset    '<-- This declaration not used - line should be deleted
    Dim datDate As Date
    Dim strSQL As String
    Dim sSQL As String       ' Could any names be used, eg., str1SQL, str2SQL? Yes any name. Could use Apple and Banana (but not a good idea)
    Dim ErrorMsg As String
    Dim ErrRecordNum As Long

    Set db = CurrentDb

    'delete old error messages
    CurrentDb.Execute "DELETE * FROM tblInsertErrors", dbFailOnError  '<-- this deletes the previous error messages table of records.
    Me.Requery    '<-- This requeries & refreshes the current (active) form
              'I don’t see a similar statement for tblNonServAllDatesTemp. 'Is it not necessary because its opened via the INSERT INTO… statement later?
             ' Kinda. The table is techniquely not "Opened". It is easier/faster to use the DBE (database engine) to insert the record.


    strSQL = "SELECT * FROM tblNonServDays"
    Set rs = db.OpenRecordset(strSQL)   'Yes, this creates a recordset object for tblNonServDay.

    If Not rs.BOF And Not rs.EOF Then
        rs.MoveLast            'Does rs.MoveLast load all the records in the recordset?  Yes 
        rs.MoveFirst           ' And then rs.MoveFirst goes to the first record?   Yes
        Do While Not rs.EOF
            datDate = rs![Non Serv Start Date]
            If IsDate(rs("[Non Serv End Date]")) Then
                Do While datDate <= rs![Non Serv End Date]
                    strSQL = "INSERT INTO tblNonServAllDatesTemp (Program, [Non Serv Start Date], [Non Serv End Date], datDate)"
                    strSQL = strSQL & "  VALUES (" & rs!Program & ", #" & rs![Non Serv Start Date] & "#, #" & rs![Non Serv End Date] & "#,#" & datDate & "#);" 'Fields in an SQL statement don’t need a dim statement? Correct. You are naming the fields IN THE TABLE that the data is put into
'And modify this statement to:  
'strSQL = strSQL & "  VALUES (" & rs!Program & ", #" & rs![Non Serv Start Date] & "#, #" & rs![Non Serv End Date] & "#, " &  rs![Site Not In Service] & ",#" & datDate & "#);"
'There was a comma missing before the new value and the date delimiters needed to be adjusted

                    '   Debug.Print strSQL  'I assume this would display what’s in strSQL if you were debugging…?  Do you have to un-comment it to do so?                                                    ' Correct and Yes
                    db.Execute strSQL, dbFailOnError   'This tells the database to execute the current strSQL commands? Yes, the DBE (ACE)
                    'Add 1 day
                    datDate = DateAdd("d", 1, datDate)
                Loop
            Else
                ' invalid end date
                ErrRecordNum = rs("Non Serv Chg Number")
                ErrorMsg = "Invalid DATE at [Non Serv Chg Number] --> " & ErrRecordNum
                sSQL = "INSERT INTO tblInsertErrors ( PK_Number, ErrorMsg, ErrorDate )"
                sSQL = sSQL & " VALUES (" & ErrRecordNum & ", '" & ErrorMsg & "',#" & Now() & "#);"
                '                Debug.Print sSQL
                db.Execute sSQL, dbFailOnError
                Me.Requery
            End If
            rs.MoveNext  'This moves to the next record in tblNonServDays? Yes
        Loop
    End If

    rs.Close  'Closes tblNonServDays? No, closes the record set "rs"
    Set rs = Nothing
    Set db = Nothing

    MsgBox "Done!"
End Sub

-------------------------------------------------------------
Note: I intend to query tblNonServDays and in the query set the Non Serv End Date = Non Serv Start Date. The query would then be the input rs to this procedure.
There is already a recordset (rs) created on tblNonServDays. No need to open another record set.

Would I “Dim” that in the same manner, eg:
Dim qryNonServDays As DAO.Recordset? NO!


Are close and Set = Nothing not needed for the output table tblNonServAllDatesTemp? If so, why?
The output table was never opened.

The rules are
If you create it, destroy it.
Example: Set db = CurrentDb
Here you created a reference to the currentdb, but you didn't open it. So you destroy it (Set db = Nothing), but not close it.

If you open it, close it.
Example: Set rs = db.OpenRecordset(strSQL)
Here you created a reference to the recordset (rs) AND you opened it (=OpenRecordSet)
Therefore you first close the recordset (rs.Close), then you destroy it (Set rs = Nothing).


-------------------------------------------------------------------------------------------------------------
[/COLOR][/B]BTW, you should NEVER use spaces in object names. Object names refer to fields, tables, forms, queries and reports.
Really bad name examples:
Non Serv End Date better is NonServEndDate or Non_Serv_End_Date
Non Serv Start Date better is NonServStartDate or Non_Serv_Start_Date
Site Not In Service better is SiteNotInService or Site_Not_In_Service

[rjgriffin46]

Thanks again for your answers. I think I just have one remaining question regarding the following:

"Note: I intend to query tblNonServDays and in the query set the Non Serv End Date = Non Serv Start Date. The query would then be the input rs to this procedure.

There is already a recordset (rs) created on tblNonServDays. No need to open another record set."

Here is my additional question:

Perhaps you thought the query would be an Update query on tblNonServDays? That was not my intent - it would be a Select query. If I create the query, lets say it will be named "qryNonServDays", that has the blank NonServEndDate field filled in and I want this to be used instead of tblNonServDays, can I just substitute qryNonServDays wherever tblNonServDays is used in the code?

Once again, your assistance has be invaluable and most appreciated.

[ssanfu]

I am confused on what you are asking.

When you say

If I create the query, lets say it will be named "qryNonServDays",

that means, to me, that you are creating a saved query. A saved query is a query you can see in the database window (I think it is now named the "Navigation Pane").
A query consists of a SQL statement that can consist of 1 or more tables. An example would be

SELECT tblAssociateInfo.aiAssociateID, [aiLastName] & (", " & [aiFirstName]) AS FullName 
FROM tblAssociateInfo;

or more complex (multiple tables)

SELECT tblAssociateInfo.aiAssociateID, [aiLastName] & (", "+[aiFirstName]) AS Associate, tblWorkDays.wdDate, tblTaskActivities.taTaskNumber, ([taStopTime]-[taStartTime])*1440 AS tClockedTime
FROM (tblAssociateInfo INNER JOIN tblWorkDays ON tblAssociateInfo.[aiAssociateID] = tblWorkDays.[wdAssociateID]) INNER JOIN tblTaskActivities ON tblAssociateInfo.aiAssociateID = tblTaskActivities.taAssociateID;

and can contain an optional WHERE clause to limit records returned.


IN VBA
---------------------
In code, I would call a query a record set. A recordset object can be created using different data sources.
Examples:
Set rst1 = Currentdb.OpenRecordset("SELECT * FROM tblNonServDays") <-- using an SQL string - selects all fields in table

Set rst2 = Currentdb.OpenRecordset(strSQL) <----using a variable where the variable value is "SELECT * FROM tblNonServDays"- selects all fields in table

Set rst3 = Currentdb.OpenRecordset("tblNonServDays") <-- using a table - selects all fields

Set rst4 = Currentdb.OpenRecordset("qrytblNonServDays") <-- using a saved query - selects one or more fields from one or more tables


You can name (declare) recordsets anything you want to name it, but I would stick to a name that begins with "r" or "rs" (just my personal preference - remember that in 6 months or more you (or someone) may have to read the coded and try to understand what you were trying to do )


If you wanted, you could create a record set named "qryNonServDays", but it might get confusing reading the code.



So after all this, I probably missed the question you were asking...... maybe post an example of the question??

[rjgriffin46]

Yes, I was thinking of a saved query. Thanks again Steve.

[rjgriffin46]

So to use my saved query I would use the record set statement that you have as rst4.

I will give this a shot and let you know if I encounter any problems or have any questions!

[rjgriffin46]

Steve:

With your code, explanations and some internet searching I have been able to develop and modify this function further and learn a lot more about VBA coding.

I am very grateful as this has been a breakthrough for me.


And with that I am closing this thread.