MakeSchedule function

[Marcelo Henderson via AccessMonster.com]

I'm trying to make a calendar schedule table with 2 fields: SchDate, SchTime.
where is the error?


Option Compare Database

Public Function MakeSchedule(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
dtmDayStart As Date, _
dtmDayEnd As Date, _
intMinuteInterval As Integer, _
ParamArray varDays() As Variant)

' Accepts: Name of schedule table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Time when first 'time-slot' starts each day: DateTime
' Time when last 'time-slot' starts each day: DateTime
' Length of each 'time-slot' in schedule in minutes: Integer
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim dtmTime As Variant
Dim varDay As Variant

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

' does table exist? If so delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(SchDate DATETIME, SchTime DATETIME" & _
"CONSTRAINT PRIMARY KEY (SchDate))"
cmd.CommandText = strSQL
cmd.Execute

' fill table with dates of selected days of week
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Or varDay = 0 Then
cmd.CommandText = strSQL1
strSQL1 = "INSERT INTO " & strTable & "(SchDate) " & _
"VALUES(#" & Format(dtmDate, " mm/dd/yyyy ") & "#)"

For dtmTime = dtmDate + dtmDayStart To dtmDate + _
dtmDayEnd Step intMinuteInterval / 1440
cmd.CommandText = strSql2
strSql2 = "INSERT INTO " & strTable & "(SchTime) " & _
"VALUES(#" & Format(dtmTime, "hh:nn:ss") & "#)"


cmd.CommandText = strSQL1 & strSql2

cmd.Execute
Next dtmTime
End If
Next varDay
Next dtmDate

Set cmd = Nothing

End Function

 

[Daniel Pineault]

Where is what error? Are you receiving an error message? What does it say?
What number are you receiving? Does it goto a specific line in your code,
which one?

If you want any form of help from the people here, you are going to have to
help us help you by providing a little bit more information on your problem!!!
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Marcelo Henderson via AccessMonster.com]

Hi Daniel
Error:

Runtime error “-2147217900 (80040e14)’:
Syntax error on field definition

...

strSQL = "CREATE TABLE " & strTable & _
"(SchDate DATETIME, SchTime DATETIME" & _
"CONSTRAINT PRIMARY KEY (SchDate))"
cmd.CommandText = strSQL

cmd.Execute -----------> Highlight


I watched that: when the cursor is on "dtmDate" appears a value as time (00:
00:00)


For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Or varDay = 0 Then
cmd.CommandText = strSQL1
strSQL1 = "INSERT INTO " & strTable & "(SchDate) " & _
"VALUES(#" & Format(dtmDate, " mm/dd/yyyy ") & "#)"

For dtmTime = dtmDate + dtmDayStart To dtmDate + _
dtmDayEnd Step intMinuteInterval / 1440
cmd.CommandText = strSql2
strSql2 = "INSERT INTO " & strTable & "(SchTime) " & _
"VALUES(#" & Format(dtmTime, "hh:nn:ss") & "#)"


regards

 

[Marcelo Henderson via AccessMonster.com]

New code:

Public Function MakeSchedule(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
dtmDayStart As Date, _
dtmDayEnd As Date, _
intMinuteInterval As Integer, _
ParamArray varDays() As Variant)

' Accepts: Name of schedule table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Time when first 'time-slot' starts each day: DateTime
' Time when last 'time-slot' starts each day: DateTime
' Length of each 'time-slot' in schedule in minutes: Integer
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim dtmTime As Variant
Dim varDay As Variant

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

' does table exist? If so delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(SchDate DATETIME, SchTime datetime)"
cmd.CommandText = strSQL

Debug.Print strSQL

cmd.Execute


' fill table with dates of selected days of week
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()

If Weekday(dtmDate) = varDay Or varDay = 0 Then
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(SchDate) " & _
"VALUES(#" & Format(dtmDate, " mm/dd/yyyy ") & "#)"

For dtmTime = dtmDate + dtmDayStart To dtmDate + _
dtmDayEnd Step intMinuteInterval / 1440
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(SchTime) " & _
"VALUES(#" & Format(dtmTime, "hh:nn:ss") & "#)"


cmd.CommandText = strSQL

cmd.Execute
Next dtmTime
End If
Next varDay
Next dtmDate

Set cmd = Nothing

End Function

Now is running, however only SchTime field is populate. SchDate field is no
data.

WHY???

 

[Marcelo Henderson via AccessMonster.com]

I GUESS THAT ERROR IS HERE:

' fill table with dates of selected days of week

For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()

If Weekday(dtmDate) = varDay Or varDay = 0 Then
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(SchDate) " & _
"VALUES(#" & Format(dtmDate, " mm/dd/yyyy ") & "#)"

For dtmTime = dtmDate + dtmDayStart To dtmDate + _
dtmDayEnd Step intMinuteInterval / 1440
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(SchTime) " & _
"VALUES(#" & Format(dtmTime, "hh:nn:ss") & "#)"

Can you help me?

regards

 

[Douglas J. Steele]

You can't run multiple Insert statements like that: Access doesn't support
it.

Dim strValues As String

For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Or varDay = 0 Then
strValues = "VALUES(" & Format(dtmDate, "\#yyyy\-mm\-dd\#") & ","
For dtmTime = dtmDate + dtmDayStart To dtmDate + _
dtmDayEnd Step intMinuteInterval / 1440
strValues = strValues & Format(dtmTime, "\#hh:nn:ss\#") & ")"
cmd.CommandText = "INSERT INTO [" & strTable & "](SchDate,
SchTime) " & _
& strValues
cmd.Execute
Next dtmTime
End If
Next varDay
Next dtmDate

To be perfectly frank, though, I think you'd be better off combining date
and time into a single field. If you need only one or the other, use the
DateValue or TimeValue function.

For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Or varDay = 0 Then
For dtmTime = dtmDate + dtmDayStart To dtmDate + _
dtmDayEnd Step intMinuteInterval / 1440
cmd.CommandText = "INSERT INTO [" & strTable & "](SchDateTime) " &
_
"Values(" & Format(dtmDate + dtmTime, "\#yyyy\-mm\-dd
hh:nn:ss\#") & ")"
cmd.Execute
Next dtmTime
End If
Next varDay
Next dtmDate

 

[Marcelo Henderson via AccessMonster.com]

Hi Douglas

Great explain, Master.

good we can count on you

Thanks a lot,

apologize for my hard english.

Henderson