Hi S,
the following code work, now I tested it and it does exactly what you want.
the two set statement are visualized on two rows but must be on one line of
code so when you paste the code in your button be sure that they are one line.
Also check that on the references microsoft DAO is checked and microsoft
activex data objects is not.
Set rec_ses = CurrentDb.OpenRecordset("select session from entries group by
session order by session", dbOpenDynaset)
Do While Not rec_ses.EOF
'you specify the start time for the session in the input box
strt_tm = InputBox("Specify the start time for " & rec_ses!session)
'this recordset contains all the entries in the current session ordered
by entries
Set rec = CurrentDb.OpenRecordset("select * from entries where
session=""" & rec_ses!session & """ order by [entry #]", dbOpenDynaset)
num_entry = 0
Do While Not rec.EOF
rec.Edit
rec!Time = DateAdd("n", num_entry * 4, strt_tm)
rec.Update
rec.MoveNext
num_entry = num_entry + 1
Loop
rec.Close
'you goto next session
rec_ses.MoveNext
Loop
rec_ses.Close
MsgBox "Update completed"
Cheers Paolo
S said:
This is the where the error lies in the code
'this recordset contains all the entries in the current ceremony ordered
by entries
Set rec = CurrentDb.OpenRecordset("select * from entries where
ceremony=""" & rec_ses!Ceremony & """ order by [Entry #]", dbOpenDynaset)
Paolo said:
Sorry S,
I recycled the code from the answer I gave you yesterday and I forgot to
change a thing and that raise the error. substitute the line of code you
posted with this one
Set rec = CurrentDb.OpenRecordset("select * from entries where session=""" &
rec_ses!Session & """ order by [entry #]", dbOpenDynaset)
Cheers Paolo
:
We're getting there....
I get an error message for the following line of code
Set rec = CurrentDb.OpenRecordset("select * from entries where session=""" &
rec!Session & """ order by [entry #]", dbOpenDynaset)
The beginning part seems to work. a Dialog box opens and asks me to Specify
the start time for session 1. Then this error appears.
Thanks again for all your help.
:
Hi S,
So I think you can do that
' this recordset contains all the session
set rec_ses=currentdb,openrecordset("select session from entries group by
session order by session",dbopendynaset)
do while not rec_ses.eof
'you specify the start time for the session in the input box
strt_tm=inputbox("Specify the start time for " & rec_ses!session)
'this recordset contains all the entries in the current session ordered
by entries
set rec=currentdb,openrecordset("select * from entries where session="""
& rec!session & """ order by [entry #]",dbopendynaset)
num_entry=0
do while not rec.eof
rec.edit
rec!time=DateAdd("n", num_entry*4, strt_tm)
rec.update
rec.movenext
num_entry=num_entry+1
loop
rec.close
'you goto next session
rec_ses.movenext
loop
rec_ses.close
msgbox "Update completed"
Obviously you have to put this code in a button so when you press it the
update process will begin.
This is aircode so you have to test it.
HTH Paolo
:
Hi Paolo,
My Table is called Entries
Some Fields that will be used for calculations in table are
Entry #
Session
Time
So I would like it to find the first entry # in Session 1.
Then prompt me for a Time to start the session.
Then update all records in that particular session.
Then I could repeat it same procedure for
Session 2
Session 3
Session 4
Does that make sense to you?
I'm sorry I'm having such a hard time getting this. I truly appreciate your
help.
:
Well, you said "I am not looking for it to automatically input the time when
adding a new record but instead fill in the time of entries already in the
database when I
set a start time for each catagory or ceremony."
I think the code I wrote do that.
In the form you specify the new start time and the category using this new
start time. I also included the number of minutes between the entries if you
wanna change it. So when you click the button the code update the field
containing the start time for each entry. I assume that you have them in a
table, isn't it? So in my sample I called it yourtable, obviously you must
substitute it with the real name of the table. Also the fields name were an
example and must be replaced with the real name (obviously if the structure
of the table where you store the data is like I imagined)
If you have a field called entry # enclose it between box brackets [entry #]
If I'm not 'nuff clear post the structure of the table you wanna update so
I'll use the real names of the fields and my code will be clearer.
Cheers Paolo
:
Hi Paolo,
I am still having problems. Let me explain a little better.
I have all the information including catagory numbers already filled in. The
only field not filled in is my "Time" field.
Are you saying I need another field other than "Time" one for "Start Time"
as well?
Could I also be having problems because my field is "Entry #" is the #
messing up the code?
:
Hi S,
If I understood well your problem that should solve it:
let's say you have a form where you have 3 text box, one for the category
you wanna update called txt_category, one for the start time called
txt_start_time, and the third called num_min where you specify the number of
minutes between entries.
I call the table you wanna update yourtable where I assume there's a field
called for example category, another called entry and a third called start
containing the start time
Add to the form a button with this code
set rec=currentdb,openrecordset("select * from yourtable where category="""
& txt_category & """ order by entry",dbopendynaset)
num_entry=0
do while not rec.eof
rec.edit
rec!start=DateAdd("n", num_entry*num_min, txt_start_time)
rec.update
rec.movenext
num_entry=num_entry+1
loop
rec.close
msgbox "Update completed"
All this is aircode so you have to test it.
HTH Paolo
:
PLEASE IF ANYONE CAN HELP. I AM UNDER A DEADLINE.
THANK YOU!
:
I should mention - all entries are already entered into the database. I then
group them together by catagory and then manually assign it a catagory
number, an awards ceremony, and then an entry number.
I am not looking for it to automatically input the time when adding a new
record but instead fill in the time of entries already in the database when I
set a start time for each catagory or ceremony.
:
Assume that you have a table with the time field defined as follows:
Field Name Data Type
EntryTime Date/Time - With Format of "Medium Time"
Also the Form has the EntryTime field on it but not updateable by the user.
Then create a "BeforeUpdate" event like so:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim datLastTime As Date
If Form.NewRecord Then
If DCount("EntryTime", "Table1") > 0 Then
datLastTime = DMax("EntryTime", "Table1")
EntryTime = DateAdd("n", 4, datLastTime)
End If
End If
End Sub
This should do it.
==============================
:
I have created a database for running a competition.
I am now trying to automate making a schedule.
I am looking for access to input the time for each record for me to create a
schedule.
Say entry # 001 starts at 8:00AM and each entry is 4 minutes long.
how do i get access to automatically go to entry 002 and fill in the time as
8:04AM
entry 003 as 8:08AM etc etc.
