Running sum in a form

[shiro]

Everyday,I insert 5 records.Now,manually I have to type
data number from 1 untill 5.
Does anyone have an idea to make it automatically
running sum just like in a report?
Any idea greatly appreciated.
Thank's and rgds,

Shiro

 

[Marshall Barton]

Everyday,I insert 5 records.Now,manually I have to type
data number from 1 untill 5.
Does anyone have an idea to make it automatically
running sum just like in a report?

First, I have to say that any design that requires a fixed
number of records to describe an entity seems to have some
kind of flaw somewhere.

But, to your question. The records will need to have a
field that identifies them for the day they were created.
Let's say that field is named DayField and the name of the
data number field is named DataNum.

Use the form's BeforeInsert event to set the DataNum field:

Me.DataNum = Nz(DMax("DataNum", "yourtable", _
"DayField = " & Me.DayField), 0) + 1

 

[shiro]

Dear Marsh,
I still can't get rid on this.It always labelling with number 1
for every new record inserted instead continuing
the number.Do you have any other suggestion?
I work with access 2000.

 

[Marshall Barton]

Please post a Copy/Paste of the code that you used. Include
an explanation of the table(s?) involved along with the
relevant fields, their Type and their related controls on
the form(s?)

 

[shiro]

Sorry for the late reply Marsh and thank you for helping.
The form is created by the wizard so the fields's name
and their control source are similar.

Here is the code :
Me.Sample_no = Nz(DMax("Sample_no", "Inspection result_tbl", _
"Inspection_date = " & Me.Inspection_date), 0) + 1

Sample_no data type is number (long integer)
Inspection_date data type is Date/time (medium date)

Actually,inserting record process begin with filtering the query
by assigning criteria on 4 fields.The criteria is entered on a
filtering form.Hope I explain it clearly.

Please post a Copy/Paste of the code that you used. Include
an explanation of the table(s?) involved along with the
relevant fields, their Type and their related controls on
the form(s?)
--
Marsh
MVP [MS Access]

I still can't get rid on this.It always labelling with number 1
for every new record inserted instead continuing
the number.Do you have any other suggestion?
I work with access 2000.

"Marshall Barton" wrote

 

[Marshall Barton]

Here is the code :
Me.Sample_no = Nz(DMax("Sample_no", "Inspection result_tbl", _
"Inspection_date = " & Me.Inspection_date), 0) + 1

Sample_no data type is number (long integer)
Inspection_date data type is Date/time (medium date)
[] Hope I explain it clearly.

Very clearly and there's the problem ;-)

When you put a date literal into an expression it must be in
an unambiguous style using either / or - as separators.
Because the local settings in Windows can change just about
anything about how a date value is converted to a string,
the only reliable thing you can do is use the Format
function.

I think this is a semi universal example of how to format a
date when concatenating a date value into a string:

Me.Sample_no = Nz(DMax("Sample_no", _
"Inspection result_tbl", "Inspection_date = " & _
Format(Me.Inspection_date, "\#yyyy-m-d\#")), 0) + 1

If you want to see the difference between your and my
expressions, enter the following in the Immediate window:


?"Inspection_date = " & Me.Inspection_date
?"Inspection_date = " & Format(Me.Inspection_date,
"\#yyyy-m-d\#")

Watch out for line wrapping.

 

[shiro]

I think there's a problem with the design of my form.It's still not fixed
yet.
The entry is always 1.

Here is the code :
Me.Sample_no = Nz(DMax("Sample_no", "Inspection result_tbl", _
"Inspection_date = " & Me.Inspection_date), 0) + 1

Sample_no data type is number (long integer)
Inspection_date data type is Date/time (medium date)
[] Hope I explain it clearly.

Very clearly and there's the problem ;-)

When you put a date literal into an expression it must be in
an unambiguous style using either / or - as separators.
Because the local settings in Windows can change just about
anything about how a date value is converted to a string,
the only reliable thing you can do is use the Format
function.

I think this is a semi universal example of how to format a
date when concatenating a date value into a string:

Me.Sample_no = Nz(DMax("Sample_no", _
"Inspection result_tbl", "Inspection_date = " & _
Format(Me.Inspection_date, "\#yyyy-m-d\#")), 0) + 1

If you want to see the difference between your and my
expressions, enter the following in the Immediate window:


