Split record into two or more records. 2

[MICKI0220]

I need some advice on how to handle a coding situation that splits a record into two or more records.. Here is the situation...I have a program that imports invoices and payments on contracts for a given period. Each Contract has a "contract type".. 01, 02, 03 etc...based on the contract type, a given percentage of commission is paid to the sales men for the duration of the contract. It all worked fine, but then the powers above me decided that they wanted the contract to be paid in two or more different "contract types"...What I need to do now is bring my payments and invoices into a form. On the bottom of the form I have text boxes for each contract type so my user can input a amount for each contract type that is specified for it to be split by. After my user does this how do I make the form add two or more records based on how many fields in the contract type are not null?

Thanks for any suggestions

Micki

 

[MazeWorX]

with the after update event you could run an update query using the input as criteria. We would need to see more details about the underlying table(s) to provide more of a solution

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MICKI0220]

The underlying table is called ARTAP_1. What will happen is that I open my form which has all the fields that are in ARTAP_1 filled in for a Invoice (record). It also has 8 check boxes and 8 text boxes on the lower part....Each one is label 1-8. If the invoice is not going to be split, the user needs to hit the create record command button and what I want it to do is populate another table called "ARTAP with all the original fields and then delete that record from ARTAP_1. If the invoice needs to be split, then the user will check the boxes of the appropriate contracts (1-8) and put in the amounts in the text box appropriately. When the user hits the create record button, I want it to create two record, but now the key will at _1 to the first record and _2 to the second record, the amount will be taken from the txtcheckbox amount instead of original amount etc, put the data in ARTAP and delete the record out of ARTAP_1. Does this make sense?

 

[dhookom]

Please provide a sample record for ARTAP_1 that needs to be divided and how you would want this to appear in ARTAP.

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Original
Seq_No CustID InvNum TranType Date Amount ChkNum
00000512 WO6811 ML00002 L 1/1/2012 364.17
Contract_Type Rep_1 Rep_2 CommStDate
01 108 1/1/2010


New 1st Record
Seq_No CustID InvNum TranType Date Amount ChkNum
00000512-1 W06811 ML00002 L 1/1/2012 200.17
Contract_Type Rep _1 Rep_2 CommStDate
01 108 1/1/2010

New 2nd Record
Seq_No CustID InvNum TranType Date Amount ChkNum
00000512-2 W06811 ML00002 L 1/1/2012 164.00 Contract_Type Rep _1 Rep_2 CommStDate
08 108 1/1/2010

 

[dhookom]

MICKI0220,
You have started 90 threads and replied 135 times and you still aren't using TGML to format your postings so they can be read more easily by those who might want to help you.

Also, you haven't provide any clue as to why the amount was split 200.17 and 164.00.

Please expend the effort to allow us to help you.

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

First.....I guess I don't use TGML format because I have no idea what it is or how to use it....

Second....That is just a way that the accounting people will do the splitting....They are trying to realize cost and profit on several aspects of a job...They decide how much will go to what ever contract they want to....They could choose 99.9% go to one and then .1 percent go to another and then next month, they may change it....That's why we have to do this manually in a form because we can't get the main system to do it...

Right now the "amount" comes in as an import from our accounting software....I bring the records into this ARTAP_1 table...The form is linked to the table...It may show that it has 25 records for the month. On each record, the accounting person editing the data, will look at each record and she knows whether this one record has two contract types to evaluate commission on...Based on what contract type it is, is how the percentage of the invoice is given to the rep_1 or rep_2.

If she looks at the record and it isn't going to be split, she will just hit enter and it will go through and see if any of the check boxes on the bottom are checked...If none are then the record goes into ARTAP as it came into ARTAP_1...and then deleted from the Artap_1 table.....If she decides this record needs to be commissioned into more than one contract_type, she will check the appropriate check boxes and then put the SPLIT amount into the appropriate amount boxes....This time when she hits enter it will again look for the values in the check boxes, take the contract type from the check box...take the amount from the new amount box and put those values in the ARTAP table as the amount instead of the 'full Amount' and the contract_type that is checked into the Contract_type in the contract_type box instead of the one that was in the Artap_1 table. Make two or more records instead of one...making all the fields the same except the contract_type and amount for each record.... Then delete that split record from Artap_1

 

[dhookom]

Please figure out how to use TGML which is explained in the screen after you click [Preview Post].

The ones I use most are for code and fixed space fonts:
[ignore]

  Dim strWhere as String
  strWhere = "1=1 "

and
[tt][blue]
Month Value
====== =====
Jan 3
Feb 7
Mar 12
Apr 9
May 4
[/blue][/tt]
[/ignore]

  Dim strWhere as String
  strWhere = "1=1 "

