Put figures in using information in previous record

[s4]

Hi
I have several records with "Opening" and "Closing". The Opening are always
the closing from the previous record. Then those closing are the opening of
the next record etc.
Is there a way to automatically copy Closing from the first record into the
Opening of the next record and so on?
Thanks

 

[Bob Quintal]

Hi
I have several records with "Opening" and "Closing". The
Opening are always the closing from the previous record. Then
those closing are the opening of the next record etc.
Is there a way to automatically copy Closing from the first
record into the Opening of the next record and so on?
Thanks

Databsae design courses teach the rule that if the data is
always the same as another item of data, you query it, you do
not store it in the table. It is a good rule, but like all
rules, made to be broken.

In the form where you enter the data, you need to set the
default value of opening to the value you entered for closing.
You would do this from the AfterUpdate event of the control
bound to your closing field in the table.

me.txtOpening.default = me.txtclosing.value

But that value will not persist if you close then reopen the
form, so you need to also set the default value for the opening
to the most recently entered closing in the Form_Open event. I
cannot give you a specific answer as to how to write the Where
Clause for the query as I cannot guess the structure of the
table, but you probably could use the Dlookup() function to do
this easily.
If for example, the date and time of the entry is stored, you
want the closing value associated to the maximum date in the
table, or the bigest Sequence Number...

something like
stWhereclause = "[Entrydate] = # & Dmax
("entrydate","thetableName") & "#"

me.txtOpening.default = Dlookup
("closing","thetablename",stwhereclause)

 

[Ken Sheridan]

Bob:

It’s the DefaultValue property, not the Default property that's needed here
in fact. The latter is a property of a command button and is a Boolean
expression indicating whether the button is the default button on a form.

Its also worth noting that the DefaultValue property is always a string
expression, regardless of the data type in question, so should be wrapped in
literal quotes characters like so:

Me.txtOpening.DefaultValue = """" & Me.txtClosing & """"

A lot of the time omitting the literal quotes characters won't matter, but
sometimes they are crucial. One instance is where the value is a date in an
internationally ambiguous format such as a short date. If the normal # date
delimiter is used instead of quotes this would give the wrong date on a
system not using the mm/dd/yyyy format, e.g. the UK format of dd/mm/yyyy. If
no delimiter is used then the value is treated as an arithmetical expression,
so the date passed into the control would be whatever date/time value the
result of that expression represents in the underlying date/time
implementation, e.g. 08/11/2007 would result in 30 December 1899 00:00:31,
which is the date/time value represented by 0.00036236807537256, the result
of the expression 8/11/2007. This is because Access implements date/time
values as a 64 bit floating point number as an offset from 30 December 1899
00:00:00, with the integer part representing the days and the fractional part
the time of day.

Ken Sheridan
Stafford, England

Hi
I have several records with "Opening" and "Closing". The
Opening are always the closing from the previous record. Then
those closing are the opening of the next record etc.
Is there a way to automatically copy Closing from the first
record into the Opening of the next record and so on?
Thanks

Databsae design courses teach the rule that if the data is
always the same as another item of data, you query it, you do
not store it in the table. It is a good rule, but like all
rules, made to be broken.

In the form where you enter the data, you need to set the
default value of opening to the value you entered for closing.
You would do this from the AfterUpdate event of the control
bound to your closing field in the table.

me.txtOpening.default = me.txtclosing.value

But that value will not persist if you close then reopen the
form, so you need to also set the default value for the opening
to the most recently entered closing in the Form_Open event. I
cannot give you a specific answer as to how to write the Where
Clause for the query as I cannot guess the structure of the
table, but you probably could use the Dlookup() function to do
this easily.
If for example, the date and time of the entry is stored, you
want the closing value associated to the maximum date in the
table, or the bigest Sequence Number...

something like
stWhereclause = "[Entrydate] = # & Dmax
("entrydate","thetableName") & "#"

me.txtOpening.default = Dlookup
("closing","thetablename",stwhereclause)

 

[Bob Quintal]

=?Utf-8?B?S2VuIFNoZXJpZGFu?=

Bob:

