VBA to split lines based on fields in table

[Tom123456]

Hello,

I have a database that contains a table of orders - some of which need to be invoiced in installments and are differentiated as such by the 'number of installments column

If the order is to be invoiced in installments i need to split the order into multiple rows based on number of installments (eg 12 installments = 12 rows,6 installments = 6 rows) and also ensure that these newly created row contain the right 'installment amount' & 'installment posting date' for that installment from the source file

I've uploaded a database with a sample input file and how i'd like the output to look like.

Is this possible to do in Access? I've seen something similar done via record sets but not quite like this

Cheers,
Tom

[OBP]

Yes this can be done with a VBA Recordset.
I will take a look at your database and get back to you.

[OBP]

OK, does the data come in to the "Input" table in that structure?
The output data is not correct, the input data should stay as it is and the Dates and Installments should be in a "Payments" Table to avoid all the duplication that is in your example output table.

[Tom123456]

Data comes into the Input table in that structure - source data cannot be changed in anyway

I need the duplication in the output table in that format as this table will be exported to provide data for another system

[OBP]

You can have the table in that format, but the export can be a Query that combines the 2 tables in to the format you want.
Do you want me to look at the VBA for doing the transfer in to table 2?

[Tom123456]

If you could look at the VBA that can do the transfer into table 2 that would be great - i would need it to be able to run as part of a macro if possible also.

Thanks,
Tom

[OBP]

Tom, what is the maximum number of Instalments that can be made?

[Tom123456]

12 is the maximum

[OBP]

Tom, I have finished the VBA code and it says that your Output table was not quite right instalment wise.
I have run the code once so that you can see the results, you can run it again if you want to see how quick it is.
Open the form called transfer data and click the button.

Which brings up a problem, should the input table have a Transferred check box or date to show it has been output, so that it is not actioned again?

[Tom123456]

OBP,

Thanks for doing this VBA code - it's really useful - I've got a few questions regarding the VBA it but I'll quickly type up some background info to help make sense of my questions.

I'm planning to periodically import in data from system 1 into the input table and then reformat it into the output table in the right way for subsequent import into system 2 - each time it run's I'll have a query that will clear out input + output table so i don't believe a checkbox or date field will be required.

2 questions

1) Some rows will not be invoiced in installments so will not need to be split - if i a add a row without installment in input table (fields blank) i get a invalid use of null error - I've tried using the NZ function in the VBA but still get the same error - can the code be amended to run when the input table might have rows with no or less then 12 installments?

2) Can this code be executed as part of a macro? my thought process is for my macro to import data into input table, run this vba, and export output table.

Cheers,
Tom

[OBP]

Tom, the code should accomodate values less than 12, but not zero.
I can overcome the error OK, but the question is what do you want to happen to the records with zero installments?

As to using with a macro, it actually makes more sense to include all the code in to the VBA sub, one click, import, transfer and export.
If you already have macros for the import and export you can "Save As" them as VBA code to se what the code looks like.

[Tom123456]

If the row has zero installments it should copy over as it is into the output table.

In reference to using a macro i have an automation tool that can run access macros for me (as well as download the required input data for this db from the FTP & upload the output data to another ftp etc) so if it was executable as a macro i would be able to automate the process from start to finish.

Cheers,
Tom

[OBP]

OK, I will change/add the code to send it over as is, I assume the installmentAmount and Installment Posting Date fields will be blank?

I will transfer the code in to a module that you can call from a Macro

[Tom123456]

Thanks - yes the 2 fields will be blank.

[OBP]

Tom, here is the Macro Version, there is a new button on the form to run Macro1, which runs a Function that "calls" the code in Module 1.

if all the fields in the output table have the same name and are in the same order it would be possible to drastically reduce the code by using this format in a loop to transfer the data


rs.(x) = rstable(x).Value

with the smaller loop to add installments.

[Tom123456]

This is perfect!

Yes all the fields in the output table should have the same name and order as that of the Input table - are you able to provide the VBA that does this? I'm constantly adding/removing columns to my database at the moment (changes mirrored in source/dest table)so this would be a lot easier to keep up to date as opposed to editing the VBA each time.

Thanks again for all your help so far.

Cheers,
Tom

[OBP]

Tom, here is the code to replace the code in the Transfer part of module 1.


