Using MS Access reports to generate code

[bstern]

I am using Access import my cut-list from KCDW and have created a report to generate Shopbot code.

All is working great with the exception that when I export the report to a .txt file I have extra spaces and missing some lines.

The only help I can find on the issue says to make sure all text boxes are set to a height divisible by .125 (1/8). While this helped some I still have extra spaces at the page break.

Anybody using Access to generate code that could shed light on this problem?
Thank
Bob

[Brady Watson]

The 1st issue is creating a report. This will cause you some problems. Just have your data get populated into a table instead of a report. You can just do a 'make table query' to do this. It's much easier than creating a report & it's in the format you need for exporting.

Try exporting the table that you created as a text format with comma separated values and when you do, go to advanced options and change text qualifier field to 'none'. The text format you want is listed as txt, csv and a few others on the same line. Be sure to enter in the .txt portion after the name of your text file or it will default to .csv.

I'm not sure what you mean by setting your text box to be in divisions of .125...without seeing what you are doing, it's anyone's guess. It sounds to me like it is an issue with a report...which is another reason to dump that method & just make a table.

-B

[bstern]

Now you tell me!!!


I have about 200+ hrs in creating the data base and report.
The report works great for me. The report generates the code perfect.

What happens is, when exporting to a txt file, at the point just after where a page break is in the report, it takes 3 or 4 lines out and inserts them down the page about 2 or 3 lines. Really weird.

Microsoft has been able to duplicate the problem but has only been able to suggest workarounds that have not worked yet. That's what you get from India. They only seem equipped for canned answers.

I have been give a few suggestion by a friend and am trying them now. They show some promice.

I will try to work through excel as you suggested.

Thanks

[henrik_o]

Bob,

Are you actually missing inputs, or is it an issue of formatting (inserting unwanted input, like linebreaks/CRs or spaces, or moving things around)?

If you're missing input, you need to get that solved with your current implimentation, or move the base to a new implimentation like Brady suggests.

If you're not actually missing input, then re-formatting the file to be eligible should be easy to do with any standard dev environment, effectively post-processing the file. This is inelegant, but would work as an interim solution.

Could you post some concrete examples of what is happening, as in textblocks before export and post export?

[Brady Watson]

Bob,
There's no need to use Excel at all. Simply create a make-table query in leiu of a report. Since you most likely already have created a query to populate the report, changing it to a make table query is a breeze. Not really much work involved at all. When you export as txt, just look at the data to make sure that it is comma separated and that it goes out without quotes around it. That's it.

I was a software developer in a previous life & little sanfus like this were a daily occurance with all of the different systems we had to keep running. I know how much time some of this stuff takes to do...but I also know that if the data isn't right at the end of the line, it doesn't matter how many hours you have into it. I've invested more time in fruitless adventures like this than I care to admit.

-B

[henrik_o]

What Brady suggests sounds like the sane and excellent solution!

If you read my post in the other thread, I'm still interested in what you're doing here, Bob. Not so much the actual coding, since I'm not interested in using MS Access for my own application, but rather how you've gone about it conceptually.

I'm trying to get a grasp of what's involved in setting up my own parametric solution for certain jobs. How do you handle the transition from raw parts code to code within a sheet/panel boundary?

[bstern]

Henrick, the problem was the data was moving around. What happens is, when exporting to a txt file, at the point just after where a page break is in the report, it takes 3 or 4 lines out and inserts them down the page about 2 or 3 lines. Really weird.

Looks like I have solved the problem. Its a lengthy explanation so I will spare you all the gory details.

I learned about the basics of parametric programming from a great excel presentation done by Dirk Hazeleger.

For my strategy I used what I am familiar with, which is Access. The steps are as follows:

Design in KCDW and export cut list to a .csv file.
Open file in excel and save as excel file.
Import excel file into an optimizer (nesting software) I use Cabinet Parts Pro because I already own it and Ryan has been great to work with. I just use it for nesting.
Export the nest to a type of csv file.
Import nested raw parts with a start point and start corner into an Access table.

