how do i set a field to be the same value as the previous record

[John]

I am trying to create some data entry forms. One such forms needs a numeric
field to default to the value of the field in the previous record. This field
is a date field. the code I am using works but the date displayed is in the
american format not the system format. Code is as follows:

Private Sub BatchNumber_AfterUpdate()
If Not IsNull(Me.BatchNumber.Value) Then
BatchNumber.DefaultValue = Me.BatchNumber.Value + 1
End If
End Sub

The other problem is adding 1 to the value of a field for subsequent
records. The code I am using only works for the 2nd record and not the 3rd
etc.

Private Sub BatchNumber_AfterUpdate()
If Not IsNull(Me.BatchNumber.Value) Then
BatchNumber.DefaultValue = Me.BatchNumber.Value + 1
End If
End Sub

I am a new user so be gentle!

Thanks John

 

[Tom Wickerath]

Hi John,

See this article from Access MVP Allen Browne. I think it has what you need:

Assign default values from the last record
http://allenbrowne.com/ser-24.html

As for the format, you should be able to set whatever date format you want
for the text box control used to display the value.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John Spencer]

If you use the batch number default value then the after update event won't be
triggered - after update event for a control is only triggered if you type (or
paste) a value into the control. That is why the BatchNumber is not incrementing.

I don't know if Allen's method will work for you. If not, post back for
alternative solution.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Alimbilo]

Hello Tom,

I am a little bit confuse with your "http://allenbrowne.com/ser-24.html".

1. Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMsg As String
Call CarryOver(Me, strMsg)
If strMsg <> vbNullString Then
MsgBox strMsg, vbInformation
End If
End Sub

Do I have to replace "Form" by the actual form name?


2. Public Function CarryOver(frm As Form, strErrMsg As String, ParamArray
avarExceptionList()) As Long
On Error GoTo Err_Handler

And here too, do I have to replace "frm" with the actual form name?

Because I tried to do exactly the way you have it but it does not work.

Thanks for help

Ali Mbilo

 

[Alimbilo]

Hello Tom,

I Have one more question to ask you,
How can I get a data from a field (a specific row) from a different table in
one form?

Thank you very much,

Ali Mbilo

 

[Tom Wickerath]

Hi Ali,

1. Do I have to replace "Form" by the actual form name?

No. A form has several programmable events, one of which is BeforeUpdate.
You just paste the code in to the class module of a form, as it is shown in
Allen's article.
Note: If your form already includes this procedure, then you'd need to add
this code to an existing Private Sub Form_BeforeInsert(Cancel As Integer)
procedure.

2. And here too, do I have to replace "frm" with the actual form name?
Because I tried to do exactly the way you have it but it does not work.

Nope. What does "does not work" mean? Did you receive any error messages?
What did you see or not see?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Please post this as a brand new question (ie. create a new post). Also, try
to explain a bit more in detail what you are wanting to accomplish.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Alimbilo]

Hello,

I really appreciate your previous help and posted this new one 2 days ago
but didn't see your answer.

I need your help soloving this issue.

I have 2 tables in my database and would like to have a:

Field A in Table A to be equal to Field C in Table B.
I have tried so far to do: Field A = [Table B]![Field C]
but I am getting a "#Name?# error.

 

[Tom Wickerath]

Hi Ali,

I really appreciate your previous help and posted this new one 2 days ago
but didn't see your answer.

Sorry, but I wasn't on the lookout for your question. I suggested that you
post as a new thread, because your issue was significantly different than
this existing thread started by John. Are you saying that you haven't gotten
*any* responses? I might go take a look at it, but help me find it so that I
don't have to go on a fishing expedition. Reply with a link to your new
thread, or reply giving the following details so that I can easily find it:

Posting Name
Posting Date
Exact subject of post

And, in this posting that you mentioned, did you explain *why* you are
wanting to store the same data in more than one table:

I have 2 tables in my database and would like to have a:

Field A in Table A to be equal to Field C in Table B.

You may have a valid reason, but I'd like you to tell me, in your own words,
why you need to store the value from field A in table A into field C in table
B.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hello,

I really appreciate your previous help and posted this new one 2 days ago
but didn't see your answer.

I need your help soloving this issue.

I have 2 tables in my database and would like to have a:

Field A in Table A to be equal to Field C in Table B.
I have tried so far to do: Field A = [Table B]![Field C]
but I am getting a "#Name?# error.

 

[Alimbilo]

Thanks for your prompt reply.
I did not get any clear answers at all from others that the reason why I am
asking for your help.

The first table that I created is full (255 fields) and I had to create a
second table.
This is for a cellphone repair report.

In Table A, there is a field name "TotalQtyRepaired"
In Table B, you have these fields "Team1Repair", "Team2Repair", "Team3Repair"