?"Inspection_date = " & Me.Inspection_date
?"Inspection_date = " & Format(Me.Inspection_date,
"\#yyyy-m-d\#")

Watch out for line wrapping.

 

[Marshall Barton]

First, in reviewing my previous reply, I fogot to state that
literal dates MUST be enclosed in # signs, much like text
must be enclosed in quotes.

Always getting 1 implies that there is never a match on the
Inspection_date field. Either the values in Inspection_date
do not match a date or we made a mistake in the line of
code.

The most likely reason for the date not matching is because
it contains a time component. Check to see if you are
setting Inspection_date to Now() instead of Date(). If
that's the case, then either change how you set the
Inspection_date field or change the expression to:

Me.Sample_no = Nz(DMax("Sample_no", _
"Inspection result_tbl", _
"DateValue(Inspection_date) = " & _
Format(Me.Inspection_date, "\#yyyy-m-d\#")), 0) + 1

If that's not the problem, post back with a Copy/Paste of
the code so I can review it.
--
Marsh
MVP [MS Access]

I think there's a problem with the design of my form.It's still not fixed
yet.
The entry is always 1.

Here is the code :
Me.Sample_no = Nz(DMax("Sample_no", "Inspection result_tbl", _
"Inspection_date = " & Me.Inspection_date), 0) + 1

Sample_no data type is number (long integer)
Inspection_date data type is Date/time (medium date)
[] Hope I explain it clearly.

Very clearly and there's the problem ;-)

When you put a date literal into an expression it must be in
an unambiguous style using either / or - as separators.
Because the local settings in Windows can change just about
anything about how a date value is converted to a string,
the only reliable thing you can do is use the Format
function.

I think this is a semi universal example of how to format a
date when concatenating a date value into a string:

Me.Sample_no = Nz(DMax("Sample_no", _
"Inspection result_tbl", "Inspection_date = " & _
Format(Me.Inspection_date, "\#yyyy-m-d\#")), 0) + 1

If you want to see the difference between your and my
expressions, enter the following in the Immediate window:


?"Inspection_date = " & Me.Inspection_date
?"Inspection_date = " & Format(Me.Inspection_date,
"\#yyyy-m-d\#")

 

[shiro]

Thank's for the help Marsh,but still doesn't make a sense.
Now,I do it in another way.
I put a text box on the form's footer that count the inserted
record.Then on Befort insert of the form,I put below code:
Me.Sample_no = (Me.Total_inspected + 1)
I think it works fine.What do you think Marsh?

First, in reviewing my previous reply, I fogot to state that
literal dates MUST be enclosed in # signs, much like text
must be enclosed in quotes.

Always getting 1 implies that there is never a match on the
Inspection_date field. Either the values in Inspection_date
do not match a date or we made a mistake in the line of
code.

The most likely reason for the date not matching is because
it contains a time component. Check to see if you are
setting Inspection_date to Now() instead of Date(). If
that's the case, then either change how you set the
Inspection_date field or change the expression to:

Me.Sample_no = Nz(DMax("Sample_no", _
"Inspection result_tbl", _
"DateValue(Inspection_date) = " & _
Format(Me.Inspection_date, "\#yyyy-m-d\#")), 0) + 1

If that's not the problem, post back with a Copy/Paste of
the code so I can review it.
--
Marsh
MVP [MS Access]

I think there's a problem with the design of my form.It's still not fixed
yet.
The entry is always 1.

shiro wrote:
Here is the code :
Me.Sample_no = Nz(DMax("Sample_no", "Inspection result_tbl", _
"Inspection_date = " & Me.Inspection_date), 0) + 1

Sample_no data type is number (long integer)
Inspection_date data type is Date/time (medium date)
[] Hope I explain it clearly.


Very clearly and there's the problem ;-)

When you put a date literal into an expression it must be in
an unambiguous style using either / or - as separators.
Because the local settings in Windows can change just about
anything about how a date value is converted to a string,
the only reliable thing you can do is use the Format
function.

I think this is a semi universal example of how to format a
date when concatenating a date value into a string:

Me.Sample_no = Nz(DMax("Sample_no", _
"Inspection result_tbl", "Inspection_date = " & _
Format(Me.Inspection_date, "\#yyyy-m-d\#")), 0) + 1

If you want to see the difference between your and my
expressions, enter the following in the Immediate window:


