set value of first record only

[efandango]

How can I set the value for the first record of a recordset?

I want to set the value of the first recorde to 1. all records thereafter
will use the Now() time field for their values.

This is for a form that logs the time (in seconds) each time a new record is
created; but the problem is that if the user waits too long before creating
the first record, I get, for example a value of 25 seconds, which is too
long, so i would prefer if it was just given the value of 1 (second).

I don't mind if the first record has the real value at first, and is then
overwritten with the value of '1'.

is this possible?

 

[Marshall Barton]

How can I set the value for the first record of a recordset?

I want to set the value of the first recorde to 1. all records thereafter
will use the Now() time field for their values.

This is for a form that logs the time (in seconds) each time a new record is
created; but the problem is that if the user waits too long before creating
the first record, I get, for example a value of 25 seconds, which is too
long, so i would prefer if it was just given the value of 1 (second).

I don't mind if the first record has the real value at first, and is then
overwritten with the value of '1'.

Use your form's BeforeUpdate event. If I have cirrectly
decoded what you're trying to do, try something like:

Static dtPreviousTime As Date
If Me.RecordsetClone.RecordCount = 0 Then
Me.timefield =TimeSerial(0,0,1)
Else
Me.timefield = DateDiff("s", dtPreviousTime, Time)
End If
dtPreviousTime = Time

 

[efandango]

Marshall,

I get this error message:

'Field is based on an expression and cannot be edited

this is the field's expression:

Elapsed:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss")

is there another way of doing this?

 

[Marshall Barton]

Apparently I did not correctly decode what you're trying to
do. I suggest that you explain your question in terms of
the relevant variables, controls, tables and fields you have
along with how they relate to each other.
--
Marsh
MVP [MS Access]

I get this error message:

'Field is based on an expression and cannot be edited

this is the field's expression:

Elapsed:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings",
"Run_waypoint_List_ID = " & [Run_waypoint_List_ID]),"ss")

is there another way of doing this?

Use your form's BeforeUpdate event. If I have correctly
decoded what you're trying to do, try something like:

Static dtPreviousTime As Date
If Me.RecordsetClone.RecordCount = 0 Then
Me.timefield =TimeSerial(0,0,1)
Else
Me.timefield = DateDiff("s", dtPreviousTime, Time)
End If
dtPreviousTime = Time

 

[efandango]

OK, Marshall

Here goes...

the scenario:

I have 2 forms.

the 2nd form is used for revealing a sequence of records which happen to be
addresses. (this is for a memorizing test).

The 1st form is used to execute the 'reveal' action on the first form,
whereby the reveal button goes through each record in the form's record
source and copies the record to the other form's corresponding 'empty'
fields, thereby creating a gradual 'reveal' of the address list on the second
form.

the 1st form's reveal button uses this code to copy the record:

If IsNull(Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_waypoint])
Then
Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
DoCmd.GoToRecord , , acNext

this all works fine.


the 2nd form's record source is this:

SELECT tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_Direction,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed,
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss") AS Elapsed FROM
tbl_Waypoints_Reveal_Target ORDER BY
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;

Which all works fine.


But then I wanted to add a feature that would time how long it took the user
to ask for the next reveal

so I used another query called:

QRY_Run_Reveal_Timings

which is this:

SELECT tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed, (SELECT Dupe.Timer_pressed -
tbl_Waypoints_Reveal_Target.Timer_pressed
FROM tbl_Waypoints_Reveal_Target AS Dupe
WHERE Dupe.Run_waypoint_List_ID =
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID-1) AS Timer_Elapsed
FROM tbl_Waypoints_Reveal_Target
ORDER BY tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;


Which utilises this line and its particular DLookup reference for my 2nd
form's SQL to enable me to get the timings for the form field,[Elapsed]

Elapsed:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss")


that is the framework, and all works fine, except because of the way that
the timings work; eg: compare the current record's now() time to the previous
record's now() time, gives me the first record as being a very high and
undesirable figure, because the user may spend an inordinate amount of time
geeting their head together before beginning the reveal process, so I would
rather it was set to either 1 sec, as it is only the 2nd and subsequent
records (& timings) that will matter.

I hope this makes some sense and that you can help with a solution.

regards

Eric

Apparently I did not correctly decode what you're trying to
do. I suggest that you explain your question in terms of
the relevant variables, controls, tables and fields you have
along with how they relate to each other.
--
Marsh
MVP [MS Access]

I get this error message:

'Field is based on an expression and cannot be edited

