G
Guest
Access Gurus,
Let me first appologize for the long mail.
I asked this question on 10/21/05 (under subject: How to insert same values
that already entered in the form) but I was not ready with all the biz rules
which go in designing the application.
Here is the requirement. I've a table with 25 fields. I am listing only a
few below:
REGISTRATION_ID NUMBER
REGISTRATION_TYPE TEXT
REGISTRATION_CATEGORY TEXT
REGISTRATION_SUB_CATEGORY TEXT
REGISTRATION_PAYTYPE NUMBER
REGISTRATION_PRICE NUMBER
IS_REVERSIBLE TEXT
REVERSIBLE_REG_ID NUMBER
IS_REG_USUABLE TEXT
TRXN_SEQ_NO NUMBER
UPDATE_TS DATETIME
SHORT_DESCRIPTION TEXT
LONG_DESCRIPTION TEXT
.......
Every time a new record is added we start with Reg_Type of "A" (control is
defaulted to "A"). When "A" type record is inserted in the tabe, what I want
to do basically is change the values of some fields and insert a set of 7
more rows back into the same table immediately. The Field values will be
changed based on Reg_Type. Including Reg_Type "A", I've --> B,D,F,N,R,W,X.
This is my logic:
User enters the 1st record in the form:
1,432, "A", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "F", 0, "T", 0, "10/21/2005",
"CA OVR PAY", "OVER PAYMENT COLLECTION"
Once the 1st entry is recorded in the table, I compute the next 7 rows based
on the Reg_TYpe.
Compute for 2nd entry:
If Reg_Type = "R" Then
Increment Reg_ID by 10,000 (meaning 1,432 + 10,000 = 11,432)
Reg_Type = "R"
IS_REVERSIBLE = "T"
REVERSIBLE_REG_ID = 11,432
IS_REG_USUABLE = "F"
(other field values stays same as in Type "A")
Insert 2nd row:
11,432, "R", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "T", 11432, "F", 0,
"10/21/2005", "CA OVR PAY", "OVER PAYMENT COLLECTION"
Compute 3rd entry:
If Reg_Type = "X" Then
Increment Reg_ID by 50,000 (meaning 1432 + 50,000 = 51,432)
Reg_Type = "X"
IS_REVERSIBLE = "T"
REVERSIBLE_REG_ID = 0
IS_REG_USUABLE = "T"
(other field values stays same as in Type "A")
Insert 3rd row:
51,432, "X", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "T", 0, "T", 0,
"10/21/2005", "CA OVR PAY", "OVER PAYMENT COLLECTION"
In this way I must insert records for all the remaining Reg_Types. As you
see, everytime Reg_Type is changed I increment Reg_ID (either by 10k or 50k
or 70k ...). This means for every Reg_Type "A" entry 7 other types needs to
go into the table. The logic remains same whenever a new Registration_ID is
added. Next time around, when a new Registration_ID needs to be added check
the table and find the max(Registration_ID) for Reg_TYpe "A" and increment it
by 1. That way I increment "A" Type also meaningfully. The reason behind
for this is to make it easier for one to know what series a particular type
("A", "B", "D", "X"...etc types) fall under.
I could make the user to enter all the 8 rows. But the problem is what if
the user makes a mistake or forgets to enter a record totally. This will
cause unnecessary problems because the Registration_ID is the most key field
in the application.
Hope I explained it well what I need to do. I would really appreciate it if
you could put me in right direction as I am new to Access coding.
Thanks a million.
PS
Let me first appologize for the long mail.
I asked this question on 10/21/05 (under subject: How to insert same values
that already entered in the form) but I was not ready with all the biz rules
which go in designing the application.
Here is the requirement. I've a table with 25 fields. I am listing only a
few below:
REGISTRATION_ID NUMBER
REGISTRATION_TYPE TEXT
REGISTRATION_CATEGORY TEXT
REGISTRATION_SUB_CATEGORY TEXT
REGISTRATION_PAYTYPE NUMBER
REGISTRATION_PRICE NUMBER
IS_REVERSIBLE TEXT
REVERSIBLE_REG_ID NUMBER
IS_REG_USUABLE TEXT
TRXN_SEQ_NO NUMBER
UPDATE_TS DATETIME
SHORT_DESCRIPTION TEXT
LONG_DESCRIPTION TEXT
.......
Every time a new record is added we start with Reg_Type of "A" (control is
defaulted to "A"). When "A" type record is inserted in the tabe, what I want
to do basically is change the values of some fields and insert a set of 7
more rows back into the same table immediately. The Field values will be
changed based on Reg_Type. Including Reg_Type "A", I've --> B,D,F,N,R,W,X.
This is my logic:
User enters the 1st record in the form:
1,432, "A", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "F", 0, "T", 0, "10/21/2005",
"CA OVR PAY", "OVER PAYMENT COLLECTION"
Once the 1st entry is recorded in the table, I compute the next 7 rows based
on the Reg_TYpe.
Compute for 2nd entry:
If Reg_Type = "R" Then
Increment Reg_ID by 10,000 (meaning 1,432 + 10,000 = 11,432)
Reg_Type = "R"
IS_REVERSIBLE = "T"
REVERSIBLE_REG_ID = 11,432
IS_REG_USUABLE = "F"
(other field values stays same as in Type "A")
Insert 2nd row:
11,432, "R", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "T", 11432, "F", 0,
"10/21/2005", "CA OVR PAY", "OVER PAYMENT COLLECTION"
Compute 3rd entry:
If Reg_Type = "X" Then
Increment Reg_ID by 50,000 (meaning 1432 + 50,000 = 51,432)
Reg_Type = "X"
IS_REVERSIBLE = "T"
REVERSIBLE_REG_ID = 0
IS_REG_USUABLE = "T"
(other field values stays same as in Type "A")
Insert 3rd row:
51,432, "X", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "T", 0, "T", 0,
"10/21/2005", "CA OVR PAY", "OVER PAYMENT COLLECTION"
In this way I must insert records for all the remaining Reg_Types. As you
see, everytime Reg_Type is changed I increment Reg_ID (either by 10k or 50k
or 70k ...). This means for every Reg_Type "A" entry 7 other types needs to
go into the table. The logic remains same whenever a new Registration_ID is
added. Next time around, when a new Registration_ID needs to be added check
the table and find the max(Registration_ID) for Reg_TYpe "A" and increment it
by 1. That way I increment "A" Type also meaningfully. The reason behind
for this is to make it easier for one to know what series a particular type
("A", "B", "D", "X"...etc types) fall under.
I could make the user to enter all the 8 rows. But the problem is what if
the user makes a mistake or forgets to enter a record totally. This will
cause unnecessary problems because the Registration_ID is the most key field
in the application.
Hope I explained it well what I need to do. I would really appreciate it if
you could put me in right direction as I am new to Access coding.
Thanks a million.
PS