It’s the DefaultValue property, not the Default property
that's needed here in fact. The latter is a property of a
command button and is a Boolean expression indicating whether
the button is the default button on a form.

You are correct, sir. My fault for typing into my newsreader,
not the Access VB editor, where intellisense would have
correctly completed the property.

Its also worth noting that the DefaultValue property is always
a string expression, regardless of the data type in question,
so should be wrapped in literal quotes characters like so:

Me.txtOpening.DefaultValue = """" & Me.txtClosing & """"

A lot of the time omitting the literal quotes characters won't
matter, but sometimes they are crucial. One instance is where
the value is a date in an internationally ambiguous format
such as a short date. If the normal # date delimiter is used
instead of quotes this would give the wrong date on a system
not using the mm/dd/yyyy format, e.g. the UK format of
dd/mm/yyyy. If no delimiter is used then the value is treated
as an arithmetical expression, so the date passed into the
control would be whatever date/time value the result of that
expression represents in the underlying date/time
implementation, e.g. 08/11/2007 would result in 30 December
1899 00:00:31, which is the date/time value represented by
0.00036236807537256, the result of the expression 8/11/2007.
This is because Access implements date/time values as a 64 bit
floating point number as an offset from 30 December 1899
00:00:00, with the integer part representing the days and the
fractional part the time of day.

Ken Sheridan
Stafford, England

Hi
I have several records with "Opening" and "Closing". The
Opening are always the closing from the previous record.
Then those closing are the opening of the next record etc.
Is there a way to automatically copy Closing from the first
record into the Opening of the next record and so on?
Thanks

Databsae design courses teach the rule that if the data is
always the same as another item of data, you query it, you do
not store it in the table. It is a good rule, but like all
rules, made to be broken.

In the form where you enter the data, you need to set the
default value of opening to the value you entered for
closing. You would do this from the AfterUpdate event of the
control bound to your closing field in the table.

me.txtOpening.default = me.txtclosing.value

But that value will not persist if you close then reopen the
form, so you need to also set the default value for the
opening to the most recently entered closing in the Form_Open
event. I cannot give you a specific answer as to how to write
the Where Clause for the query as I cannot guess the
structure of the table, but you probably could use the
Dlookup() function to do this easily.
If for example, the date and time of the entry is stored, you
want the closing value associated to the maximum date in the
table, or the bigest Sequence Number...

something like
stWhereclause = "[Entrydate] = # & Dmax
("entrydate","thetableName") & "#"

me.txtOpening.default = Dlookup
("closing","thetablename",stwhereclause)

 

[s4]

This is way too complicated for me. Lol.

=?Utf-8?B?S2VuIFNoZXJpZGFu?=

Bob:

It’s the DefaultValue property, not the Default property
that's needed here in fact. The latter is a property of a
command button and is a Boolean expression indicating whether
the button is the default button on a form.

You are correct, sir. My fault for typing into my newsreader,
not the Access VB editor, where intellisense would have
correctly completed the property.

Its also worth noting that the DefaultValue property is always
a string expression, regardless of the data type in question,
so should be wrapped in literal quotes characters like so:

Me.txtOpening.DefaultValue = """" & Me.txtClosing & """"

A lot of the time omitting the literal quotes characters won't
matter, but sometimes they are crucial. One instance is where
the value is a date in an internationally ambiguous format
such as a short date. If the normal # date delimiter is used
instead of quotes this would give the wrong date on a system
not using the mm/dd/yyyy format, e.g. the UK format of
dd/mm/yyyy. If no delimiter is used then the value is treated
as an arithmetical expression, so the date passed into the
control would be whatever date/time value the result of that
expression represents in the underlying date/time
implementation, e.g. 08/11/2007 would result in 30 December
1899 00:00:31, which is the date/time value represented by
0.00036236807537256, the result of the expression 8/11/2007.
This is because Access implements date/time values as a 64 bit
floating point number as an offset from 30 December 1899
00:00:00, with the integer part representing the days and the
fractional part the time of day.

Ken Sheridan
Stafford, England

Hi
I have several records with "Opening" and "Closing". The
Opening are always the closing from the previous record.
Then those closing are the opening of the next record etc.
Is there a way to automatically copy Closing from the first
record into the Opening of the next record and so on?
Thanks