this is the field's expression:

Elapsed:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings",
"Run_waypoint_List_ID = " & [Run_waypoint_List_ID]),"ss")

is there another way of doing this?

efandango wrote:

How can I set the value for the first record of a recordset?

I want to set the value of the first recorde to 1. all records thereafter
will use the Now() time field for their values.

This is for a form that logs the time (in seconds) each time a new record is
created; but the problem is that if the user waits too long before creating
the first record, I get, for example a value of 25 seconds, which is too
long, so i would prefer if it was just given the value of 1 (second).

I don't mind if the first record has the real value at first, and is then
overwritten with the value of '1'.


Use your form's BeforeUpdate event. If I have correctly
decoded what you're trying to do, try something like:

Static dtPreviousTime As Date
If Me.RecordsetClone.RecordCount = 0 Then
Me.timefield =TimeSerial(0,0,1)
Else
Me.timefield = DateDiff("s", dtPreviousTime, Time)
End If
dtPreviousTime = Time

 

[efandango]

Marshall,

maybe another way of solving this is to deal with the source query. I have
asked the same/similar question relating to m problem in the Queries forum,
can you take a look and see if the answer is simpler with this approach?

here's a link.

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

OK, Marshall

Here goes...

the scenario:

I have 2 forms.

the 2nd form is used for revealing a sequence of records which happen to be
addresses. (this is for a memorizing test).

The 1st form is used to execute the 'reveal' action on the first form,
whereby the reveal button goes through each record in the form's record
source and copies the record to the other form's corresponding 'empty'
fields, thereby creating a gradual 'reveal' of the address list on the second
form.

the 1st form's reveal button uses this code to copy the record:

If IsNull(Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_waypoint])
Then
Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
DoCmd.GoToRecord , , acNext

this all works fine.


the 2nd form's record source is this:

SELECT tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_Direction,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed,
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss") AS Elapsed FROM
tbl_Waypoints_Reveal_Target ORDER BY
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;

Which all works fine.


But then I wanted to add a feature that would time how long it took the user
to ask for the next reveal

so I used another query called:

QRY_Run_Reveal_Timings

which is this:

SELECT tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed, (SELECT Dupe.Timer_pressed -
tbl_Waypoints_Reveal_Target.Timer_pressed
FROM tbl_Waypoints_Reveal_Target AS Dupe
WHERE Dupe.Run_waypoint_List_ID =
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID-1) AS Timer_Elapsed
FROM tbl_Waypoints_Reveal_Target
ORDER BY tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;


Which utilises this line and its particular DLookup reference for my 2nd
form's SQL to enable me to get the timings for the form field,[Elapsed]

Elapsed:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss")


that is the framework, and all works fine, except because of the way that
the timings work; eg: compare the current record's now() time to the previous
record's now() time, gives me the first record as being a very high and
undesirable figure, because the user may spend an inordinate amount of time
geeting their head together before beginning the reveal process, so I would
rather it was set to either 1 sec, as it is only the 2nd and subsequent
records (& timings) that will matter.

I hope this makes some sense and that you can help with a solution.

regards

Eric

Apparently I did not correctly decode what you're trying to
do. I suggest that you explain your question in terms of
the relevant variables, controls, tables and fields you have
along with how they relate to each other.
--
Marsh
MVP [MS Access]

I get this error message:

'Field is based on an expression and cannot be edited

this is the field's expression:

Elapsed:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings",
"Run_waypoint_List_ID = " & [Run_waypoint_List_ID]),"ss")

is there another way of doing this?


:

efandango wrote:

How can I set the value for the first record of a recordset?

I want to set the value of the first recorde to 1. all records thereafter
will use the Now() time field for their values.

This is for a form that logs the time (in seconds) each time a new record is
created; but the problem is that if the user waits too long before creating
the first record, I get, for example a value of 25 seconds, which is too
long, so i would prefer if it was just given the value of 1 (second).

I don't mind if the first record has the real value at first, and is then
overwritten with the value of '1'.


Use your form's BeforeUpdate event. If I have correctly
decoded what you're trying to do, try something like:

Static dtPreviousTime As Date
If Me.RecordsetClone.RecordCount = 0 Then
Me.timefield =TimeSerial(0,0,1)
Else
Me.timefield = DateDiff("s", dtPreviousTime, Time)
End If
dtPreviousTime = Time

 

[Marshall Barton]