I would like to have "TotalQtyRepaired" = Team1Repair + Team2Repair +
Team3Repair

I have tried to do TotalQtyRepaired = [Table B]!Team1Repair + [Table
B]!Team2Repair + [Table B]!Team3Repair

But I am getting this "#Name?" on the form view.

Any ideas?

Hi Ali,

I really appreciate your previous help and posted this new one 2 days ago
but didn't see your answer.

Sorry, but I wasn't on the lookout for your question. I suggested that you
post as a new thread, because your issue was significantly different than
this existing thread started by John. Are you saying that you haven't gotten
*any* responses? I might go take a look at it, but help me find it so that I
don't have to go on a fishing expedition. Reply with a link to your new
thread, or reply giving the following details so that I can easily find it:

Posting Name
Posting Date
Exact subject of post

And, in this posting that you mentioned, did you explain *why* you are
wanting to store the same data in more than one table:

I have 2 tables in my database and would like to have a:

Field A in Table A to be equal to Field C in Table B.

You may have a valid reason, but I'd like you to tell me, in your own words,
why you need to store the value from field A in table A into field C in table
B.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hello,

I really appreciate your previous help and posted this new one 2 days ago
but didn't see your answer.

I need your help soloving this issue.

I have 2 tables in my database and would like to have a:

Field A in Table A to be equal to Field C in Table B.
I have tried so far to do: Field A = [Table B]![Field C]
but I am getting a "#Name?# error.

 

[Tom Wickerath]

Hi Ali,

The first table that I created is full (255 fields) and I had to create a
second table.

Whoa! You are not using proper database design techniques, if you have the
maximum number of fields possible. In fact, most well designed databases very
rarely have more than 25 to 30 fields, maximum, in any one table.

In Table A, there is a field name "TotalQtyRepaired"

You do not want to store the result of something that can be calculated. The
word "Total" in this field name stongly hints that it can be calculated from
other fields in this table. If this is true, then perform such a calculation
in a query, but don't attempt to store the result of your calculation in the
table. Here's why:

Suppose A + B + C = TotalQtyRepaired. You calculate this quantity and store
the value in the TotalQtyRepaired field. Later on, you (or someone else)
realizes that one or more of the independent values were entered incorrectly.
So they change the value stored in, say Field A. However, they forget to
update the total stored in the TotalQtyRepaired field. You now have
inconsistent data.

In Table B, you have these fields "Team1Repair", "Team2Repair", "Team3Repair"

This is known as a multi-field design, which is not a good thing. If you
later have to add (or remove) team(s), you will need to add (or remove) one
or more fields. You'll then need to modify any queies, forms, reports and
module code that makes use of these fields. You should not have fields that
store similar data in the table. Here is something that you should commit to
memory:

Fields are expensive; Records are cheap

In other words, your database design should be such that it can accomodate
any number of teams without the need to add or remove fields from a table.
This is done by storing the team information in a separate, related, child
table. At this point, the best thing that you could do is to stop working on
your database for the present time, and start reading all you can about
database normalization. Here is a link to get you started:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Make sure to read the first two articles, by database design expert Michael
Hernandez.

Good Luck,


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thanks for your prompt reply.
I did not get any clear answers at all from others that the reason why I am
asking for your help.

The first table that I created is full (255 fields) and I had to create a
second table.
This is for a cellphone repair report.

In Table A, there is a field name "TotalQtyRepaired"
In Table B, you have these fields "Team1Repair", "Team2Repair", "Team3Repair"

I would like to have "TotalQtyRepaired" = Team1Repair + Team2Repair +
Team3Repair

I have tried to do TotalQtyRepaired = [Table B]!Team1Repair + [Table
B]!Team2Repair + [Table B]!Team3Repair

But I am getting this "#Name?" on the form view.

Any ideas?

 

[Alimbilo]

Thanks a lot Tom. You really helped me.

My last question is how can I have a unbound data to not duplicate in a
different record?

What I meant is, on day 1 whenever I put a value in my unbound data and when
I open a new record (day2), I still have that value in the undound field.
When I changed it on day 2, it changes too on day 1. Is there a way to stop
that to happen?

Hi Ali,

The first table that I created is full (255 fields) and I had to create a
second table.

Whoa! You are not using proper database design techniques, if you have the
maximum number of fields possible. In fact, most well designed databases very
rarely have more than 25 to 30 fields, maximum, in any one table.

In Table A, there is a field name "TotalQtyRepaired"

You do not want to store the result of something that can be calculated. The
word "Total" in this field name stongly hints that it can be calculated from
other fields in this table. If this is true, then perform such a calculation
in a query, but don't attempt to store the result of your calculation in the
table. Here's why:

