Create Multipal Times...

[Josh Armstrong]

I have a form with 4 text boxes; the data that is entered into them
are as follows.
Text1= Start Time
Text2= End Time
Text3= Date
Text4= Interval

I need the form to populate a table with sequential times based on the
start/end times, interval given. Then attach the same date to each
record.
For example...
Start Time = 9:00am
End Time = 12:00am
Date = 09-29-03
Interval = 5min

Result...
9:00am 09-29-03
9:05am 09-29-03
9:10am 09-29-03
9:15am 09-29-03
9:20am 09-29-03
ect...

[MGFoster]

I'd do it in VBA like this - put the following code in the OnClick event
procedure of a CommandButton.

Private Sub cmdSetTimes_Click()

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim dteCurrent As Date
Dim dteStartTime As Date
Dim dteEndTime As Date
Dim intInterval As Integer

' Don't know the name of the table you want this in...
' so used "tblTimes"
Set db = CurrentDb
Set rs = db.openrecordset("tblTimes")

' Make a Date/Time value so we can add minutes to it.
dteStartTime = CDate(Format(Me!txtDate, "m/d/yy") & _
" " & Format(Me!txtStartTime, "00:00"))

dteEndTime = CDate(Format(Me!txtDate, "m/d/yy") & _
" " & Format(Me!txtEndTime, "00:00"))

intInterval = Me!txtInterval
dteCurrent = dteStartTime

With rs
Do While dteCurrent <= dteEndTime
.AddNew
!IntervalDate = dteCurrent
.Update
dteCurrent = DateAdd("n", intInterval, dteStartTime)
intInterval = intInterval + Me!txtInterval
Loop
End With

rs.Close
db.Close

End Sub

I Changed your TextBox name "Date" to "txtDate" to avoid confusion with
Date in VBA, which is the function Date().

You'll probably have to clear out the table tblTimes before running this
routine. If so just put db.Execute "DELETE * FROM tblTimes" before the
"With rs" statement.

See the Access help topics on Do...Loops, DAO AddNew and Update,
Recordsets and anything else you don't understand in the routine.

HTH,

MGFoster
Oakland, CA (USA)
Josh Armstrong wrote:

I have a form with 4 text boxes; the data that is entered into them
are as follows.
Text1= Start Time
Text2= End Time
Text3= Date
Text4= Interval

I need the form to populate a table with sequential times based on the
start/end times, interval given. Then attach the same date to each
record.
For example...
Start Time = 9:00am
End Time = 12:00am
Date = 09-29-03
Interval = 5min

Result...
9:00am 09-29-03
9:05am 09-29-03
9:10am 09-29-03
9:15am 09-29-03
9:20am 09-29-03
ect...

[Pieter Linden]

jo***********@socal.rr.com (Josh Armstrong) wrote in message news:<b6**************************@posting.google. com>...

I have a form with 4 text boxes; the data that is entered into them
are as follows.
Text1= Start Time
Text2= End Time
Text3= Date
Text4= Interval

I need the form to populate a table with sequential times based on the
start/end times, interval given. Then attach the same date to each
record.
For example...
Start Time = 9:00am
End Time = 12:00am
Date = 09-29-03
Interval = 5min
Child's play
CREATE TABLE TimesList(
StartTime DATE PRIMARY KEY,
EndTime DATE NOT NULL)

Create an UNBOUND form (no recordsource) with textboxes for this stuff: Start Time = 9:00am
End Time = 12:00am
Date = 09-29-03
Interval = 5

Interval Size="n";"Minutes";"h";"Hours";"ddd","Days" (it's a combobox...)

dim db as dao.database
dim rs as dao.recordset

set db=currentdb
set rs = db.openrecordset("TimesList",dbOpenTable)
for dtThisTime = dtStart To dtFinish
rs.AddNew
rs.Fields("StartTime")=dtThisTime
rs.Fields("EndTime")=DateAdd(Me.cboInterval,dtThis Time,me.txtIntervalSize)
dtThisTime=DateAdd(Me.cboInterval,dtThisTime,me.tx tIntervalSize)
rs.Update
next dtThisTime

rs.close
set rs=nothing
set db=nothing

End sub