I am having a tough time wrapping my head around what you
are trying to do. Every time I think I get an idea about
it, I can not see how my thoughts can relate to the
code/queries you posted. I don't even see how the query in
that other thread has anything at all to do with what we
have been discussing. I suspect that's because your design
is foreign to how I would think about approaching whatever
objective you are trying to achieve.

I suspect that our brains are just operating on completely
different wave lenghts and you will be better off starting a
new thread with an explanation of the goal and a specific
question instead of jumping to a detailed how to code
question that may be based on a less than optimal design.
--
Marsh
MVP [MS Access]

maybe another way of solving this is to deal with the source query. I have
asked the same/similar question relating to m problem in the Queries forum,
can you take a look and see if the answer is simpler with this approach?

here's a link.

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

I have 2 forms.

the 2nd form is used for revealing a sequence of records which happen to be
addresses. (this is for a memorizing test).

The 1st form is used to execute the 'reveal' action on the first form,
whereby the reveal button goes through each record in the form's record
source and copies the record to the other form's corresponding 'empty'
fields, thereby creating a gradual 'reveal' of the address list on the second
form.

the 1st form's reveal button uses this code to copy the record:

If IsNull(Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_waypoint])
Then
Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
DoCmd.GoToRecord , , acNext

this all works fine.

the 2nd form's record source is this:

SELECT tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_Direction,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed,
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss") AS Elapsed FROM
tbl_Waypoints_Reveal_Target ORDER BY
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;

Which all works fine.

But then I wanted to add a feature that would time how long it took the user
to ask for the next reveal so I used another query called:

QRY_Run_Reveal_Timings

SELECT tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed, (SELECT Dupe.Timer_pressed -
tbl_Waypoints_Reveal_Target.Timer_pressed
FROM tbl_Waypoints_Reveal_Target AS Dupe
WHERE Dupe.Run_waypoint_List_ID =
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID-1) AS Timer_Elapsed
FROM tbl_Waypoints_Reveal_Target
ORDER BY tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;


Which utilises this line and its particular DLookup reference for my 2nd
form's SQL to enable me to get the timings for the form field,[Elapsed]

Elapsed:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss")


that is the framework, and all works fine, except because of the way that
the timings work; eg: compare the current record's now() time to the previous
record's now() time, gives me the first record as being a very high and
undesirable figure, because the user may spend an inordinate amount of time
geeting their head together before beginning the reveal process, so I would
rather it was set to either 1 sec, as it is only the 2nd and subsequent
records (& timings) that will matter.

I hope this makes some sense and that you can help with a solution.

 

[efandango]

Marshall,

I have tried to post a new message, but am getting Void messages in the
bottom of my iexplorer window which is not enabling me to create a new post.
But I can reply to exisitng messages.

Here is what I have prepared as new question, perhaps this will make a lot
more sense as it is a description from the ground up.

Create a 'Reveal Answers'by Pressing a button on continous form



Primary Objective:

I have a form which is linked to a series of records which are Street names.
The purpose of the form is to allow the user to guess/memorise the order that
the streets appear. When the user first goes to the form tab, The form's
field boxes are blank. The user has to then decide which Address will appear
and then hit the button to discover of he was correct. So for this purpose, I
want the user to be able to hit a reveal button where each new street
(record) is revealed in ascending order on a continous form.

Secondary Objective:

Each time the user presses the Reveal button, I want to be able to measure
the difference in seconds between each button push (record request).




Some constraints:

Each Recordset is a subset based on a Master Form/PK called Run_No.
Each Recordset will differ in number of Addresses (hence a continous form)



The data of the table for the Reveal Form is: tbl_Waypoints_Reveal_Target


Run_waypoint_List_ID AutoNumber,rPK
Run_No Number,linked to Master PK
Run_waypoint Text, Address field
Run_Direction Text, Address field
Timer_pressed Timefield, seconds
Elapsed Time Timefield, seconds
OrderSeq Number


This is how the data looks on the form.

Run_No Run_waypoint_List_ID Run_waypoint Run_Direction Timer_pressed Elapsed
1 365 Main Street L on L 10:39:45 03
1 366 South Road R 10:39:46 01
1 367 East Road L 10:39:46 04

I am having a tough time wrapping my head around what you
are trying to do. Every time I think I get an idea about
it, I can not see how my thoughts can relate to the
code/queries you posted. I don't even see how the query in
that other thread has anything at all to do with what we
have been discussing. I suspect that's because your design
is foreign to how I would think about approaching whatever
objective you are trying to achieve.

