How to insert same values that already entered in the form

[PS]

Gurus,

I would greately appreciate some input with the following. 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.

Hope I explained it right.

Thanks a million in advance.

PS

 

[Steve Schapel]

PS,

This can be done quite easily, using an Append Query. One approach to
this, you would first of all set up a master Rules table. It would have
these fields...
StartType
AddType
Increment
.... so using your example, the data here would be like this...
A W 30000
A X 50000
.... etc.

However, before I go into the details of what the Append Query would
look like, let me ask something here... Is your table structure set in
concrete, or could you consider a design review? You really should have
this Reg_Num and Reg_Type data out in a separate related table. You
should not be having multiple records in this existing table relating to
what is essentially the same information.

 

[SteveS]

I, too, think you should normalize your structure before proceeding much
further.

And be aware that "Name" is a reserved word in Access and shouldn't be used
as names of objects (like field names). Also, it is not as descriptive as
maybe "Reg_Name" or "Cust_Name".

In case you can't change the tables, below is a code example to add the
records, with just a little error checking. You could/should add more.

My example has the types/inc amounts hard coded. I like Steve Schapel's
method of using a table - is it easier to add/change using a form, instead of
editing the code. Instead of using the Choose function, you could use open a
recordset an loop thru it to get the type & increment amounts.

I used a button name of "btnSave" per your comment about using a button to
add the additional records.


' ******** beg code **********
Private Sub btnSave_Click()
On Error GoTo Err_btnSave_Click

Dim strSQL As String
Dim i As Integer
Dim IncAmt As Long
Dim IncType As String
Dim vReg_Num As Long

' only create records if Reg_Type is "A"
If Nz(Me.Reg_Type, "") <> "A" Then
MsgBox "Reg Type MUST be ""A"" to create new records!!"
Exit Sub
End If

'example to create 4 additional records, use
' For i = 1 to 4
'IncAmt = Choose(i, 30000, 50000, 70000, 90000)
'IncType = Choose(i, "W", "X", "Y", "Z")

For i = 1 To 2
IncAmt = Choose(i, 30000, 50000)
IncType = Choose(i, "W", "X")

'build the SQL string
strSQL = "Insert Into TBL_A values (" & Me.tbReg_Num + IncAmt
strSQL = strSQL & ",'" & Me.tbReg_Name & "', "
strSQL = strSQL & "'" & IncType & "', "
strSQL = strSQL & "'" & Me.tbCategory & "', "
strSQL = strSQL & "'" & Me.tbSub_Category & "', "
strSQL = strSQL & "'" & Me.tbShort_Desc & "', "
strSQL = strSQL & "'" & Me.tbLong_Desc & "');"

'MsgBox strSQL

' insert the record
CurrentDb.Execute strSQL, dbFailOnError

Next i

Exit_btnSave_Click:
Me.Requery
Exit Sub

Err_btnSave_Click:
' if Reg_Num is the Primary key
If Err.Number = 3022 Then

'don't forget to modify the Choose functions here also!!
MsgBox "A Record for " & _
Me.tbReg_Num + IncAmt & _
"/'" & IncType & "' already exists!"
Resume Next
Else
MsgBox Err.Description
Resume Exit_btnSave_Click
End If
End Sub
' ******** end code **********

HTH

 

[Steve Schapel]

PS,

Let me first thank both of you for taking time.

You're welcome!

The structure that I provided here is just an example. I dont have a field
name called "Name" and same with others. Actually all my fields in the table
are very descriptive.

Just a hint for the future... Fudging the information when asking for
help is seldom helpful.

This table is basically a static table. Everytime a new Reg_Num is added a
set of 8 types goes with it and many (not all) of the field values stays the
same across all Reg_Types for that Reg_Num with some exception (still
figuring out the business rules). Thus each Reg_Num is a set of 8 different
types. Each time a new Reg_Num is added we start with Reg_Type "A" so
Reg_Num is incremented by max(Reg_Num) + 1 (where Type = A ) and increment
for others based on the corresponding Type. Using master Rules table would
definitely complicate the issue.

No it wouldn't. The example that SteveS gave is ok for the limited
scope of the example, but the expansion of this concept to incorporate
all of your business rules would get much more complicated. Whereas a
Rules table, once you figure out what your rules are, will mean you can
run an Append Query that will always be right, something like this...

Private Sub btnSave_Click()
Me.Dirty = False
CurrentDb.Execute "INSERT INTO TBL_A ( Reg_Num, Name, Reg_Type,
Category, Sub_Category Text, Short_Desc, Long_Desc)" & _
" SELECT Reg_Num + Incremment, Name, AddType, Category,
Sub_Category Text, Short_Desc, Long_Desc" & _
" FROM TBL_A INNER JOIN Rules ON TBL_A.Reg_Type =
Rules.StartType" & _
" WHERE TBL_A.Reg_Num =" & Me.Reg_Num, dbFailOnError
End Sub

But I hesitated to give this code, as it is encouraging you to continue
to do things wrongly. The point that SteveS and I both emphasised about
normalising your table design still stands... otherwise you might as
well use a spreadsheet.

Steve S: Dont I need to save the values first (first entered values of type
"A") before going on adding additional records?

In the case of SteveS's code, it would be a good idea, but not necessary.

 

[PS]

Steve,

I was not trying to falsify or misrepresent the information here. I was
just trying to put my problem in a simple way so that I can explain it better
and everyone can understand it better. I understand what you are saying and
I keep that in my mind when asking for help in the future. Thanks.

I will know the exact requirement and associated business rules in a day or
two. I will definitely bring your suggestion of normalizing the table design
during the discussion.

Thanks a lot one more time.

Sincerely,
PS