How to copy a row and insert in multiples in a form

[psus via AccessMonster.com]

Gurus,

I am new to Access coding and need help doing this. I appologize for the
long mail.

What I need to do is when I enter the data in the form and before I go to the
next record I want to save this row first in the table and then insert two
more rows with the same values (that I just entered and saved in the table)
while incrementing the index field, which I do not want the user to enter a
random value. I also want to programatically manipulate a field value in the
second and third rows before commiting. This tells me how to increment the
index field.

For Example:

Tbl-A
Reg_Num Number (Index)
Name Text (30)
Reg_Type Text (1)
Category Text (10)
Sub_Category Text (10)
Short_Desc Text (10)
Long_Desc Text (50)

1st row:
10001, "PSUS", "A","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"

After I enter these values I click on "SAVE" button. When I click on save, I
want to insert the same row twice (notice Reg_Num and Reg_Type values) in the
table and increment Reg_Num and change the value of Reg_Type.

2nd row:
40001, "PSUS", "W","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"

3rd row:
60001, "PSUS", "X","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"

As you see, I am incrementing the index field value by 30,000 if Reg_Type is
"W" and by 50,000 if the Reg_Type is "X". What it means is copy the row that
I just entered, insert it in the table, change the Reg_Type value and
increment Reg_Num based on the business rule. COMMIT. Do the same for the
next row.

I have about 20 Fields in the table. I do not want the user to type 10 times
the same values and make mistakes. This is just an example. I have about 8
different Reg_Types that needs to be stored in the table everytime a new
Reg_Num is entered with Type "A". User can enter any Reg_Type but to make
my life easier I'm restricting to start from Reg_Type "A". For each Reg_Type
the Reg_Num needs to be computed because it is extreamly important in our
application. This number drives the financial part.

Hope I explained it right this time.

Thanks a million in advance.

 

[Michel Walsh]

Hi,


Append the records from a SQL statement:


DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(40001, 'PSUS',
'W','CREDIT CARD', 'FEE', 'CCF', 'CREDIT CARD FEE' ) "

DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(60001, 'PSUS',
'X','CREDIT CARD', 'FEE', 'CCF', 'CREDIT CARD FEE') "


If some data is dependant on value actually in a control, in the
VALUES(list), you can use the syntax FORMS!FormName!ControlName to refer to
it.



You may have to requery the recordset to see the record so added, since they
are inserted by another user (your code is not the same user than the one at
the keyboard).


Hoping it may help,
Vanderghast, Access MVP

 

[psus via AccessMonster.com]

Thanks a lot for the response.

Like I said, I am new to the Access world. How would I append the records
from a SQL statement?

If I understand correctly, right after saving the values, before I get out,
take the values, manipulate the way I want and insert them, correct? Are
these values still available for use?

Only one user enters the data at any given time.

Thanks again.
PS

Hi,

Append the records from a SQL statement:

DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(40001, 'PSUS',
'W','CREDIT CARD', 'FEE', 'CCF', 'CREDIT CARD FEE' ) "

DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(60001, 'PSUS',
'X','CREDIT CARD', 'FEE', 'CCF', 'CREDIT CARD FEE') "

If some data is dependant on value actually in a control, in the
VALUES(list), you can use the syntax FORMS!FormName!ControlName to refer to
it.

You may have to requery the recordset to see the record so added, since they
are inserted by another user (your code is not the same user than the one at
the keyboard).

Hoping it may help,
Vanderghast, Access MVP

Gurus,

[quoted text clipped - 63 lines]

Thanks a million in advance.

 

[Michel Walsh]

Hi,


You can type the previous statements in the After Update subroutine handler
event of the form. Those are two lines:


DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(40001, 'PSUS',
'W','CREDIT CARD', 'FEE', 'CCF', 'CREDIT CARD FEE' ) "

DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(60001, 'PSUS',
'X','CREDIT CARD', 'FEE', 'CCF', 'CREDIT CARD FEE') "



the theory is:


DoCmd.RunSQL "INSERT INTO tableName(listOfFields) VALUES(listOfValues)"


will insert ONE record, with the list of values pushed into each
corresponding field from the listOfFields.

As example,

DoCmd.RunSQL "INSERT INTO table1(f1, f2) VALUES(5, 7)"

insert a single record, in table table1, with value 5 under field f1 and
value 7 under field f2.

Execute the line many times and you get as many new records.... change the
listOfValues to append different values. Assuming, as example, the second
value can be read from the control Who, the syntax is:

DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(40001,
FORMS!formnameHere!Who, 'W','CREDIT CARD',
'FEE', 'CCF', 'CREDIT CARD FEE' ) "


and, still as example, if the first value is to be 40000+ whatever is in the
control ID, your possible syntax is:



DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(
40000 + FORMS!formNameHere!id ,
FORMS!formnameHere!Who, 'W','CREDIT CARD',
'FEE', 'CCF', 'CREDIT CARD FEE' ) "



Hoping it may help,
Vanderghast, Access MVP

Thanks a lot for the response.

Like I said, I am new to the Access world. How would I append the records
from a SQL statement?

If I understand correctly, right after saving the values, before I get
out,
take the values, manipulate the way I want and insert them, correct? Are
these values still available for use?

Only one user enters the data at any given time.

Thanks again.
PS

Hi,

Append the records from a SQL statement:

DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(40001, 'PSUS',
'W','CREDIT CARD', 'FEE', 'CCF', 'CREDIT CARD FEE' ) "

DoCmd.RunSQL "INSERT INTO tableName( listOfFields) VALUES(60001, 'PSUS',
'X','CREDIT CARD', 'FEE', 'CCF', 'CREDIT CARD FEE') "

If some data is dependant on value actually in a control, in the
VALUES(list), you can use the syntax FORMS!FormName!ControlName to refer
to
it.

You may have to requery the recordset to see the record so added, since
they
are inserted by another user (your code is not the same user than the one
at
the keyboard).

Hoping it may help,
Vanderghast, Access MVP

Gurus,

[quoted text clipped - 63 lines]

Thanks a million in advance.