and
[tt][blue]
Month Value
====== =====
Jan 3
Feb 7
Mar 12
Apr 9
May 4
[/blue][/tt]
Consider reposting your sample records with TT tags and only the fields that are of interest.

Apparently the percentages are not automated. If these values are entered after the split, then don't include them in your posting.


Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Lets see if I did this TGML coding right.

Okay I got a little farther, so lets try the code approach.

What I am trying to do is have the code look at 1 record at a time through the form. If the user has edited the form, when she clicks on the create button the following code runs....I will add more code to look at the rest of the text boxes but it is basically the same code. I get "Run-time error '424; Object Required" on the highlighted part of the code

[b]
DoCmd.SetWarnings (off)

Dim db As Database
Dim rsArtap As Recordset

Set db = CurrentDb()

Set rsArtap = db.OpenRecordset("ARTAP")


[highlight]If frmSplitCommission!txt01 <> 0 Then [/highlight]
   rsArtap.AddNew
        rsArtap!Seq_No = frmSlitCommission!txtKey & "_1"
        rsArtap!Amount = frmSlitCommission!txt01
        rsArtap!CustID = frmSlitCommission!txtCustId
        rsArtap!InvNum = frmSlitCommission!txtInvNum
        rsArtap!TranType = frmSlitCommission!txtTransType
        rsArtap!Date = frmSlitCommission!txtDate
        rsArtap!ChkNum = frmSlitCommission!txtChkNum
        rsArtap!Contract_Type = frmSlitCommission!txtContractType
        rsArtap!Rep_1 = frmSlitCommission!txtRep1
        rsArtap!Rep_2 = frmSlitCommission!txtRep2
        rsArtap!CommStDate = frmSlitCommission!txtCommStartDate
        rsArtap.Update

Else:
        rsArtap.AddNew
        rsArtap!Seq_No = frmSlitCommission!txtKey
        rsArtap!Amount = frmSlitCommission!txtAmount
        rsArtap!CustID = frmSlitCommission!txtCustId
        rsArtap!InvNum = frmSlitCommission!txtInvNum
        rsArtap!TranType = frmSlitCommission!txtTransType
        rsArtap!Date = frmSlitCommission!txtDate
        rsArtap!ChkNum = frmSlitCommission!txtChkNum
        rsArtap!Contract_Type = frmSlitCommission!txtContractType
        rsArtap!Rep_1 = frmSlitCommission!txtRep1
        rsArtap!Rep_2 = frmSlitCommission!txtRep2
        rsArtap!CommStDate = frmSlitCommission!txtCommStartDate
        rsArtap.Update


End If [/b]

 

[MazeWorX]

if the code is in the forms code module use Me.txt01 if its outside the module then Forms!frmSplitCommission.txt01

btw the TGML looks great!!

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

Yes, good job on the TGML. Try be explicit with

Dim db As [b]DAO.[/b]Database
Dim rsArtap As [b]DAO.[/b]Recordset

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

you should code set warnings like this

DoCmd.SetWarnings False

and don't forget to turn them back on

DoCmd.SetWarnings True

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[TheAceMan1]

How are ya MICKI0220 . . .

Using prior suggestions in this thread and a little different logic ... here's a sample of crunching your code (more readable):

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("ARTAP", dbOpenDynaset)
   DoCmd.SetWarnings False
   
   With rst
      .AddNew
         If Me!txt01 <> 0 Then
            !Seq_No = Me!txtKey & "_1"
            !Amount = Me!txt01
         Else
            !Seq_No = Me!txtKey
            !Amount = Me!txtAmount
         End If
            
         !CustID = Me!txtCustId
         !InvNum = Me!txtInvNum
         !TranType = Me!txtTransType
         !Date = Me!txtDate
         !ChkNum = Me!txtChkNum
         !Contract_Type = Me!txtContractType
         !Rep_1 = Me!txtRep1
         !Rep_2 = Me!txtRep2
         !CommStDate = Me!txtCommStartDate
      .Update
   End With
   
   Set rst = Nothing
   Set db = Nothing
   DoCmd.SetWarnings True[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MICKI0220]

MazeWorx.. I tried that first suggested to use Me! for the form controls and that worked great. Thanks

Another question...Now that I have the records being split and deleted from the original table, how do I refresh the form so it doesn't show 'deleted' in the text boxes. I'd like it to grab the next record so the user can edit that one.

 

[TheAceMan1]

MICKI0220 . . .

A simply [blue]Me.Requery[/blue] should do ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MICKI0220]

TheAceMan1

That was easy....Thanks alot...All of you...it is working great.