?"Inspection_date = " & Me.Inspection_date
?"Inspection_date = " & Format(Me.Inspection_date,
"\#yyyy-m-d\#")

 

[Marshall Barton]

What is "still doesn't make a sense" supposed to mean?

Did you check to see how you are setting Inspection_date?

Your detour into "another way", is not reliable if your
program will ever be used by more than one user at the same
time. There could be a long time (hours?) between the
BeforeInsert event and when the record is saved. It also
will not work the first inspection.

 

[shiro]

Did you check to see how you are setting Inspection_date?
I did and the setting for both table and form is same.It's medium
date (dd-mmm-yy) without any part of time,but I do ahve another
date/time field named inputting time that record the time of data
input.It's general time (dd-mmm-yy hh:mm:ss).

It also will not work the first inspection.

I see it works without knowing it's risky.

And I'm sorry if I can't speak english well.

What is "still doesn't make a sense" supposed to mean?

Did you check to see how you are setting Inspection_date?

Your detour into "another way", is not reliable if your
program will ever be used by more than one user at the same
time. There could be a long time (hours?) between the
BeforeInsert event and when the record is saved. It also
will not work the first inspection.
--
Marsh
MVP [MS Access]

Thank's for the help Marsh,but still doesn't make a sense.
Now,I do it in another way.
I put a text box on the form's footer that count the inserted
record.Then on Befort insert of the form,I put below code:
Me.Sample_no = (Me.Total_inspected + 1)
I think it works fine.What do you think Marsh?


"Marshall Barton" wrote

 

[tina]

And I'm sorry if I can't speak english well.

shiro, Marsh wasn't poking fun at your english skills, trust me. he was
saying that when you say that something "still doesn't make sense", you're
making a general comment that doesn't help him to help you - because it
doesn't give him any specific information to work with. like all of us who
regularly answer questions in these newsgroups, Marsh needs detailed
feedback: did you check/change/test whatever was previously recommended?
was there an error message displayed, and if so what was it? did something
happen that was not what was expected/wanted, and if so what was it?

you're not the first person in the ngs to make the mistake of poor feedback,
and i'm sure you won't be the last, so don't worry about it - just try to
improve on that as you continue your discussion with Marsh. and, trust me
again, he's one of the best people here to have helping you; he's helped me
in the past, and i know him to be patient, and kind, and very knowledgeable.

Did you check to see how you are setting Inspection_date?

I did and the setting for both table and form is same.It's medium
date (dd-mmm-yy) without any part of time,but I do ahve another
date/time field named inputting time that record the time of data
input.It's general time (dd-mmm-yy hh:mm:ss).

It also will not work the first inspection.

I see it works without knowing it's risky.

And I'm sorry if I can't speak english well.

What is "still doesn't make a sense" supposed to mean?

Did you check to see how you are setting Inspection_date?

Your detour into "another way", is not reliable if your
program will ever be used by more than one user at the same
time. There could be a long time (hours?) between the
BeforeInsert event and when the record is saved. It also
will not work the first inspection.
--
Marsh
MVP [MS Access]

Thank's for the help Marsh,but still doesn't make a sense.
Now,I do it in another way.
I put a text box on the form's footer that count the inserted
record.Then on Befort insert of the form,I put below code:
Me.Sample_no = (Me.Total_inspected + 1)
I think it works fine.What do you think Marsh?


First, in reviewing my previous reply, I fogot to state that
literal dates MUST be enclosed in # signs, much like text
must be enclosed in quotes.

Always getting 1 implies that there is never a match on the
Inspection_date field. Either the values in Inspection_date
do not match a date or we made a mistake in the line of
code.

The most likely reason for the date not matching is because
it contains a time component. Check to see if you are
setting Inspection_date to Now() instead of Date(). If
that's the case, then either change how you set the
Inspection_date field or change the expression to:

Me.Sample_no = Nz(DMax("Sample_no", _
"Inspection result_tbl", _
"DateValue(Inspection_date) = " & _
Format(Me.Inspection_date, "\#yyyy-m-d\#")), 0) + 1

If that's not the problem, post back with a Copy/Paste of
the code so I can review it.

 

[Marshall Barton]

Your English is ok. I'm just trying to get specific and
detailed information to help me figure out what happened.

