 | |
Continuous form - Auto Update a field
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Continuous form - Auto Update a field -
03-04-2010
, 02:54 PM
#2
| |||
| |||
|
|
I have a continuous form where the first field in the form is a serial number. It starts with 1 and increments each time a new record is added. I want to autoincrement this field. It is set as a number. I do not want to use autonumber for this field. Serial Number Rev Number Date of Test 1 1 03/03/2010 2 1 03/03/2010 3 1 03/04/2010 Is this possible without an autonumber in a continuous form? Any suggestions are appreciated. |
Me.SerialNumber = Dmax("SerialNumber","YourTableName") + 1
if it passes all validation checks.
#3
| |||
| |||
|
|
In the Before_Update event of the form, you could do something like Me.SerialNumber = Dmax("SerialNumber","YourTableName") + 1 if it passes all validation checks. |
change the value every time the record is edited, rather than each
time a record is added (which is hat I understand was requested).
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
#4
| |||
| |||
|
|
Salad <salad (AT) oilandvinegar (DOT) com> wrote in news:uNKdnYL75uk9vA3WnZ2dnUVZ_t6dnZ2d (AT) earthlink (DOT) com: In the Before_Update event of the form, you could do something like Me.SerialNumber = Dmax("SerialNumber","YourTableName") + 1 if it passes all validation checks. Don't you mean Before_Insert? If you use Before_Update it will change the value every time the record is edited, rather than each time a record is added (which is hat I understand was requested). Personally, I wouldn't do it at B4Insert. I'd do it it at the save time |
If Me.NewRecord then
statement preceeding the calc if I were to use that method.
#5
| |||
| |||
|
|
clk wrote: I have a continuous form where the first field in the form is a serial number. It starts with 1 and increments each time a new record is added. I want to autoincrement this field. It is set as a number. I do not want to use autonumber for this field. Serial Number Rev Number Date of Test 1 1 03/03/2010 2 1 03/03/2010 3 1 03/04/2010 Is this possible without an autonumber in a continuous form? Any suggestions are appreciated. In the Before_Update event of the form, you could do something like Me.SerialNumber = Dmax("SerialNumber","YourTableName") + 1 if it passes all validation checks. |
To avoid modifying existing records, that line should be
after:
If Me.NewRecord Then
--
Marsh
#6
| |||
| |||
|
|
David W. Fenton wrote: Salad <salad (AT) oilandvinegar (DOT) com> wrote in news:uNKdnYL75uk9vA3WnZ2dnUVZ_t6dnZ2d (AT) earthlink (DOT) com: In the Before_Update event of the form, you could do something like Me.SerialNumber = Dmax("SerialNumber","YourTableName") + 1 if it passes all validation checks. Don't you mean Before_Insert? If you use Before_Update it will change the value every time the record is edited, rather than each time a record is added (which is hat I understand was requested). Personally, I wouldn't do it at B4Insert. I'd do it it at the save time in case the added record is aborted. But I'd probably have a If Me.NewRecord then statement preceeding the calc if I were to use that method. |
But I guess you're saying you want to delay the getting of the
serial number until the record is actually "undirtied." Adding in
the .NewRecord test would make that sensible, yes.
I'll have to give that one a think. I like the idea, but in
particular applications of mine, see it as a potential problem. In
general, my philosophy is to save the records as quickly as
possible, since I often do the record add in an unbound form, then
open it in the editing form. This delays the assignment of the
incremented field until certain data has been collected, and perhaps
this accomplishes the same thing.
I long ago abandoned for most apps trying to add and edit records in
the same form because it was just too complicated to try to validate
everything. Indeed, I'm thinking of one app that has had problems
using a single form for both and I believe it's time to add an ADD
NEW form. Doing so would eliminate a whole bunch of potential
problems.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
#7
| ||||
| ||||
|
|
Salad <salad (AT) oilandvinegar (DOT) com> wrote in news:0tCdndrmcIz_7AzWnZ2dnUVZ_oudnZ2d (AT) earthlink (DOT) com: David W. Fenton wrote: Salad <salad (AT) oilandvinegar (DOT) com> wrote in news:uNKdnYL75uk9vA3WnZ2dnUVZ_t6dnZ2d (AT) earthlink (DOT) com: In the Before_Update event of the form, you could do something like Me.SerialNumber = Dmax("SerialNumber","YourTableName") + 1 if it passes all validation checks. Don't you mean Before_Insert? If you use Before_Update it will change the value every time the record is edited, rather than each time a record is added (which is hat I understand was requested). Personally, I wouldn't do it at B4Insert. I'd do it it at the save time in case the added record is aborted. But I'd probably have a If Me.NewRecord then statement preceeding the calc if I were to use that method. Huh? BeforeInsert is cancellable. |
sequential ids are the desired goal, I wouldn't do it in B4Insert, I'd
do it when the record is actually saved.
Quote:
|
But I guess you're saying you want to delay the getting of the serial number until the record is actually "undirtied." Adding in the .NewRecord test would make that sensible, yes. |
end-user. If I saw CustomerID with a value of 20 or CustomerName of
"Joe Blow's Mechanical Shop", I'd more easily recognize the name over
the ID. In fact, I typically hide the IDs from the user.
Quote:
|
I'll have to give that one a think. I like the idea, but in particular applications of mine, see it as a potential problem. In general, my philosophy is to save the records as quickly as possible, since I often do the record add in an unbound form, then open it in the editing form. This delays the assignment of the incremented field until certain data has been collected, and perhaps this accomplishes the same thing. |
I'd use an autonumber as it basically does it's magic as a BeforeInsert
event.
Quote:
|
I long ago abandoned for most apps trying to add and edit records in the same form because it was just too complicated to try to validate everything. Indeed, I'm thinking of one app that has had problems using a single form for both and I believe it's time to add an ADD NEW form. Doing so would eliminate a whole bunch of potential problems. |
#8
| |||
| |||
|
|
David W. Fenton wrote: Salad <sa... (AT) oilandvinegar (DOT) com> wrote in news:0tCdndrmcIz_7AzWnZ2dnUVZ_oudnZ2d (AT) earthlink (DOT) com: David W. Fenton wrote: Salad <sa... (AT) oilandvinegar (DOT) com> wrote in news:uNKdnYL75uk9vA3WnZ2dnUVZ_t6dnZ2d (AT) earthlink (DOT) com: In the Before_Update event of the form, you could do something like * *Me.SerialNumber = Dmax("SerialNumber","YourTableName") + 1 if it passes all validation checks. Don't you mean Before_Insert? If you use Before_Update it will change the value every time the record is edited, rather than each time a record is added (which is hat I understand was requested). Personally, I wouldn't do it at B4Insert. *I'd do it it at the save time in case the added record is aborted. *But I'd probably have a * * If Me.NewRecord then statement preceeding the calc if I were to use that method. Huh? BeforeInsert is cancellable. Yes. *However, it fires when you input the first field of a form. *If sequential ids are the desired goal, I wouldn't do it in B4Insert, I'd do it when the record is actually saved. But I guess you're saying you want to delay the getting of the serial number until the record is actually "undirtied." Adding in the .NewRecord test would make that sensible, yes. Yes. *A numeric key field, for most instances, doesn't say much to an end-user. *If I saw CustomerID with a value of 20 or CustomerName of "Joe Blow's Mechanical Shop", I'd more easily recognize the name over the ID. *In fact, I typically hide the IDs from the user. I'll have to give that one a think. I like the idea, but in particular applications of mine, see it as a potential problem. In general, my philosophy is to save the records as quickly as possible, since I often do the record add in an unbound form, then open it in the editing form. This delays the assignment of the incremented field until certain data has been collected, and perhaps this accomplishes the same thing. I would agree with you if sequential numbering is not relevent. *Then I'd use an autonumber as it basically does it's magic as a BeforeInsert event. I long ago abandoned for most apps trying to add and edit records in the same form because it was just too complicated to try to validate everything. Indeed, I'm thinking of one app that has had problems using a single form for both and I believe it's time to add an ADD NEW form. Doing so would eliminate a whole bunch of potential problems. Each developer has his preferences.- Hide quoted text - - Show quoted text - |
using the before insert event of the form with a query to limit it to
a particular serial/part id.
#9
| |||
| |||
|
|
I long ago abandoned for most apps trying to add and edit records in the same form because it was just too complicated to try to validate everything. Indeed, I'm thinking of one app that has had problems using a single form for both and I believe it's time to add an ADD NEW form. Doing so would eliminate a whole bunch of potential problems. Each developer has his preferences. |
James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com
I don't want to stereotype people, but it's hard not to when faced
with stereotypical behavior. -- Ken Harrington
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.