Suppose A + B + C = TotalQtyRepaired. You calculate this quantity and store
the value in the TotalQtyRepaired field. Later on, you (or someone else)
realizes that one or more of the independent values were entered incorrectly.
So they change the value stored in, say Field A. However, they forget to
update the total stored in the TotalQtyRepaired field. You now have
inconsistent data.

In Table B, you have these fields "Team1Repair", "Team2Repair", "Team3Repair"

This is known as a multi-field design, which is not a good thing. If you
later have to add (or remove) team(s), you will need to add (or remove) one
or more fields. You'll then need to modify any queies, forms, reports and
module code that makes use of these fields. You should not have fields that
store similar data in the table. Here is something that you should commit to
memory:

Fields are expensive; Records are cheap

In other words, your database design should be such that it can accomodate
any number of teams without the need to add or remove fields from a table.
This is done by storing the team information in a separate, related, child
table. At this point, the best thing that you could do is to stop working on
your database for the present time, and start reading all you can about
database normalization. Here is a link to get you started:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Make sure to read the first two articles, by database design expert Michael
Hernandez.

Good Luck,


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thanks for your prompt reply.
I did not get any clear answers at all from others that the reason why I am
asking for your help.

The first table that I created is full (255 fields) and I had to create a
second table.
This is for a cellphone repair report.

In Table A, there is a field name "TotalQtyRepaired"
In Table B, you have these fields "Team1Repair", "Team2Repair", "Team3Repair"

I would like to have "TotalQtyRepaired" = Team1Repair + Team2Repair +
Team3Repair

I have tried to do TotalQtyRepaired = [Table B]!Team1Repair + [Table
B]!Team2Repair + [Table B]!Team3Repair

But I am getting this "#Name?" on the form view.

Any ideas?

 

[Tom Wickerath]

Hi Ali,

My last question is how can I have a unbound data to not duplicate in a
different record?

The data in your unbound control is not getting saved with any record,
unless you specifically write VBA code to cause this to happen. So, it's not
"duplicating in a different record", it's simply displaying the .text
property of the control. Why are you attempting to use an unbound control?
There are two uses of unbound controls that come to mind (there may be more
as well). The first use would be to help find an existing record. For
example, take a look at this tutorial:

Combo box to find a record
http://www.access.qbuilt.com/html/find_a_record.html

Here, we are using a combo box control to assist in the task of finding a
record in a recordset. However, we are not using this control to change data.
The second use would be unbound forms with unbound controls. This is an
advanced technique that only few people ever venture into using. I suppose a
third use of an unbound control might be to assist the user with an
on-the-fly conversion of data, for example pounds to kilograms, if they
needed to store the metric equivalent. In this case, you would be providing
an "on-screen" calculator of sorts, to assist with the conversion.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Alimbilo]

Hi Tom,
The reason I am asking that is we want every records in the forms to be save
according to the date.
We want to be able to go back in february and see what we did on that day.
Since I am out of fields in the table I am using, I am trying to come up
with a backup plan to help me save more data.

 

[Tom Wickerath]

Hi Ali,

The reason I am asking that is we want every records in the forms to be save
according to the date.

Okay, so all you need to do is add a field to track the date added. It can
have a default value of =Date(). You don't even need to display this field on
a form. You can also add another field, called DateLastModified, to track the
last date that this record was edited. Generally, people accomplish this by
using the Form's BeforeUpdate event procedure.

We want to be able to go back in february and see what we did on that day.

By adding one or both of the fields I just mentioned, it will be very easy
to query the database for any given day or range of days (for example, any
week or any month).

Since I am out of fields in the table I am using, I am trying to come up
with a backup plan to help me save more data.

Don't try to "work around" a flawed database design by "coming up" with
work-arounds. You will only be creating more work for yourself, in the
future, if you try to build using bailing wire and bubble gum. You *really*
do need to start normalizing your database, so that you end up with tables
that are no where near the maximum number of fields. As I said before, most
well-designed databases do not include more than 25 to 30 fields maximum, on
average, in any given table. Take what I'm trying to tell you to heart. If
you have a table with 255 fields, you don't have a database; instead, what
you have is an Access spreadsheet. Invest the time now to start learning more
about database design and normalization, to save yourself untold amounts of
time and frustration in the future trying to work with your current design.
Trust me on this.

I hinted before that there is a valid reason for storing the same data in
more than one table. The reason is when you need to track history. For
example, storing the full address of a customer in a Customers table, and
storing the same full address in an Orders table. This way, you have a
historical record of exactly where each and every order was delivered, even
if your customer has moved several times in the intervening years. Another
example is storing current price versus the price at the time of a sale. You
want any reports that calculate total sales amounts to reflect the prices
charged at the time of the sale, not today's current prices for the same
items.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________