By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Create Multipal Times...

P: n/a
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...
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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...


Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.