Medium date is a format for displaying a date/time value.
It has nothing to do with how the value is assigned to the
field. For example, the table field could have its Default
Value set to Now() or maybe a form text box bound to the
field has that default value. Or you could be using VBA
code to set the bound text box to Now. Or, maybe you are
just typing a date into a form text box.

Are you sure you tested the situation with adding a new
record when there are no existing subform records?

 

[shiro]

Thank's Marsh,
On my table,the Inspection_date default value is Date()
But in my form,it's default value is :
Forms![Filter forms]![DateOfInspection]
and the default value of DateOfInspection in the filter form is Date()

Forget to mention that the main form record source comes from
a joined two table in a query.And one more thing,the my form
has no subform in it.It's a continuous form.

Your English is ok. I'm just trying to get specific and
detailed information to help me figure out what happened.

Medium date is a format for displaying a date/time value.
It has nothing to do with how the value is assigned to the
field. For example, the table field could have its Default
Value set to Now() or maybe a form text box bound to the
field has that default value. Or you could be using VBA
code to set the bound text box to Now. Or, maybe you are
just typing a date into a form text box.

Are you sure you tested the situation with adding a new
record when there are no existing subform records?
--
Marsh
MVP [MS Access]

I did and the setting for both table and form is same.It's medium
date (dd-mmm-yy) without any part of time,but I do ahve another
date/time field named inputting time that record the time of data
input.It's general time (dd-mmm-yy hh:mm:ss).

I see it works without knowing it's risky.

 

[Marshall Barton]

On my table,the Inspection_date default value is Date()
But in my form,it's default value is :
Forms![Filter forms]![DateOfInspection]
and the default value of DateOfInspection in the filter form is Date()

Forget to mention that the main form record source comes from
a joined two table in a query.And one more thing,the my form
has no subform in it.It's a continuous form.

I don't see anything that would cause it to always set
Sample_no to 1.

For some reason, the Inspection_date field in table
Inspection result_tbl is never matching the value of
Inspection_date in the form. Try setting the Format
property of Inspection_date field in table Inspection
result_tbl to
d m yyyy h:nn:ss
Then open the table and check to make sure that the time is
00:00:00
for all reords.

 

[shiro]

Yes,it does 00:00:00 for all records.

On my table,the Inspection_date default value is Date()
But in my form,it's default value is :
Forms![Filter forms]![DateOfInspection]
and the default value of DateOfInspection in the filter form is Date()

Forget to mention that the main form record source comes from
a joined two table in a query.And one more thing,the my form
has no subform in it.It's a continuous form.

I don't see anything that would cause it to always set
Sample_no to 1.

For some reason, the Inspection_date field in table
Inspection result_tbl is never matching the value of
Inspection_date in the form. Try setting the Format
property of Inspection_date field in table Inspection
result_tbl to
d m yyyy h:nn:ss
Then open the table and check to make sure that the time is
00:00:00
for all reords.

 

[Marshall Barton]

I can not see the problem. There must be something
different about Inspection_date in the table OR something is
happening to the field in the record source query. All I
can suggest is for you to break the problem into small steps
and verify that the data is correct at each step. Make sure
you run your tests starting with a new inspection so
existing data does not get in the way.
-------------------------
I never did provide you with the complete code for the
**form** BeforeUpdate event provedure. Make sure you check
for a new record before setting sample_no:

If Me.NewRecord Then
Me.Sample_no = Nz(DMax("Sample_no", _
"Inspection result_tbl", _
"Inspection_date = " & Me.Inspection_date), 0) + 1
End If

That should not affect the problem, but I wanted to make
sure you did not change Sample_no just by editing a record
that already has a sample number.
--
Marsh
MVP [MS Access]

Yes,it does 00:00:00 for all records.

On my table,the Inspection_date default value is Date()
But in my form,it's default value is :
Forms![Filter forms]![DateOfInspection]
and the default value of DateOfInspection in the filter form is Date()

Forget to mention that the main form record source comes from
a joined two table in a query.And one more thing,the my form
has no subform in it.It's a continuous form.

I don't see anything that would cause it to always set
Sample_no to 1.

For some reason, the Inspection_date field in table
Inspection result_tbl is never matching the value of
Inspection_date in the form. Try setting the Format
property of Inspection_date field in table Inspection
result_tbl to
d m yyyy h:nn:ss
Then open the table and check to make sure that the time is
00:00:00
for all reords.