Dim data As String, count As Integer, count2 As Integer, rstable As Object
Dim recount As Integer, rs As Object, x As Integer, fieldcount As Integer
On Error GoTo errorcatch
Set rstable = CurrentDb.OpenRecordset("input")
Set rs = CurrentDb.OpenRecordset("output")
rstable.MoveLast
recount = rstable.RecordCount
rstable.MoveFirst
For records = 1 To recount
If Not IsNull(rstable![Number of Instalments]) And rstable![Number of Instalments] <> 0 Then
count = rstable![Number of Instalments]
Else
count = 1
End If
For count2 = 1 To count
rs.AddNew
For fieldcount = 1 To 34
rs.Fields(fieldcount).Value = rstable(fieldcount).Value
Next fieldcount
If count > 1 Then
x = count2 * 2
rs.[Instalment Amount] = rstable(34 + x).Value
rs.[Instalment Posting Date] = rstable(35 + x).Value
End If
rs.Update
rs.Bookmark = rs.LastModified
Next count2
rstable.MoveNext
Next records
skip:
rs.Close
Set rs = Nothing
rstable.Close
Set rstable = Nothing
MsgBox "Transferred records"
Exit Sub
errorcatch:
MsgBox Err.Description & " - " & count2 & " field " & fieldcount

The loop that counts from 1 to 34 is the one that transfers the "standard data", so if you want to add more fields ensure that you add them before the individual Installment fields and then adjust the 34 accordingingly.

[Tom123456]

Thanks OBP! I'll look to implement this code the next time i'm asked to add or remove columns from this process.

[Tom123456]

Hi OBP,

I've tried adding more columns in the input/output tables and amending the VBA but the installments no longer split as expected and return various error messages - I'd like to fix this and make any further changes easier.

You mentioned in post 15 that if the input/output tables are identical then the code could be simplified - would you be able to provide the VBA that does this which includes the smaller installment loop?

Cheers,
Tom

[OBP]

Can you provide me with a copy of the tables that are now going wrong?
I think that the code that I supplied in Post #17 already has the code that you talking about.

[Tom123456]

Hi OBP,

Thanks for looking into this - attached is the database with the new columns i added.

The structure of the input & output tables will always be identical if this makes the VBA any easier to implement.

Cheers,
Tom

[Tom123456]

20876 test

[OBP]

Your new Desttable has a problem, installment 6 is in the wrong order.

[Tom123456]

Hi OBP,

Thanks - I've fixed the column order but it's still returning errors (attached) when i run the module and i can't work out how to amend to fix.

I tried adding in the new columns in the VBA then adding to the 34/35 values by the number of columns added but it didn't work - sorry if i'm missing something obvious, don't use VBA like this often.

Cheers,
Tom

[OBP]

The zip contains a link and not the database.

[Tom123456]

Sorry - this should be it.

Edit: Just seen this example is missing the following columns

Installment Total
Installment Amount
Installment Posting Date

where the new data should be inputted but is in my orignal where i'm also getting errors.

[OBP]

So does this version contain what you need?

[OBP]

That does not contain the latest VBA code that I posted, that has the original Field by Field version.

[OBP]

OK, the reason it wsn't working is because you changed the names of the Installment and installment date fields.
Ther attached version is now working, I have added Macro1 into this database to run the VBA code.

[Tom123456]

Hi OBP,

Thanks for updating however the attached version doesn't split the installment posting date & amount into the new field as it did in your previous example.

In the attached I've run your new macro which splits the rows but it needs to to insert the date/amount values into the 'installment posting date' & 'installment amount' fields - how can this be changed?

Thanks again for your help

Cheers,
Tom

[OBP]

This version works ok.

[Tom123456]

Hi OBP,

That version isnt putting it in the right column sorry - if you look at the access db in post 30 it should put the installment date & value for that row in the 'Installment Posting Date' & 'Installment amount' field

Tom

[OBP]

Well if the tables you provided actually had those fields in it I might be able to put them in there.
It is why the original ones you sent me did not work, because those fields were not in place.
Have you added them in the V1.zip database?

ps I see that you have, but that is a totally different table to the ones I have, not only are those 2 fields missing from the originals, but the Number of installments is in a completely different place.

[Tom123456]

Sorry for the confusion - the file i originally sent was incorrect, the file in post 30 is the correct version and does include those columns that need to be populated as well as the new columns i need that caused your original solution to error out.

[OBP]

OK, I will look at the Code change required to work with the V1 version.

ps you have also lost the currency type in both tables for the later installment amounts in the V1 version.

[OBP]

Try this one.

[Tom123456]

Looks like it's working thanks!

I'll append 'output' into another table after this module has ran for any new columns i want to add etc!

Thanks once more for all your help and patience!

[OBP]

You're welcome.

[Tom123456]

Hi OBP,

Hope your well - I've been using this VBA as part of my project but i get an error if the installment text field is not in GBP ie a value of £7500 works but $7500 doesn't. I've had a look at the VBA as well as a few google search but cant work out why it would treat it differently in this case