I have created a parts Table linked by part name that has options such as edgebanding, cut rafix or any other operations needed.
I do a couple a simple update operations to the imported table that does things like note the cabinet depth, chose the proper slide for the depth and define the points of the 4 corners.

Then I run a report that has a series of sub-reports that do each operation, such as cut rafix, drill front 32mm holes, cut onion skin.

I have about 8 or 9 sub-reports now. Each one "can shrink" or disappear if the operation is not requested on that part.

If I need to add another operation, I just make a new sup-report. You just have to be able to write BIG IIf statements. They can get pretty long. After a while, if you can say what you want it to do out loud you can write it.

All was going great and producing the code I wanted in the report until I tried to export it to a txt file. I did not need it formatted the same. I knew that text files have little to no formatting. All I need is for the lines of text to come out in the order printed on the report. The Shopbot control software has no problem with extra spaces.
I had done this before with no problems. Evidently the sub-reports throw a kink in the way MS exports. Its flawed and MS has admitted its flawed. I think I have found a way of getting around it. More testing is needed to confirm as the problem is intermittent. For now it looks good.

I will let you all know how it comes out.


Henrick, If you have any other questions please feel free to email me.

[Brady Watson]

"You just have to be able to write BIG IIf statements"...

You might want to try using a 'Select Case' statement instead of nesting If statements to keep some semblence of sanity...Just a suggestion.

-B

[bstern]

Brady, I checked and "Select Case" seems only to be available in Vba not in a report text box. You got me looking around though and found the "Switch" Function has some elements of Select Case and may make things a bit easier.

Thanks!

As I have said, I'm not much of a coder. I use access because of query by example and the report generating requires little real programming knowledge. Only knowledge of basic fuctions, allot I had already learned in excel.

[henrik_o]

Bob,

Sorry about not getting back to you sooner.

Very interesting info. As I related, I'm looking into writing my own parametric solution, and I was getting stumped on the nesting part. For some reason, nesting algorithms seem remarkably hard to find as open source. I've sat down and looked at the problem and have a reasonable idea of the conceptual side, but from that to a working implimentation, oh boy. Making do with an existing nester is by far the preferable scenario, and I want to thank you for opening my eyes to Cabinet Parts Pro.

I'm also glad to hear your problem re: formatting seems to be solved. I'm not much of a coder either, but if you need any help or second set of eyes, feel free to say so -- though you would probably be much better off just asking Brady

[bstern]

Henrik

Glad I could be of help. Ryan is helpful and his program is great.

One issue with Cabinet parts pro is the way it saves the nest. Its a strange delimited file.
I can't get Access to import it for me. But excel works fine.

What I have done is to write an Auto_Open macro in an excel file that opens and imports a.cp3 deletes the extraneous data from the top of the file saves it as an .xls file and then closes excel. You can call that from an access macro.
Then import the xls to Access or work with it Excel.

I can send you a copy of it if you like.

After figuring out the formatting issue, access reports are working great.
I just added a function and all I had to do was write a sub-report to do the needed operation and inserted it in in the main report. All sub-reports shrink and do not show up in the code if they are not called for.

This works great for me.

My 3 spindle drill head is due in any day now and I can't wait to get off the simulator and start cutting some melamine!

[henrik_o]

Bob,

Great to hear things are a'rollin!

I'm gunning for a somewhat different parametric solution; rather than designing cab-by-cab in a software package I will have two or three build types and the parametrics will actually be room dimensions. We do library-style outfits where it goes from floor to roof, so this suits us perfectly. Quite a road to travel for that to be working, though.

I do not own KCDW so I'm not sure how legible your code would be to me, but I'm always in for something new so it would be nice if you could email it to the address in my profile.

A 3 spindle drill head! Oh my, that sounds like something. Be sure to post many pictures and information once it arrives, please!