I suspect that our brains are just operating on completely
different wave lenghts and you will be better off starting a
new thread with an explanation of the goal and a specific
question instead of jumping to a detailed how to code
question that may be based on a less than optimal design.
--
Marsh
MVP [MS Access]

maybe another way of solving this is to deal with the source query. I have
asked the same/similar question relating to m problem in the Queries forum,
can you take a look and see if the answer is simpler with this approach?

here's a link.

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

I have 2 forms.

the 2nd form is used for revealing a sequence of records which happen to be
addresses. (this is for a memorizing test).

The 1st form is used to execute the 'reveal' action on the first form,
whereby the reveal button goes through each record in the form's record
source and copies the record to the other form's corresponding 'empty'
fields, thereby creating a gradual 'reveal' of the address list on the second
form.

the 1st form's reveal button uses this code to copy the record:

If IsNull(Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_waypoint])
Then
Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Waypoints_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
DoCmd.GoToRecord , , acNext

this all works fine.

the 2nd form's record source is this:

SELECT tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_Direction,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed,
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss") AS Elapsed FROM
tbl_Waypoints_Reveal_Target ORDER BY
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;

Which all works fine.

But then I wanted to add a feature that would time how long it took the user
to ask for the next reveal so I used another query called:

QRY_Run_Reveal_Timings

SELECT tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed, (SELECT Dupe.Timer_pressed -
tbl_Waypoints_Reveal_Target.Timer_pressed
FROM tbl_Waypoints_Reveal_Target AS Dupe
WHERE Dupe.Run_waypoint_List_ID =
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID-1) AS Timer_Elapsed
FROM tbl_Waypoints_Reveal_Target
ORDER BY tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;


Which utilises this line and its particular DLookup reference for my 2nd
form's SQL to enable me to get the timings for the form field,[Elapsed]

Elapsed:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]),"ss")


that is the framework, and all works fine, except because of the way that
the timings work; eg: compare the current record's now() time to the previous
record's now() time, gives me the first record as being a very high and
undesirable figure, because the user may spend an inordinate amount of time
geeting their head together before beginning the reveal process, so I would
rather it was set to either 1 sec, as it is only the 2nd and subsequent
records (& timings) that will matter.

I hope this makes some sense and that you can help with a solution.

 

[Marshall Barton]

Here is what I have prepared as new question, perhaps this will make a lot
more sense as it is a description from the ground up.

Create a 'Reveal Answers'by Pressing a button on continous form

Primary Objective:

I have a form which is linked to a series of records which are Street names.
The purpose of the form is to allow the user to guess/memorise the order that
the streets appear. When the user first goes to the form tab, The form's
field boxes are blank. The user has to then decide which Address will appear
and then hit the button to discover of he was correct. So for this purpose, I
want the user to be able to hit a reveal button where each new street
(record) is revealed in ascending order on a continous form.

Secondary Objective:

Each time the user presses the Reveal button, I want to be able to measure
the difference in seconds between each button push (record request).


Some constraints:

Each Recordset is a subset based on a Master Form/PK called Run_No.
Each Recordset will differ in number of Addresses (hence a continous form)

The data of the table for the Reveal Form is: tbl_Waypoints_Reveal_Target

Run_waypoint_List_ID AutoNumber,rPK
Run_No Number,linked to Master PK
Run_waypoint Text, Address field
Run_Direction Text, Address field
Timer_pressed Timefield, seconds
Elapsed Time Timefield, seconds
OrderSeq Number


This is how the data looks on the form.

Run_No Run_waypoint_List_ID Run_waypoint Run_Direction Timer_pressed Elapsed
1 365 Main Street L on L 10:39:45 03
1 366 South Road R 10:39:46 01
1 367 East Road L 10:39:46 04

Let's stick to one objective at a time, Since we've
discussed the elapsed time issue and I don't have anything
to add at this point, I'll try a suggestion about the reveal
form.

I presume the OrderSeq field is a sequential number starting
at 1 that can be used to sort the records in the correct
order. If that's the case then you can use a criteria in
the form's record source query to only display the first N
records. If you use a hidden text box (named txtCorrect) on
the main form that contains the number of correct guessesm
then the criteria for OrderSeq field would be:
<=Forms![Master Form].txtCorrect

I do not understand why you have two tables. It seems like
they both contain the same data and the master and reveal
forms just display them in a different order, probably with
fewer fields on the master form.

I am going to be out of touch for the next week and a half,
so I won't be able to get back to this thread. Good luck in
making progress while I'm away.