I am after some onClick code that will create a record in a table (tblResults)for each record with a given date rangein another table (tblName).

For example, if tblName has the following 3 fields and 2 records;

NAME John, Mary
START_DATE 02-Jun-08, 07-Jul-09
END_DATE 04-Jun-08, 09-Jul-08

Would create the following records in tblResults

NAME John, John, John, John, Mary, Mary, Mary
DATE 02-Jun-08, 03-Jun-08, 04-Jun-08, 07-Jul-09, 08-Jul-09, 09-Jul-09

See attached for visual explanation of the above.

Thanks.

Hi Js..hope this is what you were after

Sub FillDates()
Dim intCount As Integer
Dim strSQL As String
Dim myRS As Recordset
Dim strNewName, strOldName As String
Dim dteDate As Date
Dim dteStart, dteEnd As Date
strSQL = "SELECT * FROM tblName"
Set myRS = CurrentDb.OpenRecordset(strSQL)
If myRS.RecordCount > 0 Then
myRS.MoveLast
intCount = myRS.RecordCount
myRS.MoveFirst

For n = 1 To intCount
strOldName = myRS("Name")
dteDate = myRS("Start_Date")
dteEnd = myRS("End_Date")

Do While dteDate <= dteEnd
strSQL = "INSERT INTO tblResults (Name, [date]) "
strSQL = strSQL & "SELECT '" & myRS("Name") & "' AS Name, #" & Format(dteDate, "mm/dd/yyyy") & "# AS 'Date';"
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
dteDate = dteDate + 1
Loop
myRS.MoveNext
Next n

End If
myRS.Close
Set myRS = Nothing
End Sub

Thanks ..... works a treat ..... but I place as much emphasis on learning this stuff as finding the solution.

As such, I've added a few comments to the code to try and explain how I think it works and I would be really grateful if you would read them and help increase my knowldege,

Can you confirm if I understand correctly and;

Simply explain what the sections of code maked ' ????????? actually do ?
Answer the questions I have posed.


Finally, as a general question, you use a recordset (myRS) in your code. But I thought to use a recordset you had to crerate an ADODB.Connection ......

Many thanks.

Option Compare Database

Private Sub Command0_Click()

'Number of records in the tblName
Dim intCount As Integer

'Putting SQl code into a string
Dim strSQL As String

'Defining a Recordset
Dim myRS As Recordset

'Why define strNewName but you do not use it in the code ?
'What does strOldName do ?
Dim strNewName, strOldName As String

Dim dteDate As Date

'Defining Start and End Dates
'Why define dteStart but you do not use it in the code ?
Dim dteStart, dteEnd As Date

'Select 'ALL' from tblNames
strSQL = "SELECT * FROM tblName"

'Declare that the Recordset is the string strSQL
Set myRS = CurrentDb.OpenRecordset(strSQL)

'Count the numebr of records in the recordset.
'ie. the number of records in strSQL
'ie. the number of records in the tblName
If myRS.RecordCount > 0 Then
myRS.MoveLast
intCount = myRS.RecordCount
myRS.MoveFirst

' ?????????
For n = 1 To intCount
strOldName = myRS("Name")
dteDate = myRS("Start Date")
dteEnd = myRS("End Date")
' ?????????
Do While dteDate <= dteEnd
strSQL = "INSERT INTO tblResults (Name, [date]) "
strSQL = strSQL & "SELECT '" & myRS("Name") & "' AS Name, #" & Format(dteDate, "mm/dd/yyyy") & "# AS 'Date';"
Debug.Print strSQL
'Remove warnings , run code, re set warnings
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
' ?????????
dteDate = dteDate + 1
Loop
'Move to the next record sin the recordset
'ie. the next record in strSQL
'ie. the next record in the tblName
myRS.MoveNext
Next n

End If
'Close the recordset
myRS.Close
Set myRS = Nothing

End Sub

Thanks ..... works a treat ..... but I place as much emphasis on learning this stuff as finding the solution.

As such, I've added a few comments to the code to try and explain how I think it works and I would be really grateful if you would read them and help increase my knowldege,


Can you confirm if I understand correctly and;

Simply explain what the sections of code maked ' ????????? actually do ?
Answer the questions I have posed.

Finally, as a general question, you use a recordset (myRS) in your code. But I thought to use a recordset you had to crerate an ADODB.Connection ......

Many thanks.

Option Compare Database

Private Sub Command0_Click()

'Number of records in the tblName
Dim intCount As Integer

'Putting SQl code into a string
Dim strSQL As String

'Defining a Recordset
Dim myRS As Recordset

'Why define strNewName but you do not use it in the code ?
'What does strOldName do ?
Dim strNewName, strOldName As String