Databsae design courses teach the rule that if the data is
always the same as another item of data, you query it, you do
not store it in the table. It is a good rule, but like all
rules, made to be broken.

In the form where you enter the data, you need to set the
default value of opening to the value you entered for
closing. You would do this from the AfterUpdate event of the
control bound to your closing field in the table.

me.txtOpening.default = me.txtclosing.value

But that value will not persist if you close then reopen the
form, so you need to also set the default value for the
opening to the most recently entered closing in the Form_Open
event. I cannot give you a specific answer as to how to write
the Where Clause for the query as I cannot guess the
structure of the table, but you probably could use the
Dlookup() function to do this easily.
If for example, the date and time of the entry is stored, you
want the closing value associated to the maximum date in the
table, or the bigest Sequence Number...

something like
stWhereclause = "[Entrydate] = # & Dmax
("entrydate","thetableName") & "#"

me.txtOpening.default = Dlookup
("closing","thetablename",stwhereclause)

 

[Bob Quintal]

This is way too complicated for me. Lol.

Don't worry about it. Mr Sheridan dressed me down for being
sloppy with the code I posted. He is right.

Anyway, here is the second line of code, fixed.. It's still an
example, and needs to be tailored to your specific field
names/types.


Me.txtOpening.DefaultValue = """" &
Dlookup("closing","thetablename",stwhereclause) & """"

(All on 1 line)

Q.

=?Utf-8?B?S2VuIFNoZXJpZGFu?=

Bob:

It’s the DefaultValue property, not the Default
property that's needed here in fact. The latter is a
property of a command button and is a Boolean expression
indicating whether the button is the default button on a
form.

You are correct, sir. My fault for typing into my newsreader,
not the Access VB editor, where intellisense would have
correctly completed the property.

Its also worth noting that the DefaultValue property is
always a string expression, regardless of the data type in
question, so should be wrapped in literal quotes characters
like so:

Me.txtOpening.DefaultValue = """" & Me.txtClosing & """"

A lot of the time omitting the literal quotes characters
won't matter, but sometimes they are crucial. One instance
is where the value is a date in an internationally
ambiguous format such as a short date. If the normal #
date delimiter is used instead of quotes this would give
the wrong date on a system not using the mm/dd/yyyy format,
e.g. the UK format of dd/mm/yyyy. If no delimiter is used
then the value is treated as an arithmetical expression, so
the date passed into the control would be whatever
date/time value the result of that expression represents in
the underlying date/time implementation, e.g. 08/11/2007
would result in 30 December 1899 00:00:31, which is the
date/time value represented by 0.00036236807537256, the
result of the expression 8/11/2007. This is because Access
implements date/time values as a 64 bit floating point
number as an offset from 30 December 1899 00:00:00, with
the integer part representing the days and the fractional
part the time of day.

Ken Sheridan
Stafford, England

:


Hi
I have several records with "Opening" and "Closing". The
Opening are always the closing from the previous record.
Then those closing are the opening of the next record
etc. Is there a way to automatically copy Closing from
the first record into the Opening of the next record and
so on? Thanks

Databsae design courses teach the rule that if the data is
always the same as another item of data, you query it, you
do not store it in the table. It is a good rule, but like
all rules, made to be broken.

In the form where you enter the data, you need to set the
default value of opening to the value you entered for
closing. You would do this from the AfterUpdate event of
the control bound to your closing field in the table.

me.txtOpening.default = me.txtclosing.value

But that value will not persist if you close then reopen
the form, so you need to also set the default value for
the opening to the most recently entered closing in the
Form_Open event. I cannot give you a specific answer as to
how to write the Where Clause for the query as I cannot
guess the structure of the table, but you probably could
use the Dlookup() function to do this easily.
If for example, the date and time of the entry is stored,
you want the closing value associated to the maximum date
in the table, or the bigest Sequence Number...

something like
stWhereclause = "[Entrydate] = # & Dmax
("entrydate","thetableName") & "#"

me.txtOpening.default = Dlookup
("closing","thetablename",stwhereclause)