Do you know how i could go about fixing this or any links to references that would help me solve this issue?

Thanks,
Tom

21752

[OBP]

What is the data type of the2 fields in question, they should be Currency, or at least Number to 2 decimal places?

[Tom123456]

Hi OBP,

Sorry for the delayed response - you were right in that the fields in question were the wrong data type,changing them fixed the issue but forgot to reply and thank you.

I was hoping you'd be able to help me with an amendment to the VBA i was trying to do - I'd like to also grab the field 'Installment Description' from the source table and populate the field in the output - i made some amendments which worked. However in doing so i broke the way 'Installment amount' was being populated. I've gone over the code several times but cant see what could be causing these differences logically.

Thanks,
Tom

[Tom123456]

Just to add it current populates Installment 1 instead of 'Installment Amount'

[OBP]

What code are you using and how do you run it?

[Tom123456]

Hi,

I've modified it to the below and it's run as part of a macro (RunCode) - I've included it in the sample db as a module called 'Split Data -Instalments'



Option Compare Database
Function calltransfer()
Call TransferData
End Function
Sub TransferData()
Dim data As String, count As Integer, count2 As Integer, rstable As Object
Dim recount As Integer, rs As Object, x As Integer
On Error GoTo errorcatch
Set rstable = CurrentDb.OpenRecordset("DestTable")
Set rs = CurrentDb.OpenRecordset("output")
rstable.MoveLast
recount = rstable.RecordCount
rstable.MoveFirst
For records = 1 To recount
If Not IsNull(rstable![Number of Instalments]) And rstable![Number of Instalments] <> 0 Then
count = rstable![Number of Instalments]
Else
count = 1
End If
For count2 = 1 To count
rs.AddNew
For fieldcount = 1 To 46
rs.Fields(fieldcount).Value = rstable(fieldcount).Value
Next fieldcount
If count > 1 Then
x = count2 * 3
rs(47) = rstable(47 + x).Value
rs(48) = rstable(48 + x).Value
rs(49) = rstable(49 + x).Value


End If
rs.Update
rs.Bookmark = rs.LastModified
Next count2
rstable.MoveNext
Next records
skip:
rs.Close
Set rs = Nothing
rstable.Close
Set rstable = Nothing
Exit Sub
errorcatch:
MsgBox Err.Description & " - " & count2 & " field " & fieldcount
End Sub

[OBP]

You said
"Just to add it current populates Installment 1 instead of 'Installment Amount'"
Installment 1 is 7500 in both tables, so that appears to be correct, but the second Installment is not being populated.
I will have to go back to the original database to see what it was doing.

[OBP]

I have compared the 2 sets of code and the following errors show up
The original code counts to 46 records the later code also counts to 46.
However the transfer from horizontal to vertical fields the original code was

x = count2 * 2
rs(46) = rstable(47 + x).Value
rs(47) = rstable(46 + x).Value
ie 2 fields starting at 46 but tranferring 47+x
your code has

x = count2 * 3
rs(47) = rstable(47 + x).Value
rs(48) = rstable(48 + x).Value
rs(49) = rstable(49 + x).Value
3 fields as you want, but you are starting at 47 = 47 not 46 = 47.

[Tom123456]

Hi OBP,

I've moved the columns slightly to allow for the new field i wanted to add and as a result changes the col numb it was using - from what i understand in this VBA code X represents the installment number for the row being created multiplied by the number of columns i need it to go along the input table to grab the correct installment date/amount/description columns for that installment row.

Given that

RS(47) = Installment Amount
RS(48)= Installment Posting Date
RS(49) = Installment Description Final.


x = count2 * 3
rs(47) = rstable(47 + x).Value
rs(48) = rstable(48 + x).Value
rs(49) = rstable(49 + x).Value


Why does it work for RS48 & 49 but for RS47 it doesn't take the values from rstable50,53,56? Or have i misunderstood how the code works?

Thanks,
Tom

[OBP]

If you have moved the staring point to 47 then the original count has to go to 47 as well and then 47 =48+3, 48 = 49+ 3, 49 = 50 + 3
It is the first line after x = count2 * 2 that you have changed it was 46 = 47 + x and you made it 47 = 47 + x and then the others have to follow that.

[Tom123456]

Makes sense thanks! I've actually been testing since i posted & found a simpler way of achieving this that won't require me to add/update 12 columns to both tables for the VBA - however this wouldn't of been possible without your earlier work so thanks again!


rs(fieldnumber) = "Invoice " & count2 & " of " & count

Where count2 is the current installment number being processed and count is the number of installments

Tom