Dim dteDate As Date

'Defining Start and End Dates
'Why define dteStart but you do not use it in the code ?
Dim dteStart, dteEnd As Date

'Select 'ALL' from tblNames
strSQL = "SELECT * FROM tblName"

'Declare that the Recordset is the string strSQL
Set myRS = CurrentDb.OpenRecordset(strSQL)

'Count the numebr of records in the recordset.
'ie. the number of records in strSQL
'ie. the number of records in the tblName
If myRS.RecordCount > 0 Then
myRS.MoveLast
intCount = myRS.RecordCount
myRS.MoveFirst

' ?????????
For n = 1 To intCount
strOldName = myRS("Name")
dteDate = myRS("Start Date")
dteEnd = myRS("End Date")
' ?????????
Do While dteDate <= dteEnd
strSQL = "INSERT INTO tblResults (Name, [date]) "
strSQL = strSQL & "SELECT '" & myRS("Name") & "' AS Name, #" & Format(dteDate, "mm/dd/yyyy") & "# AS 'Date';"
Debug.Print strSQL
'Remove warnings , run code, re set warnings
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
' ?????????
dteDate = dteDate + 1
Loop
'Move to the next record sin the recordset
'ie. the next record in strSQL
'ie. the next record in the tblName
myRS.MoveNext
Next n

End If
'Close the recordset
myRS.Close
Set myRS = Nothing

End Sub


You understood a lot of the comments correctly, i admit i hadn't cleaned it up afterwards as best as i should have and apologise for the variables that don't get used..

The For n = 1 to intCount is about looping through the recordset stored in myRS. Because you know the last recordnumber your routine should stop before an error is thrown like EndOfFile (EOF).

The While loop is similar to the For, except that it checks that the value defined is still valid for the loop to continue, the moment it isn't valid the code breaks out and moves onto the next line.

?????????
dteDate = dteDate + 1 - this simply increments the variable holding your date. Increasing by 1 day with each pass, which is what you wanted.

Let me pose a question...Why would you create a connection to a database that you code actually reside in? The CurrentDb makes that? reference to a connection, bit like referencing "Me." when referring to a control or property of a form. If you linked Excel to Access you would create a connection then fill a recordset but not if you're in Access already.

My last piece of advise would be to look up naming conventions. I used str for String variables, int for Integers, dte for Date...you should use similar when it comes to naming table Fields, stay well away from RESERVED words, like name, date, year, day... even when naming the table. I've seen tbl for Table, qry for Query, frm for Form...

Hope this helps.

Just a quick comment on defining variables in VBA

Dim strNewName, strOldName As String

does not define both variables as String

strNewname will be defined as a variant by the the above definition.

to make both Strings

use Dim strNewName as String, strOldName As String


Sorry to be picky but this is a common misconception.

Just a quick comment on defining variables in VBA

Dim strNewName, strOldName As String

does not define both variables as String

strNewname will be defined as a variant by the the above definition.

to make both Strings

use Dim strNewName as String, strOldName As String


Sorry to be picky but this is a common misconception.

many thanks Rabbi, i was always taught it would. I will endeavour to declare all variables seperately. :)

many thanks Rabbi, i was always taught it would. I will endeavour to declare all variables seperately. :)It does work in a lot of languages but VBA has its own little funnies.

It does work in a lot of languages but VBA has its own little funnies.

a then that's where i've gotten lost along the way...thanks for putting me back on the wagon as it were..straight and narrow...you know what i meant

Thanks alot ..... no need to apologise for not using all variables ..... you are doing me a fovour.

The only piece of code that I don't now fully understand;

Do While dteDate <= dteEnd
strSQL = "INSERT INTO tblResults (Name, [date]) "
strSQL = strSQL & "SELECT '" & myRS("Name") & "' AS Name, #" & Format(dteDate, "mm/dd/yyyy") & "# AS 'Date';"
Debug.Print strSQL

Woudl you be kind enough to explain these lines too ?

Thanks alot.

Thanks alot ..... no need to apologise for not using all variables ..... you are doing me a fovour.

The only piece of code that I don't now fully understand;

Do While dteDate <= dteEnd
strSQL = "INSERT INTO tblResults (Name, [date]) "
strSQL = strSQL & "SELECT '" & myRS("Name") & "' AS Name, #" & Format(dteDate, "mm/dd/yyyy") & "# AS 'Date';"
Debug.Print strSQL

Woudl you be kind enough to explain these lines too ?

Thanks alot.

this simply APPENDS the data to the table tblResults. If you open a query in design view, then select the SQL view, you'll really see what goes on.

The formatting i apply to the date was because i was getting strange results when appending the data.