convert records into one line

[slimjen]

Hi Steve; thanks for replying. I don't think the user has any choice but to export the records to Excel. So; yes I agree to a text file exported to excel. I wouldn't know where to begin? Also, why do you think it won't go pass 12 customers? I get that no more than 6 records per customer but I didn't think there was a limit on customers. Thanks

[ssanfu]

There is not a limit on customer records, just that a table cannot have more than 255 fields. 12 records for one customer would require 16 "base" fields plus 28 fields "per record". 28 fields times 12 records is 336 fields PLUS the 16 base fields equals 352 fields. That is 97 fields too many for a table.....

The most customer records you could have in a table is 8 records: (28 fields X 8 records) + 16 base fields = 240 fields.

Writing the customer records to a text file allows you to be able to deal with more records per customer......

So; yes I agree to a text file exported to excel. I wouldn't know where to begin?

I'll modify the code this weekend.......

[pbaldy]

Steve, is there a reason for the text file? I'd probably automate Excel directly, but I've got more experience doing that than working with text files.

[ssanfu]

Paul,
I tend to write to text files because it is easier/simpler than writing to Excel.
Assemble a text string and write it to the file. Then open Excel and import it. Excel does the heavy lifting.

I don't have to worry about setting up variables to open Excel, moving to the next cell, releasing variables,......

Either that or I'm lazy.... uhh, conserving energy..
Yeah,, that's it...conserving energy!

[pbaldy]

Hey, it's cold up there, so conserving energy is a must.

[ssanfu]

Good thing I'm alcohol fueled!!

[slimjen]

lol. Thanks so much Steve.

[ssanfu]

So Paul convinced me to bypass the text file conversion and go straight to Excel.

Try this out... there can be up to and including 12 customer sub records.

[pbaldy]

So Paul convinced me to bypass the text file conversion and go straight to Excel.

Well, don't blame me when you faint from lack of energy!

[slimjen]

Steve, I am trying to test the DB. I don't get errors and it runs thru the code as expected. I created a folder and named the file but the output is nowhere to be found. the output I noticed is xls and I'm working in Access 2010-2016. Does this make a difference. Default output is xlsx.

[slimjen]

I got it. The code had two "c2" and the last should have been "c12". I think that was it because as soon as I changed it; the output appeared on the next run. I'll test with my prod data and post back. Thanks so much!!

[ssanfu]

the output I noticed is xls

The name of the Excel should show up in the text box on Form1. the name should have the ".xlsx" extension.
The Excel workbook is actually saved at the end of the procedure using the line

    xlw.SaveAs sPathFileName

The "xls" is a variable in the code.

The code had two "c2" and the last should have been "c12".

Good catch.... I must of had too much blood in my alcohol system. (Actually, I drink Pepsi and eat Reese's Bits/Pieces while programming - can you say Sugar Rush??)

[slimjen]

lol. Steve thanks so much. You're awesome! I tested half of the prod records and it's looks like what the user needs. We're meeting on Monday. One more thing; the user just acquired another customer with 14 records. I know; it's crazy. How do I modify to add the additional column records. I tried to just add the additional code i.e. c13...n column colors but it didn't take. Hate to impose.

[ssanfu]

You can never trust those users!

Really not a problem, but at some point you need to understand each line of the code.


Read through the code for the combine button. I added comments at the top of the sub on how to add more records.
I added a constant (intMaxRecords) and a function to get the long integer number of a color that is needed to set the color constant of each group of fields.

And I moved the color constants down to the Format Excel section of the code.
You could get rid of the color constants if you replace the C1, C2=, ... with the long integers of the colors. (like the Choose() functions at lines 93 and 100)
But I would leave them as is right now......


In the form design view, bring up the color picker (under Design/Format Design Tools/ Format - Background Color/ more colors), select a color in either tab and in the custom tab you will see the RGB color numbers. Or enter the RGB numbers and see the color.
Then enter the RGB colors in the function (Module1) to get the long integer.


(clever how I added the max number of fields in the name? No?? Well, maybe I can blame Paul - I used up a lot of energy and I am feeling extremely lazy now )





Wow, 3 to 6 to 9 to 12 to 24........when will it STOP????

PS: I hope you get a BIG bonus for this.....

[pbaldy]

Not the first time I've been thrown under the bus...